Excel Flashcards by Christopher Hays

What is the box called which enables other options to be viewed in Excel?

Dialogue Box Launcher.

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, displays the Format Cells dialog box?

CTRL+1

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a number, applies or removes bold formatting?

CTRL+2

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a number, applies or removes italic formatting?

CTRL+3

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a number, applies or removes underlining?

CTRL+4

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a number, applies or removes strike-through?

CTRL+5

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a number, hides rows?

CTRL+9

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a number, hides columns?

CTRL+0

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, displays the Go To dialog box?

CTRL+G

How well did you know this?

Not at all

Perfectly

What function command displays the Go To dialog box?

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, opens a new, blank workbook?

CTRL+N

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, displays the Open dialog box to open or find a file?

CTRL+O

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, selects all cells that contain comments?

CTRL+SHIFT+O

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, displays the Print tab in Microsoft Office Backstage view?

CTRL+P

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, opens the Format Cells dialog box with the Font tab selected?

CTRL+SHIFT+P

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, saves the active file with its current file name, location, and file format?

CTRL+S

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, displays the Create Table dialog box?

CTRL+T

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, applies or removes underlining?

CTRL+U

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, switches between expanding and collapsing of the formula bar?

CTRL+SHIFT+U

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, displays the Paste Special dialog box?

CTRL+ALT+V

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, closes the selected workbook window?

CTRL+W

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a letter, repeats the last command or action, if possible?

CTRL+Y

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a function key, displays the Excel Help task pane?

How well did you know this?

Not at all

Perfectly

What shortcut in Excel, using a function key, displays or hides the ribbon?

CTRL+F1

How well did you know this?

Not at all

Perfectly

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?

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?

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?

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?

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?

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?

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?

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?

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’s the advantage of adding gradient to shapes in Excel?

It makes them more three dimensional.

Are graphics in Excel vector or raster?

Vector

What Adobe programme is best known for producing vector graphics?

Illustrator

What is the advantage of vector graphics?

Unlike raster graphics, they are scalable.

Can you put text into any image in Excel?

Yes

How can you improve the drawings you produce in Excel?

By looking at alternatives available online through Google.

What Contextual Tools is there for Pictures?

Picture Tools – Format

Can you remove the background to see the stuff behind it when working with a picture in Excel?

Yes

Filters for Pictures are now called what in Excel?

Artistic Effects

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

What recommendation is there for the use of pictures in Excel and similar programmes?

Experiment not only in Excel but in Word and PowerPoint as well

What is SmartArt in Excel a replacement for?

Bullets

What is the disadvantage of bullets in Excel?

They can be text heavy and not very interesting, which is why we work with Smart Art

SmartArt is another way to _________ communicate your information in Excel.

Visually

Where is Smart Art found in Excel?

Under Illustrations under the Insert Tab.

Is it possible to put images inside of SmarArt in Excel?

Yes

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

When pressing the Text to Column button, what do you need to do first?

Make sure there is a blank column next to the highlighted column to transfer data into.

Give an example of changes made as a result of pressing the Text to Column button.

A name Bronyn Harry is in column A. As a result of pressing the Text to Column button, Bronym will be in column A while Harry will be in Column B.

What is the effect of unmarking the tab box and marking the space box in stage 2 of the text to column wizard?

2^nd names are moved a consistent space in the data.

If instead of having two elements such as Bronn Harry, you had 3 elements such as Ms Bronyn Harry, what would you ned to do?

Make sure there were 2 blank columns available not one.

What does Data Validation do in Excel?

Restricts what data can be entered into selected cells.

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.

What is the difference between Stop and Warning options on the Error Alert page on the Data Validation Dialogue Box.

Stops prevents the data from being entered while Warning provides a warning but allows the data to be entered

What advanced function is Data Validation particularly useful with?

VLookUp

What does the VLookUp function do in Excel?

Allows you to look up a variety of different data that is not in the current space where you’re doing the looking.

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.

What is a risk of selecting data for data validation which already exists rather than being entered from the data validation dialogue box?

If the data used for the selection is moved, the data options created by the Data Validation process disappear.

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.

MS Access is a ______________ database system

Relational

Excel can be considered a __________ but does not have the ability to be a relational \_\_\_\_\_\_\_\_\_\_\_.

database

Excel can be considered a database but does not have the ability to be a ___________ database.

relational

For example, if you have a small business which sells things, you are going to have a\_\_\_\_\_\_\_ for your products, a _______ for your customers and you might have a ________ of things sold 3 different \_\_\_\_\_\_\_\_\_s which are going to relate to each other somehow for instance through customer number as each individual thing will have a customer number. You might also have a product number and an invoice number. You have a relational _________ to have them all relate based on whatever criteria you want them to relate with.

database

For example, if you have a small business which sells things, you are going to have a Database for your products, a database for your customers and you might have a database of things sold 3 different databases which are going to relate to each other somehow for instance through customer number as each individual thing will have a customer number. You might also have a product number and an invoice number. You have a ________ database to have them all relate based on whatever criteria you want them to relate with.

relational

When might you use MS Access rather than Excel for analysis?

When you wish to see the relationships between different parts of your data and analyse it.

When might you use MS Access rather than Excel for queries?

When you wish to ask what happens when you wish to interrelate this with that.

If you produce a query in MS Access, what will you likely produce as well?

A report based on the results of the query.

What is the advantage of MS Access forms?

They enable data to be entered in a very clear format.

You’d use MS Access rather than Excel when you have more _________ information and you’re just wanting to put that stuff in and its more ___________ and it relates other components.

sophisticated

MS Access is essentially a bunch of _______ files. Which has tables which interrelate with each other.

Excel

Of course there is some overlap. You could use one independent of the other and get the same result, but its more when you’re going to get into more _________ input data.

sophisticated

How would you require a number a number 10 digits long with certain required values in Excel?

You would probably need to do a Macro or use Visual Basic (VB) for that.

What is an acronym for Visual Basic?

Are Macros and Visual Basic essentially the same thing in Excel?

Yes. Macros use Visual Basic, the programming language behind a lot of Microsoft products including Word and Excel.

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 shortcut for ‘Show Formula’ in Excel?

Ctrl + `

Where do you find the ‘``’ key on the keyboard?

Just below the escape button.

What is the difference between embedding a spreadsheet in PowerPoint and linking a spreadsheet to PowerPoint?

When a spreadsheet is embedded, the data in PowerPoint not automatically updated when data is updated in Excel.

What might be the advantage of embedding rather than linking Excel spreadsheets in PowerPoint?

It takes less time to update data and requires less resources to operate the PowerPoint presentation. The PowerPoint presentation can be sent without needing to have the Excel spreadsheet linked to it.

What is an advantage of linking a spreadsheet to a PowerPoint rather than linking it?

You can be assured the values in the PowerPoint presentation are accurate.

Rather than just having a chart in a PowerPoint presentation, what might be preferable?

Having the chart on one side of the slide with bullet points describing it on the other side.

What might be the advantage of having an Excel chart as a picture in PowerPoint rather than a linked or embedded chart?

Are able to make use of the graphics capabilities of MS Office.

What is another alternative, other than embedding or linking a Chart in Excel other than inserting the chart as a picture?

Inserting the Excel Spreadsheet as an object.

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 Pivot Charts?

A more visual way to summarize and make sense of the data.

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.

When do Pivot Tables start to get interesting?

When you add conditions that divide or break down the value field.

What are the conditions added to a Pivot Table called?

Row fields

If you’re unsure how to start a Pivot Table what should you do?

Create the value field first.

Where does Excel put Pivot Tables?

On a separate sheet to the left of the sheet you are on.

Why does Excel Put Pivot Tables on a separate page from the data?

The full name for a PivotTable is: "PivotTable Report." A report is based on data, and typically people like to see the reports separately from all of the data itself.

Is it possible to put Pivot Tables on the same sheet as the data?

Yes, but most of the time, PivotTables need a lot of space, so putting them on a new sheet is a good idea.

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

What button on the formulas tab do you push to evaluate the parts of a formula?

Evaluate formula

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

What type of reference is A1?

A single cell reference.

What type of reference is A1:C4?

An array reference.

What type of reference is A1,C4?

A non-contiguous reference

What does the F4 function key enable you to do with fixed, relative and mixed references?

Toggle between them.

What formula can be used in Excel to eliminate or change error messages (but not resolve the underlying problem?

=IFERROR

Give an example of an IFERROR message where the IFERROR formula rewrites the error message.

=IFERROR(A1/B1, “Invalid Formula”)

When would the formula =IFERROR(A1/B1, “Invalid Formula”) apply?

When B1 was zero.

What is the formula IFERROR(VLOOKUP(A1,D1:E4,2,0),”-“) doing and why?

Showing a consistent error message if the VLOOKUP is incorrect, as if left as it as it was the error messages in the VLOOKUP could be seem complex and not be easy to see in a large spreadsheet.

What is the quickest way to jump to the left, right, top or bottom edge of a contiguous array (ie last non blank cell)?

Ctrl + arrow.

What is the quickest way to extend a selection to the left, right, top or bottom edge of a contiguous array (ie last non blank cell)?

Ctrl+shift+arrow

How do you jump between tabs of a workbook?

Ctrl + PAGEUP / PAGEDOWN

How do you find the shortcuts using ALT in Excel?

Use the ALT button (you don’t need to keep holding it down)

What does Data Validation allow you to do in Excel?

Specify what types of values a cell can contain eg whole numbers, positive integers, values from a list etc).

What does the LIST form of data validation enable you to do?

Create a drop-down menu of options based on a source list that you specify.

Is Data Validation useful in Dashboards?

Yes

What are Volatile Functions?

Give an example of an absolute reference in Excel.

$B$10

What do range names do in Excel?

Assign a name to the cells, or ranges of cells that you want to stay the same when copied in formulas

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

Can cell names include spaces in Excel?

What should you do if your cell name involves more than one word in Excel?

Use an _ in between words

Give an example of a cell name in Excel involving more than one word, using an underline.

=B2/Sales\_Total

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.

Give an example of how the VLOOKUP function could be used in practice.

To look up the name of a customer, based upon their customer ID number

‘Range\_lookup’ is only used when in VLOOKUP in Excel?

When you want to search for an exact match.

If an approximate match is ok, in VLOOKUP in Excel what do you do with ‘Range\_lookup’?

Leave it blank

If you have to have an exact match, for example when you are using text, then what do you need to do under the range\_lookup in Excel?

Add FALSE (or 0)

What order must your cells be in the Table\_array?

Ascending order - For example 1, 2, 3 or 345, 689, 890 would both be acceptable.

What does the 2 in this VLOOKUP example signify?

That we want to use the values in the 2^nd 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..."

Which is the default trendline?

The Linear (regression) trendline.

What does the linear regression trendline give you?

An understanding of the general direction of the trend.

Other than the linear regression trendline, what other trendline is often used in Excel?

Moving average

What does a moving average trendline offer you?

A way of smoothing the line through your existing chart.

When is a Pivot Chart very useful?

You have a set of data that can be easily categorised and summarised by some of its variables.

A ____________ is a dynamic chart that you can use to summarise your data visually. \_\_\_\_\_\_\_\_\_s are very useful if you have a set of data that can be easily categorised and summarised by some of it's variables. For example, if you were looking at sales data that could be broken down by product range, sales person, region, etc, then you could look at this data from a number of different perspectives using a \_\_\_\_\_\_\_\_\_.

Pivot Chart

How do you create a Pivot Chart?

You select the data that you want to chart, and then choose "Pivot Chart" from the Insert tab on the ribbon.

Once you’ve selected the data that you want to chart and chosen "Pivot Chart, what do you need to do next?

Select each of the fields that you want to add to the report.

When Pivot Charts are populated with fields, where does data appear?

Both on the Pivot Chart and the Pivot Table that drives it.

Once you have created your \_\_\_\_\_\_\_\_\_\_, you can begin manipulating it's settings to show your data in a range of different ways. In particular you can _______ your _______ by changing the fields that are placed into the columns section, and you can filter your chart by adding fields to the filters section. In the video below, you will see how changing the columns and the fields of your \_\_\_\_\_\_\_\_\_\_\_\_can have a significant effect on the chart and allow you to view your data from a different perspective.

Pivot Chart

What is the first thing you should do when protecting a sheet?

Unlock any cells that you want the user to be able to make changes in.

Why are the cells unlocked when protecting a sheet likely to be plain values, rather than calculations?

You want the user to enter appropriate data.

What tab is the Protect Sheet under?

Review Tab

How can you prevent an Excel Workbook from being opened.

When Saving – choose more options and choose General Options.

What is the most straight forward way of creating Macros in Excel?

Using Macro Recorder

What new item in MS Office enables quick conversion into individual parts from a picture?

“Convert to Shape’’

Give an example of how Convert to Shape could be used in MS Office.

A chart could be imported and individual once converted individual bars could be coloured separately.

What is an example of a common yet simple task that you could record a macro for in Excel?

To apply a set of formats to a selection. For example, you could apply Bold, change the font and colour of a selection all in one process if you created a macro to do that.

What are the first steps for making a Macro in Excel?

Go to the View Tab on the ribbon, click on the Macros button and choose Record Macro.

Under what tab are Macros found in Excel?

The View Tab.

Once you’ve pressed record Macro, what is the next step in Excel?

Choose a name for your macro, and this name cannot include any spaces or special characters.

What cannot names of Macros include in Excel?

Any spaces or special characters.

If you choose to select a shortcut for your Macro, what should you be careful not to do?

Don’t use a key that you would already use for something else. For example ctrl+ c would not be a good choice as it is used to perform the copy function in Excel.

Give an example of what would not be a good choice for a Macro shortcut key in Excel.

Ctrl + C

Explain why Ctrl + C would not be a good choice for a Macro shortcut key in Excel.

It is already used for the Copy function.

In most cases where would you store Macros in Excel?

In ‘This Workbook’.

If the Macro is relatively simple, what should you do with the description in Excel?

Leave it blank

When assigning a shortcut to a Macro in Excel, what is it best to do to ensure that the shortcut is not already assigned to something else?

Hold down the Ctrl + Shift key.

What shortcut do we use to view Macros in Excel?

Alt + F8

Other than running Macros from the View tab or from a shortcut key, what other method can be used in Excel to run Macros?

Add the macro to a button or shape inside your worksheet, or to a button on the Quick Access toolbar.

Why might adding a button for a Macro in Excel be preferable to running a Macro from the View Tab or by pressing a shortcut key?

Using the Macros window that you can find on the View tab of the ribbon, is quite slow and may defeat the purpose of the macro. Using the shortcut key combination that you selected when you created your macro, is fine if you don't have took many macros, but it could be hard to remember all the short cuts if you have a number.

How do you create a Macro run from a shape?

Go to the "Insert" Tab on the ribbon, choose from the list of shapes, then draw your shape where you want it. Right click on your shape and choose "Assign Macro" you will then be prompted to choose from the list of macros that you have created. The next time someone clicks on the shape, it will run the macro.

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

What is the Text to Column tool useful for in Excel?

To split data up to make it easier to search through and work with.

Give an example of where the Text to Column tool would be useful.

Where you have recorded customer information and collected the customer name as one field, which means that both their first and last names will be included in the same column.

When data is delimited in Excel, what does this mean?

Separated by a space, tab, comma, or something similar.

When would data imported into Excel be a fixed width table?

When it is imported from WORD.

‘Goal Seek is the __________ of the Microsoft Excel data analysis tools.’

Simplest

How can Goal Seek be used in Excel?

If you have a formula that is based upon some variables and you have a goal in mind for the result of your formula, then you can use Goal Seek to determine the value required for one of the variables to achieve that goal, eg for breakeven analysis

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.

What does Scenario Manager allow you to do in Excel?

Model a number of different scenarios and easily switch between them within a single sheet.

How can the Scenario Manager tool be useful in Excel?

To forecast sales and expenses and to evaluate the impact of different approaches to doing business.

What tool in Excel is used for showing best and worst-case scenarios?

Scenario Manager

Where is the Scenario Manager tool found in Excel?

Under What If Analysis in the Data tab.

Using Scenario Manager what can be quickly changed in Excel?

The figures resulting from different assumptions for different scenarios.

What is the formula if you want to provide a discount if 3 or more items are bought otherwise nil discount and cell E4 contains the quantity?

=IF(E4\>=3,30%,0%)

Create a formula to calculate Customer discounts based on information provided in a table array named ‘Discount Rates’, where relevant information is in cell B4 and the totals to be looked up are in the 2^nd column of the table array.

=vlookup(B4,Discount\_Rates,2)

Create a nested formula where if E4 \>= 3 Discount\_3 applies, otherwise if E4 \>= 2 Discount\_2 applies, otherwise 0)

=IF(E4\>=3,Discount\_3,IF(E4\>=2,Discount\_2, Discount\_2,0))

How do you ensure cells are unlocked in Excel?

Format –protection – unlock. You can then lock the sheet and these cells won’t be affected.

What does protecting a Workbook in Excel do?

Protects the positioning of your sheet tabs at the bottom of the screen, however it leaves your content within those sheets available for change.

Other than protecting the location of tabs, what else does protecting a Workbook protect against?

Deleting, renaming or moving whole worksheets

Why are Pivot Tables considered one of the most important and powerful tools in Excel when it comes to data analysis?

Did Einstein actually say that ‘analysing data without a Pivot is like hammering a nail with a noodle’?

Given that Einstein didn’t actually say that ‘analysing data without a Pivot is like hammering a nail with a noodle why did the presenter say he said it?

Because it got the point across.

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

Think of a Pivot Table as a ______ which sits on top of a raw data set. You’re not actually replicating the data. You’re just creating a lens through which you can slice and dice and filter and organise and really explore your data in a very flexible and powerful way.

layer

What’s an acronym for the 5 Key Benefits of Pivot Tables?

PBFAF

What does the acronym PBFAF describing the 5 Key Benefits of Pivot Tables stand for?

does the acronym PBFAF describing the 5 Key Benefits of Pivot Tables stand for? * Powerful * Beautiful * Fast * Accurate * Flexible

How are Pivot Tables considered to be Powerful?

They enable you to uncover insights and answer key questions about your data.

How can Pivot Tables be made Beautiful?

By apply custom styles and conditional formatting rules to bring the Pivots to life.

Why are Pivot Tables considered to be fast?

Because they enable you to create custom views, filters, and calculated fields on the fly.

How are Pivot Tables considered to improve accuracy?

By automating calculations to minimize human error.

How are Pivot Tables considered to improve flexibility?

They enable you to manipulate table layouts and create dynamic views in seconds.

Before we get out hands dirty and start working with actual Pivots its incredibly important to understand that a __________ is only as strong as the data behind it.

Pivot Table

There is a right and a wrong way to structure your source ______ .A good _____ structure is rectangular with variables laid out as columns with clear header names and observations laid out as rows. If your \_\_\_\_\_\_\_’s flip flopped or transposed you really won’t be able to analyse it in any sort of logical way using a Pivot.

data

Should source data for a Pivot Table in Excel have variables laid out as columns, or should the variables be laid out as rows?

As columns

What is the disadvantage of having source data variables as rows rather than columns for Pivot Tables in Excel?

You won’t be able to analyse the data in any meaningful way in a Pivot Table.

Good source data is also free of any unnecessary formatting. Formats like number types, font styles, cell fills etc won’t pass through to a _________ anyway so it’s a best practice to keep things simple and clean on the back end.

Pivot Table

Should source data for a Pivot Table in Excel be formatted?

Does any formatting in source data in an Excel Pivot Table pass through to the Pivot Table?

Your source data should only include __________ and \_\_\_\_\_\_\_\_\_\_\_. Think of __________ as categorical fields and _________ as numerical fields. Don’t add any additional header or footer rows since Excel will automatically identify column headers based on the first row in your source data range.

Dimensions measures

Finally, your raw source data shouldn’t contain any _________ or calculated fields. Pivot Tables are designed to do those sorts of calculations. for you and to do those in a more accurate and flexible way, so adding any fields, aside from the raw values in your source data will only make things more complicated. No on the flip side a bad data structure might be transposed with variables as rows and observations as columns. It might be laid out in an unstructured or non-rectangular way or maybe it contains unnecessary formatting, calculated fields, misleading column names or extra header rows.

Subtotal rows

Finally, your raw source data shouldn’t contain any subtotal rows or \_\_\_\_\_\_\_\_\_\_. Pivot Tables are designed to do those sorts of calculations. for you and to do those in a more accurate and flexible way, so adding any fields, aside from the raw values in your source data will only make things more complicated. No on the flip side a bad data structure might be transposed with variables as rows and observations as columns. It might be laid out in an unstructured or non-rectangular way or maybe it contains unnecessary formatting, calculated fields, misleading column names or extra header rows.

Calculated fields

Header rows

The bottom line is that the only role your source data needs to play is to store the _________ for your pivot table to analyse. Your best is to keep things clean and simple and apply your formats and calculated fields using the Pivot Table itself.

source data

The bottom line is that the only role your source data needs to play is to store the raw data for your pivot table to analyse. Your best is to keep things _______ and simple and apply your formats and calculated fields using the Pivot Table itself.

clean

The bottom line is that the only role your source data needs to play is to store the raw data for your pivot table to analyse. Your best is to keep things clean and simple and apply your formats and \_\_\_\_\_\_\_\_\_\_using the Pivot Table itself.

Calculated fields

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?

When you’re working with Pivot Tables you’ll be taking one of two different approaches when it comes to your source data. That source data will either be ________ or dynamic.

static

When you’re working with Pivot Tables you’ll be taking one of two different approaches when it comes to your source data. That source data will either be static or \_\_\_\_\_\_\_\_\_\_\_.

dynamic

What I mean by that is that sometimes you’ll have just one set of data that will never ever change that you want to use a Pivot Table tool to analyse. When that’s the case, we’ll call that ________ source data.

static

What’s more common is having source data that you may need to edit or tweak or add data to, moving forward that you want to attach a Pivot Table to and in that case your source data is _______ and when you’re working with _______ data, it is important to understand how to refresh and update your pivot table as you make changes to that source data.

dynamic

So there are two tools in the analyse tab called the ________ and Change Data Source which allow you to do just that. Now the difference between the two is that the _______ will update your pivot table based on changes on changes made within the defined source data range or table so for instance if you’re using a range of data from A1 through B100 imagine that you’re drawing a box around that range.

Refresh

So there are two tools in the analyse tab called the Refresh and __________ which allow you to do just that. Now the difference between the two is that the Refresh will update your pivot table based on changes on changes made within the defined source data range or table so for instance if you’re using a range of data from A1 through B100 imagine that you’re drawing a box around that range.

Change Data Source

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

Any change that you make within that box will be captured by the Refresh 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 Refresh. 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 Refresh 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 __________ 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.

time series data

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

NOTE: You can double-click any specific value in a Pivot to generate a new tab showing the exact ___________ used to calculate it

source data

What function tells you the formula in a cell?

What is another name for a binary variable?

A (binary) flag

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?

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 ________ 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.

Back 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.

There are a few reasons why you may very well want to use different types of table layouts and in fact the ________ options that Excel provides in my opinion really aren’t the most useful formats at all.

default

There are a few reasons why you may very well want to use different types of table layouts and in fact the default options that Excel provides in my opinion really aren’t the most useful ________ at all.

formats

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.

When you click on Tabular form it essentially tries to recreate the look and form of a \_\_\_\_\_\_\_

table

You can insert and delete rows in a Pivot Table for the purposes of readability using options under the ________ Rows button.

Blank

One Pro Tip concerning Pivot Tables is using the \_\_\_\_\_\_\_\_\_\_\_to create new source data.

Tabular Layout

Maybe I just want a table of data that shows revenues by country by genre and that’s the deepest I want to take it. In that case I can _______ anything from the Pivot that I’m not interested in.

exclude

(of Pivot Tables) So what I can do is change to tabular mode and make a couple of adjustments to make this into a ______ I can use elsewhere. So the first thing I’m going to do is repeat all the item labels and then I’m going to turn off the item totals and turn off the subtotals. This gives me a nice clean tabular format which shows country, genre and revenue. Now all I need to do is grab this table and move it elsewhere and save that as whatever I need to. This has essentially allowed me to aggregate my data at a completely different level of granularity and produce a new raw data tabular set that I can use somewhere else.

Table

(of Pivot Tables) So what I can do is change to tabular mode and make a couple of adjustments to make this into a table I can use elsewhere. So the first thing I’m going to do is repeat all the _________ and then I’m going to turn off the item totals and turn off the subtotals. This gives me a nice clean tabular format which shows country, genre and revenue. Now all I need to do is grab this table and move it elsewhere and save that as whatever I need to. This has essentially allowed me to aggregate my data at a completely different level of granularity and produce a new raw data tabular set that I can use somewhere else.

item labels

(of Pivot Tables) So what I can do is change to tabular mode and make a couple of adjustments to make this into a table I can use elsewhere. So the first thing I’m going to do is repeat all the item labels and then I’m going to turn off the item totals and turn off the subtotals. This gives me a nice clean tabular format which shows country, genre and revenue. Now all I need to do is grab this table and move it elsewhere and save that as whatever I need to. This has essentially allowed me to aggregate my data at a completely different level of __________ and produce a new raw data tabular set that I can use somewhere else.

granularity

Formula

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 ______ header to whatever I choose.

Column

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

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 exists, so a little work around here. If I just add a __________ 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.

space

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 exists, so a little work around here. If I just add a space at the end, Excel will treat that as a\_\_\_\_\_\_\_\_\_\_\_\_\_\_ 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.

totally separate value

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 exists, so a little work around here. If I just add a space at the end, Excel will treat that as a 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 \_\_\_\_\_\_\_\_\_. That how you do it.

column

One of the coolest and most useful ways to customize your pivot table formatting is using \_\_\_\_\_\_\_\_\_\_\_\_\_\_s. So \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ting rules can be applied to Pivot Tables just like they can be applied to normal data ranges and you can access these \_\_\_\_\_\_\_\_\_ting rules right in the home menu of your screen.

Conditional format

\_\_\_\_\_\_\_ 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

Let’s use some \_\_\_\_\_\_\_\_\_\_\_s on our Gross Revenue Column. I’m going to select the entire column of data. Once I have that selected, in my home menu I can drill down into the \_\_\_\_\_\_\_\_\_\_\_\_\_\_ting rules. Now I’m going to kind of cruise through this and just really scratch the surface. I do dive into these much deeper in my data visualisation course so if you’re excited by this and want to learn a little bit more I recommend checking that out.

Conditional Format

column of data. Once I have that selected, in my home menu I can drill down into the conditional formatting rules. Now I’m going to kind of cruise through this and just really scratch the surface. I do dive into these much deeper in my _____________ course so if you’re excited by this and want to learn a little bit more I recommend checking that out.

Data visualisation

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 \_\_\_\_\_\_\_\_\_\_\_\_\_, 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.

Conditional Formatting

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 ________ with an invisible text effect to create a really nice visualisation.

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 \_\_\_\_\_\_\_\_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

What shortcut is there in Word for Page Break?

Ctrl + Return

How does pressing shlft – alt – d - differ from pressing ctrl-alt-d?

shlft – alt – d inserts the date whereas ctrl-alt-d inserts a footnote.

shlft – alt – d inserts __________ whereas ctrl-alt-d inserts \_\_\_\_\_\_\_\_\_\_.

A date, the footnote

To insert the date press __________ – alt – d

Shlft

To insert footnote press ________ – alt – d

ctrl

What shortcut creates a footnote in Word?

ctrl-alt-d

Excel Flashcards

(588 cards)