Go Skills Microsoft Excel - Basic and Advanced
Suppose cells A1:A4 contain these values: Fred, Joe, Mary, John. What does the formula =HLOOKUP("joe",A1:A4,1,FALSE) return?
#N/A
There are 3 areas marked in the image below. Assuming that you were trying to locate a file that you worked on several months ago, which area would you want to explore?
2
In the chart below, how many series of data are shown?
3: two line series, and an area series
Which formula counts the number of non-blank items in the cells A1:A10 (A1 through A10)?
=COUNTA(A1:A10)
Suppose cell B9 contains a valid date. Which formula returns the end of the month?
=EOMONTH(B9,0)
Which is the best formula to add cells A1 and A4:A6 (A4 through A6) together?
=SUM(A1,A4:A6)
Which formula is used to generate the current date?
=TODAY()
How do you expand the width of the columns in a column chart (with one data series)?
Adjust the gap width
What does the command shown below actually do?
Undoes previous actions
Suppose you want a message to pop up each time a particular cell is selected. How do you make this happen?
Use a data validation input message
Which is the best method to round a calculation in a Pivot Table if a new column can't be added to the source data?
Create a calculated field that contains a rounding formula
What can be added to a pie chart to remove doubt about the size of the slices?
Data labels
Suppose you have selected columns B:E and you want them resized to fit your data without excess space. Which method is the most efficient?
Double click on any line between the column labels
How do you remove an item from a Pivot Table?
Drag it back from the Pivot fields into the field well
Which of the following commands would allow you to step through a formula to see how it is calculating?
Evaluate Formula
In the chart below the dates are standing vertically. What do we need to do to align them horizontally?
Expand the chart width
What does the formula =EDATE(DATE(2012,2,29),12) return?
Feb 28, 2013
What happens when you create a table in Excel?
Filters are automatically added to the header row
When it comes to security in Excel, by default all cells are...
Locked, but unprotected
Which function returns the most frequently occurring number in a data set?
MODE()
What is the MINIMUM Excel version in order to be able to use Dynamic Arrays?
Office 365 or any version after 2019
Where are the commands for recording and playing macros located?
On the Developer tab
Suppose column A contains a list of confectionery items. Which is the most efficient method to find anything containing "Chocolate"?
Select the Filter and type "choc" in the search box
Which is the easiest method to assign a name to a cell or range of cells?
Select the cell(s) and then type the name in the Name Box (to the left of the formula bar)
Suppose cell B12 contains the text "Golf Ball". What does the formula =FIND("ball",B12,1) return?
#VALUE!
Suppose cells A1:A4 contain these values: Fred, Joe, Mary, John. What does the formula =MATCH("joe",A1:A4,FALSE) return?
2
Demonstrate that you can apply the concepts from this video to other Excel functions. How many parameters are required for the PV function?
3
If B5 contains the formula =$A$1+B1, what would the formula read if copied to B6?
=$A$1+B2
Imagine that you have two workbooks, called Master and Data. A cell in Master contains a link to a cell in Data, with the formula ='C:\GoSkills\[Data.xlsx]Sheet1'!B5. While the Master workbook was closed, a new row was inserted at A4 in Sheet1 of the Data workbook, then saved and closed. Next time the Master workbook is opened, what does the formula read?
='C:\GoSkills\[Data.xlsx]Sheet1'!B5
Imagine that you have two workbooks, called Master and Data. A cell in Master contains a link to a cell in Data, with the formula ='C:\GoSkills\[Data.xlsx]Sheet1'!B5. While the Master workbook was closed, the Data workbook was moved to a new folder: D:\Courses\GoSkills. Next time the Master workbook is opened, what does the formula read?
='C:\GoSkills\[Data.xlsx]Sheet1'!B5 (but Excel will pop up a message that it can't update the links.)
Suppose cells A6 and B6 contain the times 8:00 AM and 4:00 PM respectively. Which formula calculates how many hours have elapsed between these two times?
=(B6-A6)*24
Which formula returns the arithmetic mean of the numbers in a data set?
=AVERAGE(A1:A10)
Which formula would you use to divide Total Sales by the number of Days for Oranges?
=B2/C2
What formula would return the product of 6 x 4 for the Apples row?
=B3*C3
Given the picture below, what would we want to enter in cell D5 to get the product of B5 and C5?
=B5*C5
Suppose cell A6 contains the time 8:00 AM, B6 the value 7.5 and D6 the formula =A6+C6 formatted as a time. Which formula for C6 returns the time 3:30 PM for D6?
=B6/24
Suppose you select cells B4:C5, and then enter =B$3+20 and press CTRL + Enter. What will C5 contain?
=C$3+20
Suppose you link to cell A5 on Sheet1 of a workbook called "Target.xlsx". When you initially create the link in Excel, how will it read?
=[Target.xlsx]Sheet1'!$A$5
Demonstrate that you can apply the concepts from this video to other Excel functions. What is missing in the following?
A closing parenthesis
What characteristics are required in the source data to create a Pivot Table?
A contiguous data range with headers
Given the image below, what would you expect to happen if you selected C10, then went to the Home tab, selected AutoSum from the Editing group, and chose Average from the list?
A formula would be entered in C10 that averaged the values of cells C5 through C9
What is a contiguous data range?
A range of data uninterrupted by blank rows or columns
Excel tables come with a new style of formulas called Structured Table Referencing. What happens when a classic A1 style of formulas that you are used to writing is entered in an Excel table cell?
A1 style formulas function as normal, but are automatically run down the entire column
Assume that you enter data in cell A6 and press Tab, then enter data in cell B6 and press Tab, then enter data in cell C6 and press Enter. Which cell will you find yourself in?
A7
Formatting a range of data as an Excel table can apply color banding and immediately add a name to the table (covering the entire table range), and also add filters. Which outcome is the most difficult to replicate on a range of data not set up as an official Excel table?
Alternate row color banding that updates automatically when new rows are inserted
Assuming you typed the following in the formula bar, what would be returned to the cell when you hit Enter?
B2+C2
Which method for copying cells results in just the formulas being copied?
Copying formulas, then choosing PasteSpecial and clicking Formulas
When working with the Scenario Manager, in order to preserve the original information, it's best to ...
Create a base case Scenario first to allow you to reset to the original values
By default, XLOOKUP defaults to which kind of matching method?
Exact match with #NA if item not found
You've written a cool XLOOKUP() function as follows: =XLOOKUP(I15,Items[Description],Items[Price]) Sadly, on occasion it is returning #NA as a result when the Items Description column doesn't hold the value in I15. How would you adjust the formula so that it returns "Price Not Found!" if the item description cannot be located? Formula A: =IFNA(XLOOKUP(I15,Items[Description],Items[Price]), "Price Not Found!") Formula B: =IF(ISERROR(XLOOKUP(I15,Items[Description],Items[Price]), XLOOKUP(I15,Items[Description],Items[Price]), "Price Not Found!") Formula C: =XLOOKUP(I15,Items[Description],Items[Price], "Price Not Found!")
Formula C
If you wanted to format numeric values with a less common foreign currency symbol, what would you have to do?
Go to the Format Cells dialog and change the symbol in the currency or accounting number formats
By default, Excel adds subtotals to Pivot Tables when value fields are added. How do you make subtotals appear at the bottom of the applicable groups?
Go to the Pivot Table Tools Design tab, and choose "Show all subtotals at bottom of group" from the Subtotals menu
Which user interface element is indicated by the red arrow?
Horizontal Scroll Bar
Which of the following data characteristics is critical in order to allow efficient sorting of data?
No entirely blank rows in the data set
You have selected a single cell in a range of data, and want to activate the filter command to filter rows out of the data set. Which characteristics must your data exhibit in order to filter correctly?
No entirely blank rows in the data set
Suppose you've just created two new Calculated Fields for your Pivot Table called Profit and Profit %. What steps are required for them to be added to the Pivot Table as shown here?
No steps are required, as the new Calculated Field is added automatically
Is this data a good candidate to be unpivoted?
No, it is already unpivoted
Suppose you've added a field containing numbers into the values area of a Pivot Table, but the display is not to your liking. Which is the best method to update the number format in the Pivot Table?
Right click the column on the Pivot Table, select Value Field Settings and then Numbers, and choose your preferred format there
You set up a query to unpivot the data shown in the image below as follows: Pull the data into Power Query ⇒ Right click the Customer column ⇒ Unpivot Other Columns. What will happen when you refresh the query if the original table is updated to have columns for Product 5, Product 6 and Product 7?
The newly added columns will be unpivoted as well
Suppose cells A2:B20 contain a table with sorted product numbers in column A and product prices in column B. What does the formula =VLOOKUP(A25,A2:B20,2) return?
The price for the product found in column A (or the closest product if the exact product number can't be found)
Suppose cells A2:B20 contain a table with product numbers in column A and product prices in column B. What does the formula =VLOOKUP(A25,A2:B20,2,FALSE) return?
The price for the product in column A if the exact product number corresponding to A25 can be found, or #N/A if not.
You are building a query that will unpivot a data set, and your intention is to use this table to drive several PivotTables. What should you ensure is removed from the data when you build the query?
Totals and subtotals
Assume you select cell D5 which contains a formula that is returning a #VALUE! Error. Which command will draw arrows to show all the cells that D5 is relying on?
Trace Precedents
Suppose you ended up with too many filters on a table and lose confidence that all items are showing. Which is the quickest method to ensure all filters are reset?
Turning off the Filter, then re-applying it
Suppose the formula =D7+F8/G8-H5+2 is in cell D10. Which is the best method to work out the value required in F8 to get a value of 850 from the formula?
Use Goal Seek to set D10 to 850 by changing F8
What would be the easiest way to select cells A5, B5 and C5?
Use the mouse, select A5, hold down the left mouse button and drag over to C5
When setting up a custom sort, you can sort based on more than just the Cell Values. What options do you have to Sort On?
Values, cell colors, icons
Which types of situation are most suited to recording an Excel macro?
Where the data-processing steps are repeated frequently and are identical every time the macro runs
Suppose you change something in your Pivot Table's source data. What are the implications?
You must manually refresh your Pivot Table in order to see the change show up there
Demonstrate that you can apply the concepts from this video to other Excel functions. Assuming we clicked C5 at this point in our formula, which parameter would it be used for?
kpi_name
What value is contained in cell B8?
65
Suppose cell B9 contains a valid date. Which formula returns the same day in the preceding month?
=EDATE(B9,-1)
You want to extract all columns from a table where the Group is equal to the value in J7 AND where the Years is equal to the value in J8 or L8. Which formula will achieve that result?
=FILTER(Sales,(Sales[Group]=J7)*((Sales[Years]=J8)+(Sales[Years]=L8)))
Which is the best formula to return the phrase "You win!" if both cell B6 equals 29 and cell C6 equals 10?
=IF(AND(B6=29,C6=10),"You win!","Try again!")
Which formula returns the value of cell C5 if this value is greater than 5?
=IF(C5>5,C5,0)
Which formula is guaranteed to return the phrase "You win!" if the value in cell B6 is 29?
=IF(OR(B6=29,C6=10),"You win!","Try again!")
Which is the best formula to return just the 5 characters on the left-hand side of the text in cell A6?
=LEFT(A6,5)
Which is the best formula to return just the 5 characters on the right-hand side of the text in cell A6?
=RIGHT(A6,5)
A5 shows a value of 1234.56. Which of the following methods would you use to round the value 1235?
=ROUND(A5,0)
There is data in the cells B4:B15, with the following formula used to subtotal the data in B9: =SUBTOTAL(9,B5:B8). Which would be best formula to ensure that we've summed up all values from B4 through B15?
=SUBTOTAL(9,B4:B15)
Assume that A1:A4 contains the result of a spilled array. What formula would you need in order to get the names to show the same results in a single row, across four columns?
=TRANSPOSE(A1#)
Suppose cells B5:C50 are selected and named "tblData", and B3 is named "lookup". The formula =VLOOKUP(lookup,tblData,2,False) is created in E5. If E5 is copied to E6, what is the formula in E6?
=VLOOKUP(lookup,tblData,2,False)
When working with a classic comment (or note in Office 365), Excel inserts a little mark in a cell to indicate that the cell has this comment (note). What does the mark look like?
A little red triangle in the top right of the cell
What is the most likely cause of this error message shown here?
A validation rule exists on the cell, but the rule's author didn't configure an error message
What is the most efficient way to update the "Collapsed" Custom View shown in the list below?
Add a new view, give it the name "Collapsed", and click Yes when asked if you'd like to delete it and continue
When using the FILTER() function, what does the * character allow you to do?
Add multiple conditions of which ALL must be true in order to include the record in the result
When using the FILTER() function, what does the + character allow you to do?
Add multiple conditions of which ANY must be true in order to include the record in the result
What do Excel's Outlining Tools do?
Allow spreadsheet sections to be grouped and compressed via easy-to-use controls
Suppose there is a small table with these values for the cells: A1 = A, A2 = B, B1 = C, B2=D. What does the formula =INDEX(A1:B2,2,1) return?
B
Suppose cells B24:B25 are grouped so they can be compressed to show the value in B26. When expanded, B26 has a top and bottom border on the cell. When compressed the top border disappears, but the bottom one doesn't. Where are these borders applied?
B25 and B26 both have bottom borders only
Combination charts are not restricted to only area and line combinations. In looking at the chart below, how would you expect to build it?
Begin by plotting a clustered column chart, then change the blue series chart type to a line chart
Suppose cell D5 has two conditional formatting rules in place as shown below. The first rule in the manage rules list turns the font blue if the value exceeds 10. The second rule in the manage rules list turns the background yellow and the font red if the value exceeds 20. The value in D5 is currently 25. What color scheme will the cell display?
Blue font with yellow background
How do you ensure that only dates, in a valid format, can be entered in a particular cell?
Create a data validation rule for the cell that restricts users to only entering dates
You have produced the following chart, but realized that not all of the names are showing in the chart. How would you fix it?
Drag the borders of the chart to make it taller
You have a PivotTable based on the Transactions table that you built with Power Query by appending several tables together. To add another table called MoreData to the end of the Transactions table, what should you do?
Edit the Transactions query and choose Append from the Power Query Home tab
You built a great query to clean up your data and land it to a table. Next month you get an updated file that uses the same structure. How do you update your query to point to a new file?
Edit the query, select the Source step, click the gear icon and browse for the new file
Suppose cells A1:A4 contain these values: 10, 20, 30, 40. What does the formula =VLOOKUP(15,A1:A4,1,TRUE) return?
10
Suppose cells A1:A4 contain these values: 10, 30, 20, 40. What does the formula =VLOOKUP(25,A1:A4,1,TRUE) return?
10
What does the formula =MONTH(DATE(2013,12,11)) return?
12
Suppose a cell contains the value 0.5. What is returned when this value is formatted with a date and time format?
1900-01-01 12:00:00 PM
Suppose cells A1:A4 contain these values: 10, 40, 20, 30. What does the formula =VLOOKUP(42,A1:A4,1,TRUE) return?
30
Assume that the following ranges contain spilled arrays as follows: - B1:D1 =TRANSPOSE(UNIQUE(Items[Category])) - A2:A8 =UNIQUE(FILTER(Items[Description],Items[Sales Price]>15)) Which formula is needed in B2 to dynamically populate the entire table as shown below? Formula A: =SUMIFS(Items[Sales Price],Items[Description],A2#,Items[Category],B1#) Formula B: =SUM(FILTER(Items[Sales Price],Items[Description]=A2#,Items[Category]=B1#)) Formula C: =SUMIF(Items[Sales Price], A2#, B1#)
Formula A
You have a scenario where you need to write a VLOOKUP-style approximate match, but you need to get the next LARGEST value if a match isn't found, not the next smallest value that VLOOKUP would produce. Which XLOOKUP function would make that happen? Formula A: =XLOOKUP(B17,Items[Description],Items[Description],"Not Found",1) Formula B: =XLOOKUP(I15,Items[Description],Items[Description],"Not Found!") Formula C: =XLOOKUP(B17,Items[Description],Items[Description],"Not Found",-1)
Formula A
Which formula would provide a dynamic array showing all columns of the Items table where the Category equals "Beer"? Formula A: =UNIQUE(FILTER(Items[Description],Items[Category]="Beer") ) Formula B: =FILTER(Items,Items[Category]="Beer") Formula C: =FILTER(Items,Items[Category]>>"Beer")
Formula B
Which formula would provide a dynamic array showing a unique list of Categories in a Products table where the Group is "Clothing"? Formula A: =FILTER(Products,Products[Group]="Clothing") Formula B: =UNIQUE(FILTER(Products[Categories],Products[Group]<>"Shoes")) Formula C: =UNIQUE(FILTER(Products[Categories],Products[Group]="Clothing") )
Formula C
In hopes of returning a product such as "Tee-Shirt - 50% Cotton", you have crafted a wildcard search using the following XLOOKUP formula: =XLOOKUP("5?% Cotton*",Products[Description],Products,"Not Found!") Assuming that the table is called Products, and Tee-Shirt - 50% Cotton is in the Description column, will it return that row of data or "Not Found!"?
It will return "Not Found!"
Suppose cell A10 contains text and you want to display it rotated on its side and centered vertically over A10:A20. Which method is the best?
Merge cells A10:A20, rotate the text 90 degrees and select Center Vertically
Suppose you want to hide the logic of the formula contained in cell H22. But you still need to show the workbook to other users. Which method is the best?
Modify the protection properties for H22 to Hide Formula, and then protect the worksheet
If column B is too narrow to show all the text you've typed into a cell, how could you expand it to display the text?
Move your mouse between the column B and column C headers, then drag the column wider
Imagine that you have a spreadsheet to print, and you want all columns to fit on one page while allowing as many pages to be printed as necessary for all rows to be displayed. Which method is the best?
On the Page Layout tab, set Width to 1 page
You create a new query that you intend to use as a "Connection only query". Which option should you choose from the Close & Load To... dialog shown below?
Only Create Connection
Suppose the formula =D7+F8/G8-H5+2 is in cell D10, and you want to review the potential outcomes where F8 is equal to 45, 85 and 97. Which is the best method or tool to be able to call up each option quickly?
Scenario Manager
Given the image below, what is the best way to get B10 to show the sum of the values from B5 through B9?
Select B10, go to the Home Tab and click the AutoSum button on the Editing group
Suppose you want to center the title you have in cell A6 over the data table in A7:G40. Which method is the best?
Select cells A6:G6, right click, choose Format Cells, and set the horizontal alignment to "Center Across Selection"
Which method can be used to link a chart title to data?
Select the chart title, press = and then select the cell
In the chart below the orange line appears to be showing on top of the blue line. What would we do to get the blue line in front of the orange line?
Select the chart ⇒ Design ⇒ Select Data. Select the blue series and click the "move down" arrow
Suppose you want to identify the top 25% and bottom 25% of values in a column of data. Which method is the best?
Set up a Top 10% and Bottom 10% conditional formatting rule, changing the % to 25% for each one
You have the following chart. What do you need to do in order to have the chart plotted with the lowest number of goals at the bottom and the highest number at the top?
Sort the data in A4:B11 in ascending order
A portion of Excel's user interface has been circled below. What is the name of that area?
The Font Group
If you wanted to customize your formatting to a high degree, where would you find the largest collection of formatting tools?
The Format Cells dialog box
A portion of Excel's user interface has been circled in the image below. What is the name of that area?
The Quick Access Toolbar (QAT)
Given this data validation rule, which of the following statements is incorrect?
The dropdown list will show the value "DValList_Drinks"
Suppose you begin recording a macro and make a mistake. What are the implications?
The mistake is recorded in the macro and will be played back each time you run the macro
Suppose cell D32 is named "Target" and B23 contains the formula =D6*Target. What happens when B23 is copied to C24?
The new formula is =E7*Target
Suppose cells A2:B20 contain a table with product numbers in column A and product prices in column B. What does the formula =VLOOKUP(A25,A2:B20,1,FALSE) return?
The number for the product in column A (or #N/A if the exact product number can't be found).
Imagine that you have a single-series column chart implemented in Excel by default. Which elements in the chart can be removed to make the chart more effective in terms of its power to communicate?
The vertical axis, but only if data labels for the columns are added
You've written a perfectly valid UNIQUE function in order to extract a list of unique items from your Items table. Why is it returning #SPILL! instead of displaying a list of the four unique values in your data?
There is data in cell K10
Which is the best reason to use a Custom View over other Excel features?
To build different looks for printing or editing that only show the data you need
Assuming you select cell D5 which contains a formula. Which of the following commands will show you the cells that rely on D5 for their calculations?
Trace Dependents
How difficult is it to break Excel's worksheet protection?
Very easy! Step-by-step methods are freely available on the internet and can be used with very little prior knowledge
Suppose cell D5 has two conditional formatting rules in place, as shown below. The first rule turns the font blue if the value exceeds 10. The second rule turns the background yellow and the font red if the value exceeds 20. What is required for a value of 25 to be displayed with a red font and yellow background?
Put the yellow rule first
Suppose cells A5 and A6 contain the numbers 48 and 40 respectively. For both cells the custom conditional format rule is =$A$5>45 which executes a red font. What font color will A6 show?
Red
What has been circled in the image below?
Ribbon Tabs
You have opened the Queries pane in your workbook, and can see that you have two queries as shown below. What is the fastest method to combine the two tables in order to create a new Transactions table with 1,340 rows?
Right click 2017 Sales ⇒ Append ⇒ choose 2018 Sales ⇒ OK
The chart below shows numbers to the right of the bars. What did the author have to do in order to do this?
Right click any bar ⇒ Add Data Labels
If your cursor is in the middle of the worksheet, what is the fastest way to call up this dialog box?
Right click on a cell, choose Format Cells, and click Border
You have the following chart and you'd like to convert the Budget series to an area chart. What is the first thing you need to do?
Right click the Budget series and choose "Change Series Chart Type"
By default, Excel automatically adjusts column widths on a Pivot Table to fit the data. (This is to avoid seeing ### symbols when a number is too large to be displayed in the cell.) If you don't want this to happen, how do you turn it off?
Right click the Pivot Table, choose Pivot Table options, and clear the "Autofit column widths on update" checkbox
The chart shown below has a black line beside the vertical axis. If we wanted to get the orange and blue series lines to touch the black axis line, what would we need to do?
Right click the horizontal (date) axis ⇒ Format Axis ⇒ Axis position ⇒ On Tick Marks
Suppose customer numbers (IDs) are in column A, invoice numbers in column B and total invoice amounts in column C. Which function is the easiest for discovering the total value of goods purchased by customers with a customer number above 1000?
SUMIF()
Suppose customer numbers (IDs) are in column A, invoice numbers in column B and total invoice amounts in column C. Which function returns the total value of goods purchased by a specific customer?
SUMIF()
How would we refer to the cell indicated in the image below?
C7
Suppose customer numbers (IDs) are in column A, invoice numbers in column B and total invoice amounts in column C. Which function returns the number of times a specific customer made a purchase?
COUNTIF()
Which of the following options will the user be able to type into the cell if it has the following data validation rule?
Cats or Raccoons
How can you soften the look of a pie chart so it doesn't visually overwhelm or dominate the rest of your report?
Change the pie slices so that the borders are plotted in color, but the slices are filled with white
Suppose rows 4:9 are grouped, as are rows 10:32, 12:17, 18:24 and 25:31. Which is the best method to show all the data rows?
Click the 3 in the outline
Which is the best method to sort a data set by multiple columns (i.e. a multi-level sort)?
Go to Data and click Sort and add as many sorting levels as needed
In order to enter data into a cell you must:
Have the cell selected, then start typing
Suppose cells D10:D50 contain a range of numbers. How do you quickly color all values over $5,000?
Set up a Greater Than 5000 conditional formatting rule for D10:D50
What happens when a date is formatted as a number, and why?
The date turns into a number representing the number of days elapsed since January 1, 1900
What is required in order a use the @mentions feature in the new Excel comments feature?
The document must be stored in SharePoint or OneDrive, and you must be using Office 365 (or a version of Excel greater than 2019)
Based on the data validation rule below, what will show up in the drop down list in the cell?
$A$17:$A$20
Suppose the value 134567 is in a cell. When this value is formatted with the custom number format ###-###-###, what does it look like?
-134-567
The pie chart below displays too many data series, and would be better displayed as a bar chart. What is the maximum number of series that should be plotted in a pie chart without it becoming cluttered and overly complicated?
3 series
Assume you open a workbook that contains a Power Query, but the Queries & Connection pane is not showing. How do you display it?
Go to Data ⇒ Queries & Connections
By default, the values in the Product # column of the table shown below will not have leading zeros. How would you force Excel to show them?
Go to Format Cells and select Number and then Custom and set the Type to 000000
Assuming that your version of Excel doesn't give you a default option formatting dates in a year-month-day format, how would you format the "Sales up to" column to display the dates as 2018-12-09, 2018-12-11 and 2018-12-10?
Go to Format Cells, choose Number and then Custom and set the Type to yyyy-mm-dd
When you are creating a new Power Query, how do you call up the dialog shown below so that you can choose where you want to place your data table?
Go to Home ⇒ Close & Load ⇒ Close & Load To...
How do you get Excel to print the date and time of the print job in the header for each page?
Go to Page Layout, and then Print Titles and Custom Header, and type "Printed " and click the date and time icons
Which is the most efficient method to modify a calculated field in a Pivot Table?
Go to into the Calculated Field list, select the field from the list, and click Modify
Suppose you want to apply a currency style to a set of numbers as quickly as possible. After having selected the numbers, what do you do next?
Go to the Home tab and click the $ on the Numbers group
How do you modify a custom style to force the changes through your workbook?
Go to the Home tab, click the Cell Styles menu (or the indicator in the bottom right corner of the Styles gallery), and then right click your Style and choose Modify
You have a workbook that contains legacy comments (notes) and wish to print the worksheet to show these notes as they are displayed in the image below. How do you accomplish this?
Go to the Page Layout tab -> Print Titles -> Comments -> As Displayed on Sheet (legacy)
How do you activate a Custom View in Excel?
Go to the View tab and Custom Views, and then select the view and Click Show
Your boss has given you a list of data and asked you to identify who your star customers are (your top 30%). How would you highlight only your top customers?
Highlight the column and create a new Top 10% rule, then adjust it to top 30%
Suppose you want to repeat the top 5 rows of your spreadsheet on every page printed. Where do you go to configure this?
In the Print Layout tab, click the Print Titles button in Page Setup
When using a wildcard search in XLOOKUP, what does the * represent?
It is a placeholder for any number of characters
When using a wildcard search in XLOOKUP, what does the ? represent?
It is a placeholder for any single character
What is the main benefit of using Styles?
It is easy to update all similarly formatted cells at once if it's needed
