Excel Flashcards
What is the box called which enables other options to be viewed in Excel?
Dialogue Box Launcher.
What shortcut in Excel, displays the Format Cells dialog box?
CTRL+1
What shortcut in Excel, using a number, applies or removes bold formatting?
CTRL+2
What shortcut in Excel, using a number, applies or removes italic formatting?
CTRL+3
What shortcut in Excel, using a number, applies or removes underlining?
CTRL+4
What shortcut in Excel, using a number, applies or removes strike-through?
CTRL+5
What shortcut in Excel, using a number, hides rows?
CTRL+9
What shortcut in Excel, using a number, hides columns?
CTRL+0
What shortcut in Excel, using a letter, displays the Go To dialog box?
CTRL+G
What function command displays the Go To dialog box?
F5
What shortcut in Excel, using a letter, opens a new, blank workbook?
CTRL+N
What shortcut in Excel, using a letter, displays the Open dialog box to open or find a file?
CTRL+O
What shortcut in Excel, using a letter, selects all cells that contain comments?
CTRL+SHIFT+O
What shortcut in Excel, using a letter, displays the Print tab in Microsoft Office Backstage view?
CTRL+P
What shortcut in Excel, using a letter, opens the Format Cells dialog box with the Font tab selected?
CTRL+SHIFT+P
What shortcut in Excel, using a letter, saves the active file with its current file name, location, and file format?
CTRL+S
What shortcut in Excel, using a letter, displays the Create Table dialog box?
CTRL+T
What shortcut in Excel, using a letter, applies or removes underlining?
CTRL+U
What shortcut in Excel, using a letter, switches between expanding and collapsing of the formula bar?
CTRL+SHIFT+U
What shortcut in Excel, using a letter, displays the Paste Special dialog box?
CTRL+ALT+V
What shortcut in Excel, using a letter, closes the selected workbook window?
CTRL+W
What shortcut in Excel, using a letter, repeats the last command or action, if possible?
CTRL+Y
What shortcut in Excel, using a function key, displays the Excel Help task pane?
F1
What shortcut in Excel, using a function key, displays or hides the ribbon?
CTRL+F1
What shortcut in Excel, using a function key, inserts a new worksheet?
ALT+SHIFT+F1
What shortcut in Excel, using a function key, displays the Spelling dialog box to check spelling in the active worksheet or selected range?
F7
What shortcut in Excel, using a function key, enables you to add a nonadjacent cell or range to a selection of cells by using the arrow keys?
SHIFT+F8
What shortcut in Excel, using a function key, displays the Save As dialog box?
F12
What are colours used to fill in cells in Excel called?
Shading
What is the benefit of shading and borders?
Contrast.
How does contrast assist users of Excel?
It helps them to interact and engage with the information.
If a row of cells containing title information is shade blue with white lettering and the row made a little taller, why is it made to look different from other rows?
To aid contrast.
What is the current formatting style for Excel?
xlsx
How do you select the rest of a row in Excel?
Ctrl + shift + right arrow
How do you ensure lines in a cell are consistently indented?
Use the indent button
What does ‘Transpose’ do in Excel?
It makes the rows of one table into the columns of another table
How do you transpose in Excel without using a formula?
Highlight cells – copy – paste special – transpose
What button can you press to merge and centre?
Merge and centre button
What button do you press to make data go at an angle?
‘Orientation’ button
What function key does the same as Ctrl + Y?
F4
Using a formula, how would you add cells H8 to H22 in Excel?
=SUM(H8:HH22)
What is a Relative Reference in Excel?
A reference to a cell or cells which is relative to other cells. The values in a relative reference will change is copied to another cell.
What does “:” mean in Excel?
Through
How would you say the formula =SUM(H8:H22)?
Sum of cells H8 through H22.
How can you automatically transfer a formula in a cell to bland cells below it?
Double click the autofill function on the cell.
Can double clicking on the autofill function in a cell be used to copy formulas across a page as well as down a page?
No
If you wish to copy a formula in a cell to 100s of blank cells immediately below it, what is the quickest way to do this?
Double click the autofill function on the cell with the formula.
What do the green arrows on an autofill mean?
That there is a potential error.
What should you do with a green arrow on autofill?
Click the exclamation mark that results and decide what option such as ‘ignore’ is best.
How do you respond to green arrows on multiple contiguous cells where any error is likely to be the same?
Highlight multiple cells, click on the first of these and choose the relevant option for all the cells.
How can a comma be used in a formula?
To select cells which are not contiguous eg =SUM(C5, C7,E6,F7,G6).
What is the ‘average’ formula in Excel?
=AVERAGE
Show the ‘average’ formula be used for cells C5 though C9 in Excel?
=AVERAGE(C5:C9)
What is the benefit of the fx button in Excel?
Provides quick access to formulas, reducing the possibility of errors.
What is the formula for maximum in Excel.
=MAX
Show the ‘maximum’ formula be used for cells C5 though C9 in Excel?
=MAX(C5:C9)
Show the ‘minimum’ formula be used for cells C5 though C9 in Excel?
=MIN(C5:C9)
Show the formula to calculate the number of cells with numbers in them for cells C4 though G9.
=COUNT(C4:G9)
Show the formula to calculate the number of non-blank cells in cells C4 though G9.
=COUNTA(C4:G9)
What does the formula COUNTA not count?
Empty cells
Give an example of how the formula COUNTIF could be used in Excel.
If you only wanted to identify the number of cells referring to a specific project in a large spreadsheet.
What is shown at the bottom of an Excel spreadsheet if numbers are highlighted?
Formulas such as COUNT and AVERAGE
How do you alter the formulas at the bottom of the page when cells with numbers are calculated?
Right click the bottom of the page and select.
What happens if you press the arrow key on the right side of the AutoSum button?
Other options such as Average appear.
Other than Average, Max and Min, and More functions what formula option appears if you press the arrow key on the right side of the AutoSum button?
Count Number
Other than Average, Max and Count Number and More functions what formula option appears if you press the arrow key on the right side of the AutoSum button?
Min
Other than Average, Min , Count Number and More functions what formula option appears if you press the arrow key on the right side of the AutoSum button?
Max
Other than Max and Min, and Count Number and More functions what formula option appears if you press the arrow key on the right side of the AutoSum button?
Average
What does choosing More formulas when pressing the arrow by the AutoSum key give you access to?
The formulas screen where you can choose other formulas.
Will Autosum work if you highlight the cells to be counted and a blank cell?
Yes, just so long as the cells are contiguous
If you do AutoSum highlighting both numbered cells and cells below and to the right, will pressing AutoSum add totals in both the blank cells at the bottom and at the right?
Yes
If column H contains numbers and cell I2 contains 4%, how can you copy a formula in cell i5 to cells I6:I8 so that all of the cells refer to cell I2?
=H5*I$2
Pressing what function key while highlighting cell I2 will result in I2 becoming an absolute reference ie I$I2?
F4
If the formula is =G5*H2, what does the formula become if the function key F4 is pressed while H2 is highlighted?
=G5*$H$2
Give an example of an Autofill List in Excel using Months of the year.
Jan, Feb, March . . .
Give an example of an Autofill List in Excel using days of the week.
Mon, Tues, Wed . . .
How would you create an Autofill List in Excel showing only week days?
By using a Custom Autofill List.
How do you find the Edit Custom List menu in Excel?
File- Options – Advanced then look under the heading General and pick Edit Custom List.
What do you use the Edit Custom List menu in Excel menu in Excel for?
To edit and create Custom lists to use with Autofill.
If you had a list of items on an Excel Spreadsheet, how would you make it into a custom list?
By highlighting the cells, then going to Edit Custom List and choosing the import Custom List option.
What are the preset formats that Excel gives you, called?
Styles
Using Excel, how do you keep a portion of a screen visible while the rest of the screen scrolls?
Pressing the Freeze Pane button.
In which tab is the Freeze Button located in Excel?
View Tab
What does the Freeze button do using Freeze Pane?
Freezes the rows above the selected row.
Other than Freeze Pane and Freeze First Column, what other option is there in Excel?
Freeze Top Row.
Other than Freeze Pane and Freeze Top Row, what other option is there in Excel?
Freeze First Column
If you wish to freeze the rows above and columns to the left of cell B5 in Excel, how do you do it?
Select cell B5 and then select the Freeze Pane option.
How can you have 4 scroll bars in the same Excel spreadsheet?
By selecting the Split button.
If the Split button is pressed, do panes selected using the Freeze button, remain?
No
How do you look at multiple worksheets at the same time?
By pressing the ‘Arrange All’ button.
What other option, other than ‘Tile’, ‘Horizontal’, and ‘Vertical’ is available when pressing the ‘Arrange All’ button?
‘Cascade’.
What is the advantage of using the ‘View Side by Side’ button in Excel?
It is possible to have two spreadsheets which move at the same time when synchronised.
What does the New Window button on the View Tab in Excel do?
Opens a 2nd Window.
What is the advantage of using the New Window button in Excel?
It enables different places on the same document to be viewed at the same time.
Can you hide rows and / or columns so that only people with the password can see them in Excel?
Yes
If you hide rows and / or columns what gets copied when you select the visible rows and / or columns and press Copy?
Only the visible cells.
Does the Hide button in Excel, hide rows and / or columns in Excel?
No
What does the Hide button hide in Excel?
Windows (not rows and / or columns).
How can you make it so that it is not so obvious that rows and / or columns are hidden in Excel?
By deselecting the ‘Show Rows + Columns’ box in Excel.
Where is the ‘Show Rows + Columns’ box in Excel?
Go to File -Options – Advanced
Other than right clicking on the tabs at the bottom of screens in Excel, how can you select these tabs ready for renaming?
Double click on them
Other than being easier to see, what is the other benefit of colouring the tabs on worksheets in Excel?
It makes them seem more 3 dimensional.
Other than more 3 dimensional, what is a benefit of colouring the tabs on worksheets in Excel?
It makes them easier to see.
Other than bevelled, what is another term for making something seem more 3 dimensional?
More of a gradient.
In what field is gradient used as a term to describe how 3 dimensional something appears?
Graphic design.
Other than more of a gradient what is another term for making something seem more 3 dimensional?
Beveled.
What function key adds another worksheet when there re existing worksheets open in Excel?
Shift + F11
How do you copy a worksheet in Excel?
Right click, select move or copy, select where it will be copied to and press the Copy Box.
Can you copy a worksheet to another workbook in Excel?
Yes
How do you copy a worksheet to another workbook in Excel?
Right click, select move or copy, select the workshop it is to be copied to and press the Copy Box.
How can you select only part of the wording of a cell other than by highlighting?
By double clicking on the relevant part of the cell.
Under what tab in Excel are the Protection options found?
Under the Review Tab
The Protection button has options to what?
What cannot be changed.
Where can you find the option for narrow or wide margins on the Print Page in Excel?
Under the Margins setting.
Where is the link to the Page Set Up page on the Print Page in Excel?
Below Margins and Settings on the left-hand side of the Print Page.
From the Print page, what is the best way to adjust margins visually in Excel?
By clicking the Show Margins Box on the right-hand side of the Print Page
What is the benefit of using the Show Margins box rather than Page Set Up to adjust margins in Excel?
Margins are easier to see when adjusting manually.
Under what tab other than File (Print) are the Print Options in Excel?
Under the Page Layout Tab.
Where can Page Layout and Page Break buttons be found in the Layout tab in Excel?
In the Top left corner
If you wanted to clear the Print Area in Excel what button in excel would you select?
The Clear Print Area option for the Print Area button
What is the easiest way to ensure the pages are centred in Excel?
From Page Set Up, selecting the Centre on Page option in the Margins tab.
How do you set up Excel, so certain columns and rows are printed on all pages printed?
Go to Page Layout – Select Print Title Button or click the Dialog Box Launcher – in Print Set Up select sheet and select the rows and columns to be printer on every page.
What is the Select Title Button similar to, but for Printing?
The Freeze Pane Button.
What is similar and what is different if Open Office were used rather than Excel?
Where you find items will be different but the ability to do things will be the same.
Does the Normal View and the Page Layout view have the same options in Excel?
No
Where can the Page Layout View and Normal options be found in Excel?
In the bottom right hand corner.
What is a contextual tab in Excel?
A tab which appears only in the context of what was clicked on.
When does the Design View tab appear in Excel?
When the header or footer is clicked on the Page Layout View.
What is another way of saying Page header / footer buttons?
Page header / footer elements.
What page number elements are there in the Design View?
Page Number and Number of pages
What buttons are there in Design View specifying file location?
File Path and File Name elements.
What button in the Design View in Excel takes you between the header and the footers?
Go to Header and Go To Footer buttons.
The sheet name will show what in Excel?
The worksheet name specified on the tab.
What Is the purpose of the Picture element in Excel?
To enable logos etc to be added to the header and footer.
What happens to Picture elements in Excel if the picture is too large to fit the header or footer?
The picture will spill over to the main spreadsheet.
What does track changes appear to have been replaced by in Excel?
Co-Authoring
Can you track changes in Co-authoring in Excel?
No
Under co-authoring in Excel what can you see if you and another person have a file opened at the same time?
Each other’s selections and changes as they happen.
Under co-authoring if a file is stored in the cloud, what can be seen so that you can see each other’s changes?
Past versions of the file.
When Excel had track changes, how did it differ from that in Word?
Unlike Word, only changes to content not format could be seen.
When adjusting the size of the header / footer what is it useful to use in Excel?
The Ruler
Where can you find the Ruler in Excel?
Under the View Tab.
How do you ensure the Ruler is available in Excel?
Go to view and check on the Ruler box.
How do you print Comments in Excel?
From Page Set up under Sheet, click on the ‘Print Command’.
Other than printing ‘As Displayed on Sheet’ what is the other option in Excel for printing Comments?
‘At End of Sheet’.
Other than printing ‘At End of Sheet’, what is the other option in Excel for printing Comments?
‘As Displayed on Sheet’
In what tabs are the Sort and the Filter buttons?
Home and Data.
What is different between Excel now compared with earlier versions of Sort?
No longer need to highlight the data to sort and where it is sorted. Excel now assumes if you want to Sort and have clicked on one cell, you also wish to sort the other cells in that row together. There is also now a box where you confirm whether or not the top row specified the headers for the Sort.
Can multiple levels of sort occur in Excel?
Yes
Where there are multiple levels of Sort, how does the Sort occur?
By applying the initial Sort first and then autocorrect later items without affecting the results of the first Sort.
Where do you find the Autocorrect Option in Word and Excel?
Under File – Options - Proofing
How do you alter the level of the Sort in Excel?
By using the up and down arrows
How do you select the top 25% using a filter?
Select filters – Number Filters – top 10 and then from this dialogue box select Top -25 – percentage
How do you remove filtering in Excel?
By clicking on the filter button again.
How do you do Conditional Formatting in Excel?
By pressing on the Conditional Formatting button.
On what tab is the Conditional Formatting button found in Excel?
Home tab
What option within Conditional Formatting in Excel do we choose to specify the formatting rule we are to apply?
Highlight Cell Rules or Top / Bottom Rules.
If we wished to show formatting with coloured bars where the length of each bar showed the extent to which a condition was met in Excel, what option within Excel should you choose?
Data Bars
How do Colour Bars within Conditional Formatting differ from Data Bars within Excel?
Rather than using length of bar, they use colour to show the extent to which a condition is met in Excel.
How does the Icon Set option differ from the Data Bars or Colour Scales in Excel?
It uses Icons rather than Colours.
What is the benefit of using Conditional Formatting?
Key items are much easier to identify if colours and Icons are used.
How do we refine and alter Conditional Formatting in Excel?
By using the Manage Rules option within Conditional Formatting.
When using the Manage Rules option within Conditional Formatting, and it appears to show that there is no Conditional Formatting where there should be, what should you check within the Manage Rules Dialogue Box?
Whether ‘Current Selection’ has been selected rather than ‘This Worksheet’.
Under which tab are Charts found in Excel?
Insert
What Contextual tab becomes available when a chart is created in Excel?
Chart Tools
What contextual sub tabs become available when a chart is created in Excel?
Design and Format
How can you move a chart to a separate page in Excel?
By clicking on the ‘Move Chart’ button on the right side of the Design sub tab.
What does the switch data button do in Excel?
Allows you to change the data rows in the chart to columns – similar to the ‘Transpose’ button for data.
What can the Quick Layout button be used for in Excel?
To assist with choosing the layout for the Chart
What do the chart legends do in Excel?
Show you what each of the featured items in the chart mean.
What is the ‘Add Chart Element Button’ used for in Excel?
To add items such as gridlines, data labels etc.
What is labelling each individual bar it having their name above them rather than as a legend to the side called in Excel?
Chart Labels
Where do you find the ‘Data Table’ button in Excel?
Under ‘Add Chart Element’.
What does the Data Table Button allow you to do?
To add the data the chart relates to below the Chart and separate from the main Excel data.
What is the ‘Page Layout Themes’ button used for in Excel?
To change the colours on the chart.
How can you change colours for individual bar types in Excel?
By clicking on one of the bars and choosing Autofill from the home page.
How can you select only one bar for colouring in Excel?
Clicking twice on the bar.
How can you select more than one bar but not all of the same type for recolouring in Excel?
Double click on each bar you want recoloured and then press Autofill.
How can you improve the titles in Charts in Excel?
By using Word Art Quick Styles.
Can you add pictures to charts in Excel?
Yes
Can you add pictures to individual parts of a graph?
yes
Where can the Table button be accessed from in Excel?
From the Insert Tab.
What does creating a table save time on in Excel?
Filtering, formatting, creating and labelling formulas.
What happens when you press the Table button, after confirming the correct range for the table in Excel?
A formatted table is automatically created with easy options for filtering.
What contextual tab is created when a table is created in Excel?
Design tab
What is the advantage of the banded rows or columns in Excel?
They make spread sheet numbers and formulas easier to read, particularly if the spreadsheets are large.
What does banded mean for the purposes of Excel tables?
The colours of rows or columns alternate, typically between lighter and darker.
What does the clicking the ‘Total Row’ check box in Design view do in Excel?
It adds a Total Row to the table where calculations can be easily performed.
Are there other options that can be selected, in the Totals Row other than Sum in Excel?
Yes
When creating formulas in a table, what is automatically done for Tables in Excel?
The formula parts are assigned Names
What does the Convert to Range button do for tables in Excel?
It enables the data to no longer be in a table but with formatting retained.
Where can you name your table in Excel?
On the left hand side of the Design tab.
What is the recommendation concerning Tables in Excel?
That the Table option should usually be selected because of the formatting.
What is said to be the advantage of pivot tables in Excel?
They enable complex statistical analysis to be done without being a statistics maths wizard.
What should you always do first with Pivot Tables in Excel?
Always identify your values (numbers) first.
What word is used in Excel which means numbers?
Values
What suggestion is there advising how to become good at Pivot Tables in Excel?
Experiment as much as possible, as there is very little in the way of consequences if things are mucked up.
How can you reduce the number of items showing in a Pivot Table in Excel?
Use filters.
How can you show Pivot Table information graphically in Excel?
By using Pivot Charts
How do Pivot Charts differ from other charts in Excel?
They are based on Pivot Table information in Excel and it is possible to filter chart items more quickly, otherwise they are the same.
How can you subtotal items in Excel?
By using the Subtotals option for the Outline Button.
Where is the Outline button found in Excel?
Under the Data tab, on the right-hand side.
If you want to each of the subtotals on a different page, how do you do it in Excel?
By clicking the option in the Subtotals dialogue box.
What would be an example of the benefits of having page breaks for subtotals in Excel?
Different clients could be shown on each page.
How could you show more than one subtotal for instance both Sum and Average?
By unclicking the replace subtotals box in the Subtotals dialogue box.
Where can Shapes be found in Excel?
Under Illustrations on the left-hand side of the Insert Menu.
What contextual tab appears when using the drawing tool in Excel?
Drawing Tools - format
Where do you go to add colour to a line you’ve drawn in Excel?
Insert - Drawing Tools – Shape Outline Colours
How do you alter line thickness in Excel?
Look at the Options for Weight from the drop-down menu under Shape outlines.
How do you re-orient shapes in Excel?
By clicking on the end of the shape.
How do you find the text box in Excel?
Under Insert - Text – Text box
How do you add text in a text box in Excel (for a chart or picture)?
Having selected the text box, click on the chart / picture and start typing.
Where can you change the colour of a shape in Excel?
Under shape styles in the Drawing Tools Contextual Tab.
What Contextual Tools is there for Pictures?
Picture Tools – Format
What button do you push to change the colour of a picture in Excel?
Colour
Are you able to crop to shape in Excel?
Yes
Where is Smart Art found in Excel?
Under Illustrations under the Insert Tab.
Can you change the order of items in Smart Art?
Yes – up and down in order or from right to left in Excel.
In what tab do you find the Text to Column button?
Data
What is the effect of unmarking the tab box and marking the space box in stage 2 of the text to column wizard?
2nd names are moved a consistent space in the data.
In what tab can the Data Validation button be found?
The Data tab.
What role does the Validation Criteria page on the Data Validation box?
It is where the selection criteria for the Data Validation is specified.
When you create a list on the Validation Criteria page of the Data Validation box, what should each of the items in the list be separated by?
Commas
What is the Input Message page on the Data Validation box for?
To inform / remind people as to the criteria for Input.
What is the role of the Error Alert page on the Data Validation Dialogue Box?
As a page where an alert can be created if the wrong data is entered and if hosen, also prevent that data from being entered.
Is it possible to create data validation using a list which already exists in Excel?
Yes it is, by pressing down on the arrow in the Data Validation box and specifying where the relevant series is.
Generally if you wish to delete data, formats etc what is a way of doing it using an Excel button?
Using the delete button the right side of the Excel home page.
How do you delete the data validation restrictions quickly?
By clicking the clear all button on the Settings page of the Data Validation Dialogue Box.
How do you print formulas rather than the resultant values in Excel?
Push the show Formulas button on the right side of the Formulas tab. If the sheet is then printed, it will print with the formula rather than the value.
What is the advantage of inserting an Excel Spreadsheet into a PowerPoint presentation as an Object
It enables the full power of Excel to be available from within the PowerPoint rather than being limited to the amount that will fit on a slide, particularly if the save as icon option is selected.
What is a Pivot Table?
In its most basic form, a PivotTable takes data and summarizes it so you can make sense of it, all without typing any formulas!
What are the first steps in making a Pivot Table?
Name your columns and make sure there are no duplicate or blank cells.
What word is used for Pivot Tables rather than column?
Field.
Why is the word field used for columns when using Pivot Tables?
It’s just a traditional term used when working with data and database applications.
If one of the fields in your data contains numeric values, and a Pivot Table totals it up, what is the field called?
A Pivot Table value field.
What does Trace Precedents in the Formulas Tab do?
Shows which cells affect the outcome of your formula.
What does Trace Dependents in the Formulas Tab do?
Shows which cells your formula affects
How does the evaluate formula button work?
Starting at the beginning it shows what happens when each section is evaluated
There are a number of times I’ve had formulas break and without _______________, I would never have discovered why.
Evaluate Formula
When would the formula =IFERROR(A1/B1, “Invalid Formula”) apply?
When B1 was zero.
What are Volatile Functions?
How do you name a cell or range of cells in Excel?
Selecting it, and then typing the name you want to give it in the name box
How can you manage the names that you have created in your workbook in Excel?
With Name Manager
Where can Name Manager be found in Excel?
In the Defined Names section of the Formulas Layer of the Ribbon.
What is an example of how the IF function could be used in Excel?
To apply a variable discount to a sale based upon the customer involved.
Write the IF Function for a case where we want to provide a 30% discount if 3 or more items are purchased and 0% otherwise starting with cell E3 containing quantity and then applying it for the cells below.
=IF(E3>=3,30%,0%)
What does the VLOOKUP function enable you to do?
Lookup values from a range of cells based upon a numeric key value in the left-hand column.
What does the 2 in this VLOOKUP example signify?
That we want to use the values in the 2nd column of the table as the rates for VLOOKUP.
If we include one function inside another function in order to create more complex functions, what type of function is this?
A Nesting function
Where might a nesting function be used?
Where a different discount is given for 2 items rather than 3 items and no discount is give for only one item.
What do Sparklines provide in Excel?
A quick visual representation of your data.
How can you quickly add in Sparklines?
By selecting your data and then choosing the Sparklines option from the “Quick Analysis” button.
When are trendlines a useful addition to your chart?
If it is measuring change over time.
How can you add in a trendline to a chart?
By right clicking anywhere on your chart data, and choosing “Add Trendline…”
How can you prevent an Excel Workbook from being opened.
When Saving – choose more options and choose General Options.
How do you remove duplicates in Excel?
Using the ‘Remove’ Duplicates button from the Data tab.
In what tab is the Remove Duplicates button found?
Data
‘Goal Seek is the __________ of the Microsoft Excel data analysis tools.’
‘Goal Seek is the __________ of the Microsoft Excel data analysis tools.’
Simplest
Give an example of using Goal Seek in Excel.
If you know that your goal is to achieve a sales total of $20,000 and you are selling bottles of water for $2.80 each, then you can use goal seek to work out how many bottles of water you need to sell in order to meet your goal. You can perform a Goal Seek by clicking on the What-If Analysis drop down button under the Data tab, and choosing Goal Seek. Choose the cell to set, which will be our Total Sales cell. Choose your target value, which will be 20000, and then choose the changing cell, which will be the Water bottles sold cell. Your result will then be calculated and if you are happy with it you can click “OK” for it to be used or “Cancel” if you want to try something else.
Where is Goal Seek found in Excel?
Under What If Analysis in the Data tab.
Where is the Scenario Manager tool found in Excel?
Under What If Analysis in the Data tab.
Why are Pivot Tables considered one of the most important and powerful tools in Excel when it comes to data analysis?
If you’re doing analytics in Excel and you’re not using Pivot Tables, you’re making life _______ times harder than it needs to be.
10,000
Give 5 reasons why this Pivot Table Structure is good
Give 5 reasons why this Pivot Table Structure is bad
What is a helpful tool in Pivot Tools – Analyse?
Selecting the Entire Pivot Table
What is selecting the Entire Pivot Table useful for?
Copying and Pasting to duplicate an entire Pivot Table.
What is the benefit of copying an entire Pivot Table onto the same sheet?
What is the benefit of copying an entire Pivot Table onto the same sheet?
Any change that you make within that box will be captured by the _______ command. Any change that you add outside of that box like adding data to rows 101,102, 103 or pasting new data into columns C, D, E or F. Those will not be captured by _______. For changes like that you need to use the second option which is change data source and what this does as you might expect, it allows you to ________ the Pivot outside of that source range or table so for instance stacking on new columns or rows of data. A common reason this is used is when you have something like time series data where you constantly need to be taking new data on to capture the most up to date data. When you have a case like that, the change data source and we’ll talk though some pro tips specifically related to working with growing data in the following lecture as well.
Refresh
A Pro Tip when you are dealing with dynamic data
When you are dealing with dynamic data you can either format your source data as a ______or you can use column only range references, so no row references to help you work with data that is changing.
Table
When you are dealing with dynamic data you can either format your source data as a table or you can use ___________, so no row references to help you work with data that is changing.
column only range references
There are two efficient ways of accommodating dynamic data in a Pivot Table.
The first is to remove_____ numbers from our source reference which will essentially tell Excel to pull in any _____s from within these columns so that you can simply refresh rather than adjusting that reference each time.
Row
Alternatively, format source data as a _____ rather than a range.
Type the name of the ______ rather than the range of cells.
To get really advanced could lead link ______ to a SQL or other database.
table
What does the formula =IF(OR(F2=”Rain”,F2=”Snow”),”Wet”,”Dry”) mean?
If cell F2 = “Rain” or ”Snow”, then mark this cell as Wet, otherwise mark it as dry.
What is a formula in Excel which returns “Wet”) if G2 is “Rain” or “Snow”, and “Dry”, otherwise?
=IF(OR(F2=”Rain”,F2=”Snow”),”Wet”,”Dry”)
What does the formula =IF(AND(D2=”Yes”,C2>0),”Snow”,IF(AND(D2=”No”,C2>0),”Rain”,”None”)) mean?
If there is precipitation and the temperature is below freezing then return “Snow”. If there is precipitation and the temperature is above freezing then return “Rain” otherwise return that there is no precipitation.
What is the formula in Excel which returns “Snow” if the temperature is below freezing and there is precipitation, rain if above zero and precipitation, otherwise “none”?
=IF(AND(D2=”Yes”,C2>0),”Snow”,IF(AND(D2=”No”,C2>0),”Rain”,”None”))
When writing ____________ especially if you have a number of different logical tests all stacked together you can just copy and paste pieces of your formula to save yourself some time so rather than rewriting IF AND and IF OR statements from scratch just copy and paste and build your formulas that way.
Nested functions
When writing nested functions especially if you have a number of different logical tests all stacked together you can just co pieces of your formula to save yourself some time so rather than rewriting IF AND and IF OR statements from scratch just ____________ and build your formulas that way.
copy and paste
What is the Pro Tip concerning nested functions and repetitive pieces?
When writing nested functions, copy/paste repetitive pieces and tweak individual elements to save time (rather than starting from scratch).
What is the Pro Tip concerning nested functions and tweaking individual elements?
When writing nested functions, copy/paste repetitive pieces and tweak individual elements to save time (rather than starting from scratch).
Please correct this formula:
=IF(AND(I2=”Yes”,H2>0),”Snow”,=IF(AND(I2=”No”,H2>0),”Rain”,”None”))
=IF(AND(I2=”Yes”,H2>0),”Snow”,IF(AND(I2=”No”,H2>0),”Rain”,”None”))
In a nested function, should there usually be “=” before the second IF?
No
How do you evaluate a case where a statement is not true?
By using either the NOT statement or a “<>” operator
What does =IF(NOT(C2=0),“Wet”,“Dry”) do in Excel?
Defines Conditions = “Wet” if the amount of precipitation is NOT equal to 0.
How do you write a NOT statements in Excel which states a cell should say Wet if cell C2 does not = 0, and states “dry” if cell C2 = 0?
=IF(NOT(C2=0),“Wet”,“Dry”) do in Excel?
What does =IF(C2<>0,“Wet”,“Dry”) do in Excel?
Defines Conditions = “Wet” if the amount of precipitation is NOT equal to 0.
How do you write a formula using a “<>” operator in Excel which states a cell should say Wet if cell C2 does not = 0, and states “dry” if cell C2 = 0?
=IF(C2<>0,“Wet”,“Dry”)
(Talking about using the“<>” operator).
The second way to accomplish the same thing in what in my opinion is a more straight forward approach is to use the ________ sign.
And its just a little bit cleaner. Syntax is more straight forward, easier to comprehend and you don’t introduce these extra parentheses that you would by using the NOT statement.
not equal to
(Talking about using the“<>” operator).
The second way to accomplish the same thing in what in my opinion is a more straight forward approach is to use the not equal to sign.
And its just a little bit cleaner. Syntax is more straight forward, easier to comprehend and you don’t introduce these extra ___________ that you would by using the NOT statement.
parentheses
There are a couple of implications of doing that. The first is just in the context of design and polish. It can be nice as a __________ formatting tool or trick. It can also be really useful on the data side and the back end so you may want to categorise your data consistently whether you’re seeing an error message or not, or if it’s a numerical field you’re working with and you want to be able to make statistical operations on that column of data sometimes have a N/A value in their will prevent you from properly calculating the values, so you may want to replace the n/a with something like a zero or another value.
Front end
There are a couple of implications of doing that. The first is just in the context of design and polish. It can be nice as a front-end formatting tool or trick. It can also be really useful on the data side and the back end so you may want to categorise your data consistently whether you’re seeing an error message or not, or if it’s a numerical field you’re working with and you want to be able to make statistical operations on that column of data sometimes have a _______ value in their will prevent you from properly calculating the values, so you may want to replace the ________ with something like a zero or another value.
N/A
So the tip here is to write your _________ first and at the end your last step will be to wrap it in an IFERROR statement.
Full formula
So the tip here is to write your full formula first and at the end your last step will be to _____ it in an IFERROR statement.
wrap
What type of formatting is the IFERROR statement particularly useful for?
Front-end formatting
Why might you choose ”Other” as the message if an error rather than “Yes” or “No” as a response to an error where “Yes” and “No” are the two options in the field?
Because you’re unsure which of the options “Yes” or “No”, would be correct.
For this lecture lets focus on the Design tab. Now the style options really speak for themselves. Just like you can format a table, you can also format a ________. with all of these different stock templates that Excel provides. Personally, I like to keep it simple and go with one of the light options. If you don’t want any formatting at all you can choose the upper left option. Let’s just stick with something like this. And then other style options here. You can turn on or off the shading on your row or column headers and you can add banding to your rows or columns to help differentiate those. Personally, I don’t typically use banded rows or columns but totally subjective. Up to you.
Pivot Table
For this lecture lets focus on the Design tab. Now the style options really speak for themselves. Just like you can format a table, you can also format a Pivot Table with all of these different stock templates that Excel provides. Personally, I like to keep it simple and go with one of the light options. If you don’t want any formatting at all you can choose the upper ______ option. Let’s just stick with something like this. And then other style options here. You can turn on or off the shading on your row or column headers and you can add banding to your rows or columns to help differentiate those. Personally, I don’t typically use banded rows or columns but totally subjective. Up to you.
left
For this lecture lets focus on the Design tab. Now the style options really speak for themselves. Just like you can format a table, you can also format a Pivot Table with all of these different stock templates that Excel provides. Personally, I like to keep it simple and go with one of the light options. If you don’t want any formatting at all you can choose the upper left option. Let’s just stick with something like this. And then other style options here. You can turn on or off the shading on your row or column headers and you can add banding to your rows or columns to help differentiate those. Personally, I don’t typically use banded rows or columns but totally ___________. Up to you.
subjective
What is the benefit of using the Show in Outline Form Report Layout rather than Show in Compact Form?
It makes formatting, filters and sorting options easier.
You can insert and delete rows in a Pivot Table for the purposes of readability using options under the ________ Rows button.
Blank
But Sum of Gross Revenue (Excel Pivot Tables) isn’t a very friendly name so let’s say I want to change that to Total Revenue instead. All you need to do is select that header label and go up in the formula bar and just type in the new name right there and it just changes the column header to whatever I choose. The one thing I will point out here is that if I wanted to name this Gross Revenue which is the name of the raw data column, I’m going to get this ______ that says the Pivot Table name already exists, so a little work around here. If I just add a space at the end, Excel will treat that as totally separate value and allow me to drop that in. So a little tip there. Sometimes you want to use more intuitive or more friendly names for a column. That how you do it.
Error
But Sum of Gross Revenue (Excel Pivot Tables) isn’t a very friendly name so let’s say I want to change that to Total Revenue instead. All you need to do is select that header label and go up in the formula bar and just type in the new name right there and it just changes the column header to whatever I choose. The one thing I will point out here is that if I wanted to name this Gross Revenue which is the name of the raw data column, I’m going to get this error that says the Pivot Table name already _________, so a little work around here. If I just add a space at the end, Excel will treat that as totally separate value and allow me to drop that in. So a little tip there. Sometimes you want to use more intuitive or more friendly names for a column. That how you do it.
exists
_______ are a really great way to visualise relative values like the blue bars shown here (attachment). You can use colour scales which is an awesome way to make heat maps. And if you want to get really advanced you can dive into formula based formatting rules which we won’t really cover here. If you are interested, you can check out that section in my Advanced formulas and functions course where we dive into that piece
Data bars
So, starting with ___________ cell rules. This is a great way to ________ based on the cell’s value itself. If we were looking at a text field here we would see these other options available so that text that contains certain strings or duplicate values. In this case we’re looking at a numerical field so our options are limited to greater than, less than or between or equal to. Let’s format any values greater than a certain number. We can just type that number right here so I’m going to choose 100 million and format it with green cell with dark green text. I can press ctrl Z to undo that. In fact let me press ctrl Y to put it back because the other way to remove formats is to go into Conditional formatting, manage rules at the bottom. You always have access to this mange rules choice (attachment). When you go in it will list out any formatting rule that’s applied to the select range.
highlight
So, starting with highlight cell rules. This is a great way to highlight based on the cell’s value itself. If we were looking at a text field here we would see these other options available so that text that contains certain strings or duplicate values. In this case we’re looking at a numerical field so our options are limited to greater than, less than or between or equal to. Let’s format any values greater than a certain number. We can just type that number right here so I’m going to choose 100 million and format it with green cell with dark green text (attachment). I can press ctrl Z to undo that. In fact let me press _________to put it back because the other way to remove formats is to go into Conditional formatting, manage rules at the bottom. You always have access to this mange rules choice. When you go in, it will list out any formatting rule that’s applied to the select range.
Ctrl Y
So, starting with highlight cell rules. This is a great way to highlight based on the cell’s value itself. If we were looking at a text field here we would see these other options available so that text that contains certain strings or duplicate values. In this case we’re looking at a numerical field so our options are limited to greater than, less than or between or equal to. Let’s format any values greater than a certain number. We can just type that number right here so I’m going to choose 100 million and format it with green cell with dark green text (attachment). I can press ctrl Z to undo that. In fact let me press ctrl Y to put it back because the other way to remove formats is to go into Conditional formatting, _________ at the bottom. You always have access to this ___________ choice (attachment). When you go in it will list out any formatting rule that’s applied to the select range.
Manage rules
________ is a really nice way to show relative volume. (Attachment) And one things that’s worth pointing out here is that even though I’ve applied this rule to the specific range C7 though C171. If I were to pull country out that conditional formatting rule will adapt to this new data range. So I don’t have to clear that rule and set it again. It will kind of morph as my pivot table changes which is really useful.
Data bars
Data bars is a really nice way to show relative volume. (Attachment) And one things that’s worth pointing out here is that even though I’ve applied this rule to the specific range C7 though C171. If I were to pull country out that conditional formatting rule will adapt to this new data range. So I don’t have to clear that rule and set it again. It will kind of ______ as my pivot table changes which is really useful.
morph
Let’s just do a standard red to green ____________ and this will give us a sense of which genres across the board are driving more revenue than others.
Colour scale
While we’re on the topic of Conditional Formatting, there’s one Pro Tip I have to share with you guys and its using a combination of data bars with an invisible text effect to create a really nice visualisation. So first things first lets go ahead and clear these flags we had set in the last lecture and what I want to do here is pull in a second instance of gross revenue so even though its already inserted into the Pivot, I can just drag that field again and create a second instance of revenue. The reason I’m going to do this is because I’d like to have one column that continues to show me the raw values themselves on their own and a second version of the column to treat purely as my visualisation column. So like I showed you in the last lecture I could select the values, go into conditional formatting and add ________here.
It works okay. My problem I that I don’t like the overlap between the numbers and the visuals themselves. I think it’s a little bit messy. It’s tough to read. So I really want to get the numbers out of this second instance especially as I’m going to keep them preserved in the first instance of my gross revenue column so a lot of people will tell you ‘okay yeah you can select the data, you can do a font size of one, something like that or maybe choose your font light and that will get you 90% of the way there usually. You still have some weirdness like this and it doesn’t always do what you expect it to do.
Data bars
While we’re on the topic of Conditional Formatting, there’s one Pro Tip I have to share with you guys and its using a combination of data bars with an invisible text effect to create a really nice visualisation. So first things first lets go ahead and clear these flags we had set in the last lecture and what I want to do here is pull in a second instance of gross revenue so even though its already inserted into the Pivot, I can just drag that field again and create a second instance of revenue. The reason I’m going to do this is because I’d like to have one column that continues to show me the raw values themselves on their own and a second version of the column to treat purely as my visualisation column. So like I showed you in the last lecture I could select the values, go into conditional formatting and add data bars here.
It works okay. My problem I that I don’t like the overlap between the numbers and the visuals themselves. I think it’s a little bit messy. It’s tough to read. So I really want to get he numbers out of this second instance especially as I’m going to keep them preserves in the first instance of my gross revenue column so a lot of people will tell you ‘okay yeah you can select the data, you can do a font size of one, something like that or maybe choose your font light and that will get you 90% of the way there usually. You still have some weirdness like this and it doesn’t always do what you _________ it to do.
expect
I’m going to undo those changes and show you a better approach. And that’s using a custom number format. I’m going to right click and go into number format and down here in the custom option I can type a specific Custom cell format here. and here’s the key. If you type 3 consecutive _____________. Press okay. That tells Excel to make the font invisible and that’s exactly what we’re trying to do here. So now we’ve got the numbers themselves and the associated data bar. We’ve got a really nice visualisation that tells the story very well.
So, there you go. Pro Tip using data bars combined with custom cell format to create a really cool visualisation effect.
semi colons
I’m going to undo those changes and show you a better approach. And that’s using a custom number format. I’m going to right click and go into number format and down here in the custom option I can type a specific Custom cell format here. and here’s the key. If you type 3 consecutive semi colons (attachment). Press okay. That tells Excel to make the font invisible and that’s exactly what we’re trying to do here. So now we’ve got the numbers themselves and the associated data bar. We’ve got a really nice ___________ that tells the story very well.
So, there you go. Pro Tip using data bars combined with custom cell format to create a really cool ____________ effect.
visualisation
