INTRODUCTION – Excel Fundamentals
This module offers a comprehensive introduction to key Excel elements and techniques, beginning with the creation and formatting of worksheets. It explores various features in Excel that enable efficient viewing and management of large datasets, ensuring that users can navigate and analyze extensive data smoothly.
Furthermore, the module provides in-depth guidance on performing accurate calculations using Excel’s powerful computational tools, helping users conduct precise data analysis and generate dependable results. By the end of this module, users will be equipped with a strong foundation in Excel, allowing them to confidently and effectively tackle complex data tasks.
Learning Objectives:
- Create and format worksheet content.
- Manage worksheet and workbook structures.
- Use filtering and sorting techniques to handle large datasets.
SELF-REVIEW: ADDING DATA TO A WORKSHEET
In the exercise Adding data to a worksheet, you added data to a worksheet named Sheet1. You first downloaded and opened the Excel file. You then verified and corrected the existing information within Sheet1. Next, you fo
rmatted the data, then added new information to the workbook. Finally, you managed the worksheet and renamed it Sample Figures. You also added a second worksheet called Exchange Rates.
Your final workbook should resemble the following screenshots:
Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.
1. In Sheet1, Microsoft Excel automatically aligned a numerical entry to the left. In this case, such an action indicates an issue with the content. How can you correct an error like this?
- Alter the Number format.
- Edit the content.
- Change the alignment. (CORRECT)
Correct! If a cell contains a mixture of text and numbers, then Excel considers the entry to be text. Because of the tilde character at the beginning of the numbers, Excel recognized the contents as a text entry. To ensure the entry is recognized correctly, remove the tilde character. It then automatically re-aligns to the right as Excel now considers it numeric.
2. In step 3, you formatted cells E23 to H25, which contained figures representing Japanese Yen amounts. What steps did you take to apply the Yen Currency format to this block of cells?
- Select the Currency button in the Number section of the Home tab. Choose More Accounting Formats. With Currency highlighted as a category, select the Symbol drop-down on the right. Select Japanese from the list of currencies that appear. (CORRECT)
- Select the Currency button in the Number section of the Home tab. Choose More Accounting Formats. With Custom highlighted as a category, select the Symbol drop-down on the right. Select Japanese from the list currency symbols that appear.
- Select the Currency button in the Number section of the Home tab. Choose More Accounting Formats. With Special highlighted as a category, select the Symbol drop-down on the right. Select Japanese from the list of currency symbols that appear.
Correct! In the currency details screen, you can select from a list of currency symbols that list the associated countries.
3. As one of the final steps in the exercise, you hid the Contacts worksheet using the Hide & Unhide command. Where on the Home tab is this command located?
- On the Format choice in the Cells group. (CORRECT)
- On the Format choice in the Styles group.
- On the Format choice in the Editing group.
Correct! The Format choice in the Cells group opens a drop-down menu that contains the Hide & Unhide command in a section called Visibility.
4. You attempt to rename an Excel file to Customer’s first/last name and address. However, Excel rejects this name. Can you identify the reason why Excel won’t accept this name?
- The filename contains prohibited punctuation marks. (CORRECT)
- The filename contains too many characters.
- The filename contains several spaces.
Correct! The punctuation marks / and . are not acceptable in a filename.
5. You want to quickly add the department name Customer Service in cells C1 to C20. You decide to use the Autofill feature. What type of mouse pointer shape do you need to Autofill?
- A narrow white hand shape.
- A narrow white cross shape.
- A narrow black cross shape. (CORRECT)
Correct! When the mouse pointer hovers over the square in the bottom right of the cursor it changes shape to a narrow black cross. Holding down the mouse button and dragging down will AutoFill the original entry into the cells underneath.
KNOWLEDGE CHECK: CREATING WORKBOOK CONTENT
1. You type some non-scientific numerical data directly into a Microsoft Excel worksheet. You then press Enter and 1.9E+09 appears in the cell. Why has this text appeared in the cell?
- The number format is incorrect.
- The column is too narrow. (CORRECT)
- The cell contains text and numbers.
That’s correct! Excel never obscures part of a number. If the column is not wide enough to display a number completely, then Excel displays either a series of cross hatch symbols or, where the original number is very long, an entry such as 1.0E+09. You will need to widen the column to see the actual numbers correctly.
2. Which number format option is both a format and an action button in Excel?
- The Comma format
- The Currency format
- The Percentage format (CORRECT)
That’s correct! The Percentage format button changes the appearance of a number by adding a percentage symbol at the end. However, it also alters the number by multiplying it by a hundred, so it should only be used in certain circumstances.
3. You are editing a worksheet in which column G is hidden and is not visible between columns F and H. Which set of steps can you take to unhide column G and check its contents?
- Select column G and click Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu.
- Select the column to the right of the hidden column. Select Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu.
- Highlight columns F and H. Select Format on the Home tab. Choose Hide & Unhide from the drop-down menu and then unhide columns from the sub-menu. (CORRECT)
That’s correct! By highlighting the columns on either side of the hidden one, Excel can identify what needs to be unhidden when you make the appropriate choices on the Format drop-down.
4. You type the heading Customer Name into a cell. Part of the heading is hidden because of the column width and the fact that there is other content in the cell directly to the right. You would like the full heading to be visible and the word Name to appear under the word Customer. How can you do this?
- Use the Wrap Text command in the Alignment group. (CORRECT)
- Type the word Name in the cell underneath the heading.
- Use the Center align command in the Alignment group on the Home tab.
That’s correct! This command will position the two words of the heading so that one is under the other. To accommodate this, the row will become deeper.
5. You need to sort or reorder a large block of information in a worksheet. Which tab do you need to select to access the Sort feature?
- The Data tab (CORRECT)
- The View tab
- The Contextual Sort tab
That’s correct! The Data Tab contains commands such as sorting that are useful when working with large blocks of information.
SELF-REVIEW: SORTING DATA
In the exercise Sorting data, you sorted data in a worksheet. You first downloaded the file Adventure Works Inventory.xlsx and customized the relevant worksheet. You then performed a series of alpha-numeric sorts within the worksheet.
Your final worksheet should resemble the screenshot:
Now it’s time to review your understanding of the tasks you completed by answering the questions that follow. Don’t forget that you can revisit the previous learning items to recap the process steps.
1. In the exercise, you sorted the Products worksheet data by the Product Name information stored in column C. Which of the following steps did you perform first, so that data would be sorted when you selected the Sort Ascending choice?
- Select Product Name from a drop-down list.
- Select column C by selecting the column initial.
- Select any cell in column C. (CORRECT)
Correct! The Sort Ascending option is a shortcut choice. Excel looks at the column that the cursor is in and uses the content of that column as the key to the sort. The cursor can be on any cell in the column.
2. You were asked to sort the data first by Supplier and then by Units in Stock. Which choice did you select on the Data ribbon to perform this?
- Sort (CORRECT)
- Sort A to Z
- Sort Z to A
Correct! The Sort choice on the Data ribbon allows you to customize what Excel is sorting by. It also allows you to create multiple levels of sort which run simultaneously.
3. True or False: You used Sort to sort the data using two columns with a first-level sort by Supplier and a second-level sort data by Units in stock. This meant that when the sort was complete, the Supplier entries were no longer in alphabetical order.
- True
- False (CORRECT)
That’s correct! In a multi-level sort, the data is sorted first by the first level sort. This sort would have grouped the supplier’s rows together. The second-level sort sorts these rows within the supplier groupings. The suppliers’ names would therefore still be in alphabetical order.
4. While editing the Adventure Works file, you turn the first visible row and column in the spreadsheet static. Which one of the following options do you use to complete this task?
- Freeze Panes (CORRECT)
- Freeze Top Row
- Freeze First Column
That’s correct! The Freeze Panes option freezes the screen in two directions. When you choose Freeze Panes from the drop-down menu, Excel freezes everything above and to the left of the cursor position.
5. True or False: You need to specify the key for a Sort when using the Sort Ascending choice by highlighting the relevant column.
- True
- False (CORRECT)
That’s correct! Excel uses the cursor position as the Sort key. It can identify the cursor’s column without any need for highlighting.
SELF-REVIEW: FILTERING DATA
1. You have five filters in place on the data in your worksheet. What is the quickest way to remove all filters and restore the full display of data?
- Use the Clear option on the Data ribbon. (CORRECT)
- Use the Undo button.
- Use the Clear filter from choice in each filter drop-down.
That’s correct! The Clear choice which is to the right of the Filter button on the Data ribbon is the quickest way to remove all filters in place.
2. You are working with a large block of data. You applied a filter but there are still a lot of rows visible. How can you determine how many results you have found?
- Check the bottom-left of the Excel screen. (CORRECT)
- Check the middle of the status bar.
- Check the bottom-right of the Excel Screen.
That’s correct! The bottom-left of the Excel screen will display a message showing how many records were found.
3. In the exercise, you selected the Product Name filter to filter and find all the entries relating to mountain bikes. What were your next steps?
- You selected Text filter and then contains on the sub-menu. (CORRECT)
- You selected Text filter and then begins on the sub-menu.
- You selected Text filter and then equals on the sub-menu.
That’s correct! The Contains option on the Text filter sub-menu allows you to look for a piece of text regardless of its position in the cell entry.
KNOWLEDGE CHECK: WORKING WITH BLOCKS OF DATA IN EXCEL
1. You want to move quickly to cell S1215 in your worksheet. To achieve this, you type the cell reference into a particular area of the screen and then press enter. What is the area of the screen called in which you type the cell reference?
- The Formula Bar
- The Name Box (CORRECT)
- The Title Bar
That’s correct! The Name Box is located just above the top left corner of the worksheet. If you type a cell reference into this box and press enter, the cursor moves directly to that position.
2. True or False: The UAE leads the Median Sales Distribution by Country visual with a value of 20.85, indicating its prominence as a key market for sales revenue and suggesting that most sales transactions in the UAE are of high value.
- The Filter arrow contains an arrow symbol.
- The Filter arrow drop-down symbol is in bold.
- The Filter arrow contains a funnel symbol. (CORRECT)
That’s correct! The button on the Data ribbon that is used to turn on the Filter feature contains a large funnel symbol. This symbol is used in miniature on a filter arrow to show that it has been used to filter the data.
3. Column A in your worksheet contains component names. All twenty-two names have been input as text except for one entry called 11ratchet, which begins with a number. If you were to sort this column in descending order, where would the entry 11ratchet appear?
- At the top of the column.
- At the bottom of the column. (CORRECT)
- In the middle of the column.
That’s correct! Excel will sort numbers separately to text in an alpha-numeric sort. The convention is that numbers are listed before text. Since this is a Sort Descending sort, which reverses the order, the one entry which contains a number will move to the bottom of the column.
4. Row one in your worksheet contains headings and it is currently visible at the top of the screen. The cursor is on cell B3. Which of the Freeze Panes options must you choose to ensure that the headings remain visible onscreen?
- Freeze Top Row (CORRECT)
- Freeze Panes
- Freeze First Column
That’s correct! The Freeze Top Row choice does not take the position of the cursor into account. The row visible at the top of the screen will remain static when you select this option.
5. True or False: You need to edit only those rows in a spreadsheet that have been shaded yellow. You can use the Filter by color filter option to hide rows that haven’t been formatted in this way.
- False
- True (CORRECT)
That’s correct! You can Filter by color in a spreadsheet. In this case, since a yellow color has been applied to the whole row, you can click on any of the Filter arrows and filter by the yellow color.
MODULE QUIZ: EXCEL FUNDAMENTALS
1. True or False: When you select the Filter button on the Data ribbon, Excel automatically filters the data based on choices that you have already made in the drop-down menus next to each heading.
- True
- False (CORRECT)
That’s correct! Selecting the Filter button on the Data ribbon does not apply a filter. It simply switches the feature on or off. If the filter button is selected to turn on the Filter feature, you would then need to make choices in the drop-down menu to apply a filter to the data.
2. You want to identify all rows in the data that have a figure exceeding 500 in a specific column. Which of the following options can you use to complete this task?
- Use Greater than on the Number Filter sub-menu. (CORRECT)
- Use Equals on the Number Filter sub-menu.
- Use Does not equal on the Number Filter sub-menu.
That’s correct! Microsoft Excel is automatically aware of the type of content in the column. This column contains numeric entries and, as a result the drop-down menu automatically contains a Number Filters option which gives access to additional choices, one of which is “greater than”. This filter will look for any number greater than 500.
3. You are applying a multi-level Sort to a block of data. Row one of the data contains headings. How do you ensure that these titles are not sorted with the rest of the data?
- Tick the box that says, My data has headers. (CORRECT)
- Select the whole data range.
- Click the first row of the data range.
That’s correct! The Sort dialog contains a tick box in the top right-hand corner labeled My data has headers. When a tick mark is present in this box, Excel assumes that the top row of the block of data contains headings and does not include that row in the sort.
4. You want to assign a specific name to a cell. You have used this same name previously for a different cell. Where can you view the sheet and cell reference that indicates where the name has been assigned?
- Use the Name Manager in the Formulas ribbon (CORRECT)
- Use the Search feature at the top of the Excel window and type the name.
- Use the Name box on the top left of the worksheet.
That’s correct! The Name Manager in the Formulas ribbon provides a full list of all Assigned Names in use in the workbook and shows the sheet and cell references for each one.
5. You need to highlight all the data in your worksheet. The cursor is on cell A1. Which key combination on a Windows PC can you use to quickly highlight all information in the block?
- Control + Shift + End (CORRECT)
- Control + End
- Control + Alt + End
That’s correct! The control + end combination by itself will move the cursor to the bottom right-hand corner of the used area of the worksheet. However, holding down Shift at the same time ensures that Excel highlights all cells as it moves the cursor to the bottom right corner.
6. Which of the following tasks can you perform using the Search box on the Title bar? Select all that apply.
- Search for text or number content across a whole workbook. (CORRECT)
- Search for Names assigned to cells.
- Search for text or number content in a worksheet. (CORRECT)
Exactly! When you select “Find,” you can access additional options that give you more control over the search, including a dropdown that allows you to extend the search to the entire workbook.
Correct! By selecting the search box and choosing “Find,” you’ll open the Find dialog.
7. You are working with a block of information, and you realize that you need to add a new column to the left of the column that your cursor is in. Which of the following series of steps can you perform to correctly insert the column? Select all that apply.
- Select the Insert Ribbon, select on Insert, and then select Insert Sheet Columns.
- Click the right mouse button, select Insert, and then select Entire column. (CORRECT)
- Select the Home ribbon, select Insert, and then select Insert Sheet Columns. (CORRECT)
Exactly! The shortcut menu offers options for inserting cells, rows, and columns.
Correct! The “Insert” dropdown on the Home ribbon is helpful in multiple scenarios, as it allows you to insert cells, rows, columns, and even a new worksheet.
8. You enter geographic data into a worksheet. This prompts Excel’s Convert to Geography option to appear. When selected, the option displays a card icon. What type of information does Excel display when you select this card?
- Information about the location in a Bing search result card. (CORRECT)
- A map graphic with the listed locations marked.
- A summary of how many times each geographic location has been mentioned in the worksheet.
9. You manually adjust the width of column A and then type 958697100T into cell A2. However, the entry still displays across cell B2, which is currently empty. What is the visual effect on the entry in cell A2 when you enter data into cell B2?
- The content will only be partially displayed and some of the entry will be hidden by the content in B2. (CORRECT)
- The content will be replaced by crosshatch symbols as the column is too narrow to display it fully.
- The content will automatically “wrap around” so that some of the heading will be brought down to a new line.
That’s correct! The entry is primarily numeric but there is a single alphabet character at the end. If an entry is a mixture of letters and numbers, Excel will automatically consider it to be text. If the column is too narrow to display a text entry fully, and the cell to the right contains content, Excel will hide part of the entry.
10. A colleague in Adventure Works has asked you to quality-check information in a workbook. How would you know if the data in the workbook has been filtered? Select all that apply.
- There are gaps in the row numbering on the left of the screen. (CORRECT)
- There are funnel symbols on some of the Filter arrows. (CORRECT)
- There are Filter arrows beside the headings on the first row of the screen.
- Some Filter arrows have funnel symbols and are in a different color.
That’s correct! When filters are applied, the rows that do not match the filter criteria are hidden in the display.
That’s correct! When a Filter drop-down menu is used to filter the data, the Filter arrow will display a small funnel symbol.
11. You are working on an inventory spreadsheet that contains a list of suppliers and products. You need to know how many touring bikes are supplied by a company which is called “Cycles”. However, several other suppliers in the spreadsheet also have the word cycles in their name. Which of the following options should you use to complete this task?
- Use Equals on the Text Filter sub-menu. (CORRECT)
- Use Begins with on the Text Filter sub-menu.
- Use Ends with on the Text Filter sub-menu.
That’s correct! The drop-down menu automatically contains a Text Filters option because this column contains text entries. This gives access to additional choices, including Equals. This filter will match the company name on the cell entry.
12. In larger worksheets, it can be useful to hide columns that are not required. This allows you to scroll through the content more quickly. What is the key visual indicator onscreen that there are columns hidden in a worksheet?
- There is a gap in the initial letters at the top of the screen. (CORRECT)
- Some column initials are shaded in a different background color.
- There is a darker vertical line between two of the column initials.
That’s correct.
13. You are entering repeat content in your worksheet. After entering the content several times Excel offers the content as an automatic suggestion. What is this feature called?
- Autofit
- Autocomplete (CORRECT)
- Autofill
That’s correct.
14. You enter numbers into a column. Your entries are displaying a different number of decimal places from those you intended. Where can you locate the increase and decrease decimal buttons to control the number of decimal places displayed?
- In the Number group on the Home ribbon. (CORRECT)
- In the Alignment group on the Home ribbon.
- In the Editing group on the Home ribbon.
That’s correct! The increase and decrease decimal buttons in the Number group on the Home ribbon allow you to customize the number of decimal places displayed.
15. True or False: You need to sort a staff spreadsheet alphabetically by department, and then sort each department by join date with the most recent appearing first. You can complete this task by first using the Sort A-Z shortcut on the Department Name column and then the Sort Newest to Oldest shortcut on the Join Date column.
- True
- False (CORRECT)
That’s correct! Using the shortcut Sort buttons would not work here. The second option, the Newest to Oldest sort, would cancel the first sort. In this situation, you should use the Sort dialog box to perform a multi-level sort.
16. You use the New Window command to view two copies of your worksheet. Which ribbon do you select to access the New Window command?
- The Review ribbon.
- The Home ribbon.
- The View ribbon. (CORRECT)
That’s correct! The New Window command opens another view of the file in another window. This is not a separate copy. It is a different view of the same file.
17. What appears on the Formula bar when you enter a calculation in a worksheet cell?
- The result.
- The calculation. (CORRECT)
- The calculation and the result.
That’s correct! When you create a calculation, the result will appear in the cell in the worksheet. The formula (the “engine” that is driving the result) will appear in the Formula Bar. The Formula Bar always shows the real contents of the cell.
18. You type a supplier name into cell C2 in your spreadsheet. You want to use one of Excel’s quick entry features to copy the same supplier name into all cells down to C100. What shape should the mouse pointer be?
- A white cross.
- An arrow.
- A narrow black cross. (CORRECT)
That’s correct.
19. As you type a list of city names into a column in Excel, a floating dialog saying Convert to Geography appears. You accidentally select this feature and reveal a card icon. How can you reverse this choice?
- Select Undo on the left-hand side of the Title Bar.
- Select the floating dialog which should now read Remove Geography.
- Right-click on the card and immediately choose Convert to Text from the options presented. (CORRECT)
That’s correct.
20. You want to identify all rows that contain a specific word, but the word might appear anywhere in a cell entry. Which of the following options can you use to complete this task?
- Use Begins with on the Text Filter sub-menu.
- Use Ends with on the Text Filter sub-menu.
- Use Contains on the Text Filter sub-menu. (CORRECT)
That’s correct! This column contains text entries and, as a result the drop-down menu automatically contains a Text Filters option which gives access to additional choices, including Contains. This filter will carry out a partial match on the cell entry.
21. Cell A2 in your worksheet contains the number 50. You want this number to be recognized as a percentage. If you apply the Percentage format to this cell what number would now be showing in A2?
- 500%
- 50%
- 5000% (CORRECT)
That’s correct! The Percentage format button applies a format, but it also multiplies by 100. The Percentage format should only be used to format the results of specific calculations.
22. You need to present a worksheet to your colleague. However, columns D and E contain confidential data that they cannot be privy to. What option on the Home ribbon can you use to hide these columns during the presentation?
- Delete drop-down.
- Insert drop-down.
- Format drop-down. (CORRECT)
That’s correct! The options to hide rows and columns are formatting choices, so they are contained in the Format drop menu.
23. You have frozen the top row (Row 1) of the screen to make your spreadsheet easier to read. What cell will your cursor move to when you move to the top of the worksheet using the Control + Home shortcut?
- A3
- A1
- A2 (CORRECT)
That’s correct! The keyboard combination Control + Home would normally move the cursor to the first cell in the worksheet, which is A1. However, if the first row contains headings and has been frozen to remain visible onscreen, then the cursor will move to cell A2.
24. You need to change the position of sheets in a workbook. Which set of steps can you take to complete this task? Select all that apply.
- Point at the sheet tab. Hold down the mouse button. Drag the sheet to a new location. (CORRECT)
- Point at the sheet tab. Hold down the Ctrl key and then the mouse button. Drag the sheet to a new location.
- Point at the sheet tab. Click the right mouse button. Choose Move or Copy from the shortcut menu. (CORRECT)
That’s correct! This is the quickest way to change the position of a worksheet in the workbook.
That’s correct! Selecting the Move or Copy choice from the right-click menu brings up a list of worksheets so that you can indicate the new position of the worksheet you are in.
25. You are having difficulty locating numbers in a large worksheet. You decide to name the cells that contain these figures so that you can locate them quickly. Which of the following options are valid names you could use? Select all that apply.
- MonthlyProfit2 (CORRECT)
- Monthly_Profit (CORRECT)
- Monthly Profit
That’s correct! Excel allows for a mixture of letters and numbers when you are naming a cell.
That’s correct! Excel allows for an underscore character between words when you are naming a cell.
26. You want to sort information alphabetically by supplier and then by product in an inventory file. You access the Sort dialog and choose the supplier as the Sort by option. Which of the following series of steps do you need to carry out next to complete this sort?
- Select Add and choose product from the then by column drop-down. (CORRECT)
- Select Add and choose Supplier from the Then by column drop-down.
- Select Add and choose Product from the then by order column.
That’s correct! By selecting the Add choice in the Sort dialog, it is possible to specify criteria for multiple levels of sort. By using this option, you can first sort the data by Supplier and then by Product.
27. You need to change the position of sheets in a workbook. Which set of steps can you take to complete this task? Select all that apply.
- Point at the sheet tab. Click the right mouse button. Choose Move or Copy from the shortcut menu. (CORRECT)
- Point at the sheet tab. Hold down the mouse button. Drag the sheet to a new location. (CORRECT)
- Point at the sheet tab. Hold down the Ctrl key and then the mouse button. Drag the sheet to a new location.
Exactly! Selecting the “Move or Copy” option from the right-click menu displays a list of worksheets, allowing you to specify the new position of the worksheet you’re working on.
Correct! This is the fastest method to change the position of a worksheet within the workbook.
CONCLUSION – Excel Fundamentals
In conclusion, this module provides users with a strong foundation in essential Excel elements and techniques. It covers everything from creating and formatting worksheets to utilizing features for managing large datasets and performing precise calculations. By mastering these core Excel skills, users will be equipped to navigate and analyze data efficiently. This knowledge will enable them to approach complex data tasks with confidence and accuracy, ultimately boosting their productivity and effectiveness in data-related projects.