Pro Tips

Question 1

1. How do you quickly jump to the last row?
2. How do you quickly jump to the first row?
3. How do you quickly jump to the first column?
4. How do you quickly jump to the last column?

Answer 1

1. Double click the bottom edge of a cell
2. Double click the top edge of a cell
3. Double click the left edge of a cell
4. Double click the right edge of a cell

Question 2

How do you quickly find cells with only numerical or only text values?

Answer 2

On the Home ribbon tab, go to Find & Select > Go To Special… > select Constants > uncheck all options except Numbers and Excel will only select the cells with numbers (no text or formulas)

Question 3

How do you create a Year To Date total using a formula?

Answer 3

1. Create a row or column to hold your YTD data
2. Input the first value in the YTD, which will be the same as the first total
3. Create a formula that adds the ‘new total’ to the total you created in step 2
4. Drag this formula across the remaining rows. As long as you used relative cell references, Excel will iteratively calculate a recursively increasing YTD total for each new column as shown in the example below

Question 4

What is the difference between the COUNT and COUNTA functions?

Answer 4

COUNT only counts all cells with numeric values - either numbers or dates

COUNTA counts all cells with data (alpha or numbers or dates)

Question 5

How can you add Trending Arrow ‘Stoplights’?

Answer 5

Home ribbon > Conditional Formatting > Icon Sets > 3 Arrows (Colored)

Question 6

1. What is the prerequisite for using Slicers?
2. How do you insert a Slicer?
3. How do you select multiple values in a slicer window?
4. True or false - you can add multiple Slicers at once for multiple dimensions?
5. What are Slicers?

Answer 6

1. Your data must be formatted as a Table first
2. Table Design ribbon tab > Insert Slicer
3. Hold down the Ctrl key and click the desired values
4. True
5. Pretty filters on steriods

Question 7

How do you insert data in cells without altering the data in adjacent rows or columns?

Answer 7

1. Select a cell or range of cells that you want to insert data into
2. Right click
3. Insert…

You will be prompted if you want to shift cells to the down or right.

Question 8

How do you delete cells without altering the data in adjacent rows or columns?

Answer 8

1. Select a cell or range of cells you want to delete
2. Right click
3. Delete…

You will then be prompted to shift the remaining cells to the left or up.

Question 9

How do you quickly Move, Copy, and Move & Insert data from one location to another using the mouse?

Answer 9

• Move > Mouse Drag the edge of any cell (after selecting cells)
• Copy > Ctrl + Mouse Drag the edge of any cell (after selecting cells)
• Move & Insert > Shift + Mouse Drag the edge of any cell (after selecting cells)

Note that both formulas and formatting are retained when copying or inserting data this way

Question 10

1. What is the name of the feature that allows you to show the same dataset with multiple, independent scrollbars?
2. How do you invoke this feature?
3. What is a use case for this feature?

Answer 10

1. Split View
2. Go to the View tab and click Split - where your mouse is determines how the data is split (and whether it is split horizontally, veritically, or both).
3. If you wanted to view the Total or Subtotal formula line at the end of the spreadsheet to see how it updates in real time when you update data much earlier near the beginning of a column with a lot of rows of data.

Question 11

1. What is the name of the feature in Excel where Excel can automatically summarize tota into groupings?
2. What is a prerequisite for this feature?
3. What are three really good use case examples for this feature?
4. Describe the steps to invoke this feature.

Answer 11

1. Auto Outline
2. Your data should have totals or subtotals so Excel can understand the data’s heirarchy
3. Balance Sheets or Profit & Loss Statements, Budgets
4. Data ribbon tab > Outline > Group > Auto Outline

Question 12

1. What is the name of the function that automatically shows all active Excel Workbooks (files) into a single screen (and automatically sizes them)?
2. Where can the button for this feature be found?

Answer 12

1. Arrange All
2. In the Vew tab

Question 13

1. What is the name of the feature that allows you to see multiple Worksheet tabs on an Excel file at the same time?
2. Where do you go to invoke this feature?

Answer 13

1. The Windows feature
2. View tab > New Window

Question 14

1. What is a common cause of the ‘External Links in Worksheet’ warning?
2. How do you find and remove this link to stop the message from ever coming up again?

Answer 14

1. Data Validation rules that reference some external file path & filename that no longer exists
2. File > Info > Check for Issues > Check Compatibility > Copy to New Sheet > Ctrl + F to search for data validation > click on the items - if you see any with the Source field populated with external file references > click Clear All to get rid of the external link

Question 15

1. How can you show the same data set with multiple, independent scrollbars in the same worsheet & window?
2. What is the best practice when using this feature?
3. Why would this be useful?
4. How do you invoke this feature?

Answer 15

1. Split View
2. You should only split either verticially or horizontally - never both
3. If you have data at the far bottom or far right of the dataset with a formula and you want to see how the calculated figure gets updated as you manipulate the source data
4. Place your cursor to select an entire row or column you would like to split at > View ribbon tab > Split button

Question 16

1. Why would you ever use an approximate match option in VLOOKUP or XLOOKUP?
2. What is a limitation of this feature with VLOOKUP?
3. When are exact matches useful?
4. What is the best way to avoid h aving to use absolute cell references when using VLOOKUP or XLOOKUP?

Answer 16

1. If the lookup value is a number and you are searching for a ‘corresponding number’ to the right of the ‘search number’ based on numerical thresholds or ‘buckets’.

For example, if you had a pricing list of commission rates based on dollar thresholds and you wanted to get the corresponding rate for each number in your dataset.

2. If you use an absolute reference in a table array, the data in the first column must be sorted in ascending order.
3. If the lookup value is a text or ID - think of this as a database or column join or lookup
4. Use entire column references (e.g. A:A or K:L)

Question 17

1) By default, are cells Locked or Unlocked in Excel?
2) What is required to start locking down or protecting cells?
3) Where do you go to protect cells within a worksheet?
4) How do you password protect an entire workbook (file)?
5) How do you hide/protect worksheet tabs?

Answer 17

1) Locked
2) You must first unlock all the cells in the worksheet.
3) Review > Protect Sheet
4) File > Save As > Tools > General Options
5) First, hide any worksheets you want to hide, then Review > Protect Workbook (structure). This prevents anyone from unhiding or doing anything with other worksheets you don’t want them to.