1.1 Overview of Microsoft Excel

Learning Objectives

  1. Examine the value of using Excel to make decisions.
  2. Learn how to start Excel.
  3. Become familiar with the Excel workbook.
  4. Understand how to navigate worksheets.
  5. Examine the Excel Ribbon.
  6. Examine the right-click menu options.
  7. Learn how to save workbooks.
  8. Examine the Status Bar.
  9. Become familiar with the features in the Excel Help window.

Microsoft® Office contains a variety of tools that help people accomplish many personal and professional objectives. Microsoft Excel is perhaps the most versatile and widely used of all the Office applications. No matter which career path you choose, you will likely need to use Excel to accomplish your professional objectives, some of which may occur daily. This chapter provides an overview of the Excel application along with an orientation for accessing the commands and features of an Excel workbook.

Making Decisions with Excel

Taking a very simple view, Excel is a tool that allows you to enter quantitative data into an electronic spreadsheet to apply one or many mathematical computations. These computations ultimately convert that quantitative data into information. The information produced in Excel can be used to make decisions in both professional and personal contexts. For example, employees can use Excel to determine how much inventory to buy for a clothing retailer, how much medication to administer to a patient, or how much money to spend to stay within a budget. With respect to personal decisions, you can use Excel to determine how much money you can spend on a house, how much you can spend on car lease payments, or how much you need to save to reach your retirement goals. We will demonstrate how you can use Excel to make these decisions and many more throughout this text.

Figure 1.1 shows a completed Excel worksheet that will be constructed in this chapter. The information shown in this worksheet contains sales data for a hypothetical merchandise retail company. The worksheet data can help a retailer analyze the business and determine the number of salespeople needed for each month for example.

Worksheet with centered title, entries for columns titled Month, Unit Sales, Average Price, and Sales Dollars. Total sales calculated in bottom row.
Figure 1.1 Example of an Excel Worksheet

 

Starting Excel

  1. Locate Excel on your computer.
  2. Click Microsoft Excel to launch the Excel application where you are presented with workbook options to help get you started.
  3. Click the first option; “Blank Workbook”.

Excel for Windows vs Excel for Mac

The Excel for Windows and Excel for Mac software versions are very similar. Most of the features, tools and commands are available in both versions. There are, however, some differences with the Excel interface. There are also a few features that are not available in the Excel for Mac version. The screenshots and step-by-step instructions in this textbook are specific to Excel for Windows. We have attempted to provide alternate screenshots and instructions for the Mac version when the differences are significant. When you see this icon Excel for Mac icon, it means we are providing information specific to Mac users.

 

The Excel Workbook

A workbook is an Excel file that contains one or more worksheets (referred to as spreadsheets). Excel will assign a file name to the workbook, such as Book1, Book2, Book3, and so on, depending on how many new workbooks are opened. Figure 1.2 shows a blank workbook after starting Excel. Take some time to familiarize yourself with this screen. Your screen may be slightly different based on the version you’re using.

Quick access toolbar with commands Excel Help, Zoom slider, view options, and workbook tabs.
Figure 1.2 Blank Workbook
Quick access toolbar with commands Excel Help, Zoom slider, view options, and workbook tabs.
Figure 1.2a Blank Workbook (right-side)

 

 

 

 

 

 

 

 

 

 

 

 

 

Your workbook should already be maximized (or shown at full size) once Excel is started, as shown in Figure 1.2. However, if your screen looks like Figure 1.3 after starting Excel, you should click the Maximize button, as shown in the figure.

Maximize icon, workbook title in top left-hand corner not top center as in Figure 1.2.
Figure 1.3 Restored Worksheet

Navigating Worksheets

Data are entered and managed in an Excel worksheet. The worksheet contains several rectangles called cells for entering numeric and non-numeric data. Each cell in an Excel worksheet contains an address, which is defined by a column letter followed by a row number. For example, the cell that is currently activated in Figure 1.3 is A1. This would be referred to as cell location A1 or cell reference A1. The following steps explain how you can navigate in an Excel worksheet:

  1. Place your mouse pointer over cell D5 and click.
  2. Check to make sure column letter D and row number 5 are highlighted, as shown in Figure 1.4.
Bold outline D5 cell activated.
Figure 1.4 Activating a Cell Location

 

  1. Move the mouse pointer to cell A1.
  2. Click and hold the left mouse button and drag the mouse pointer back to cell D5.
  3. Release the left mouse button. You should see several cells highlighted, as shown in Figure 1.5.

This is referred to as a cell range and is documented as follows: A1:D5. Any two cell locations separated by a colon are known as a cell range. The first cell is the top left corner of the range, and the second cell is the lower right corner of the range.

 

Cell range A1:D5 is highlighted. Multiple worksheet tabs featured at bottom. Shift F11 adds new worksheet to workbook.
Figure 1.5 Highlighting a Range of Cells

 

  1. At the bottom of the screen, you’ll see a sheet tab indicated by “Sheet1″.  Clicking on the + adds additional worksheets. This is how you open or add a worksheets within a workbook.  To see how this works, click on the + to add another worksheet so that you now have two sheets
  2. Click the Sheet1 worksheet tab at the bottom of the worksheet to return to the worksheet shown in Figure 1.5.

Keyboard Shortcuts

Basic Worksheet Navigation

  • Use the arrow keys on your keyboard to activate cells on the worksheet.
  • Hold the SHIFT key and press the arrow keys on your keyboard to highlight a range of cells in a worksheet.
  • Hold the CTRL key while pressing the PAGE DOWN or PAGE UP keys to open other worksheets in a workbook.
  • Excel for Mac icon Mac Users: Hold down the Fn and Command keys and press the left or right arrow keys

The Excel Ribbon

Excel’s features and commands are found in the Ribbon, which is the upper area of the Excel screen that contains several tabs running across the top. Each tab provides access to a different set of Excel commands. Figure 1.6 shows the commands available in the Home tab of the Ribbon. Table 1.1 “Command Overview for Each Tab of the Ribbon” provides an overview of the commands that are found in each tab of the Ribbon.

Home tab of Ribbon with font, alignment, and formatting options.
Figure 1.6 Home Tab of Ribbon

 

Excel for Mac icon The Excel for Mac ribbon, as shown in Figure 1.6a below, has two primary differences:

    • The older dropdown menu structure is still available with Excel for Mac.
    • The specific commands and tools within each tab are slightly different between the two Excel Ribbons. Some of the commands found within the Excel for Windows Ribbon tabs are located within the dropdown menu structure in the Excel for Mac version. So, if you can’t find the tool on the Excel for Mac Ribbon, then try to find the tool by looking through the dropdown menu instead.

 

Home tab of Ribbon for Mac with font, alignment, and formatting options.
Figure 1.6a Home tab of Excel for Mac Ribbon with dropdown menu structure

 

Group Title Names on the Ribbon

If you look closely at the Excel Ribbon (See Figure 1.6 above), you will see that the Ribbon is separated in groups of tool buttons, and each group has a title name. On Home tab, the group title names are “Clipboard”, “Font”, “Alignment”, “Number”, “Styles”. “Cells”, “Editing”, etc. The tool buttons within each group are all related to the group title.

Excel for Mac icon Mac Users Only: The default “View” for the Excel for Mac ribbon does not display these “group title names”. Notice in Figure 1.6a above, there are no group title names. It is a good idea to change this “view” so you can see the group title names. Here are the steps:

  1. Click the “Excel” menu option at top left above the Ribbon
  2. Choose “Preferences”
  3. Click the “View” button
  4. Scroll down and check the box for “Group Titles” 
  5. Close the “View” dialog box. The group title names should now display as shown in Figure 6.1 (not Figure 6.1a) above

 

Table 1.1 Command Overview for Each Tab of the Ribbon

Tab Name Description of Commands
File Also known as the Backstage view of the Excel workbook. Contains all commands for opening, closing, saving, and creating new Excel workbooks. Includes print commands, document properties, e-mailing options, and help features. The default settings and options are also found in this tab.
Home Contains the most frequently used Excel commands. Formatting commands are found in this tab along with commands for cutting, copying, pasting, and for inserting and deleting rows and columns.
Insert Used to insert objects such as charts, pictures, shapes, PivotTables, Internet links, symbols, or text boxes.
Page Layout Contains commands used to prepare a worksheet for printing. Also includes commands used to show and print the gridlines on a worksheet.
Formulas Includes commands for adding mathematical functions to a worksheet. Also contains tools for auditing mathematical formulas.
Data Used when working with external data sources such as Microsoft® Access®, text files, or the Internet. Also contains sorting commands and access to scenario tools.
Review Includes Spelling and Track Changes features. Also contains protection features to password protect worksheets or workbooks.
View Used to adjust the visual appearance of a workbook. Common commands include the Zoom and Page Layout view.
Help This tab provides access to help and support features such as contacting Microsoft support, sending feedback, suggesting a new feature, and community discussion groups. Excel for Mac iconThis tab is not available with Excel for Mac.
Draw Provides drawing options for using a digital pen, mouse or finger depending on the type of device (laptop with touch screen, tablet, computer, etc). This tab is not visible by default. See below on how to customize the Ribbon to add or remove tabs.
Developer Provides access to some advanced features such as macros, form controls, and XML commands. This tab is not visible by default. See below on how to customize the Ribbon to add or remove tabs.
 

The Ribbon shown in Figure 1.6 and Figure 1.6a (above) is full, or maximized. The benefit of having a full Ribbon is that the commands are always visible while you are developing a worksheet. However, depending on the screen dimensions of your computer, you may find that the Ribbon takes up too much vertical space on your worksheet. If this is the case, you can minimize the Ribbon by clicking the button shown in Figure 1.6. When minimized, the Ribbon will show only the tabs and not the command buttons. When you click on a tab, the command buttons will appear until you select a command or click anywhere on your worksheet.

Excel for Mac icon To hide the Ribbon with Excel for Mac you can use the keyboard shortcut:

Hold down the “Command and Option” keys and tap the “R” key

The same keyboard shortcut will unhide the Ribbon as well.

How to Customize the Excel Ribbon

Here are the steps to add additional tabs to the Excel Ribbon

  1. Click the File tab and choose Options
  2. Click on “Customize Ribbon” at the left side of the Options screen
  3. Click the checkbox next to the Tab name that you want to add (See Figure 1.7 below)
Figure 1.7 Customize the Ribbon Dialog Box

Keyboard Shortcuts

Minimizing or Maximizing the Ribbon

  • Hold down the CTRL key and press the F1 key.
  • Hold down the CTRL key and press the F1 key again to maximize the Ribbon.
  • Excel for Mac icon Mac Users: Hold down the Command and Option keys and press R

Quick Access Toolbar and Right-Click Menu

The Quick Access Toolbar is found at the upper left side of the Excel screen above the Ribbon, as shown in Figure 1.7. This area provides access to the most frequently used commands, such as Save and Undo. You also can customize the Quick Access Toolbar by adding commands that you use on a regular basis. By placing these commands in the Quick Access Toolbar, you do not have to navigate through the Ribbon to find them. To customize the Quick Access Toolbar, click the down arrow as shown in Figure 1.8. This will open a menu of commands that you can add to the Quick Access Toolbar. If you do not see the command you are looking for on the list, select the More Commands option.

Customize Quick Access Toolbar with frequently used commands. Open Quick Access Toolbar via keyboard: Alt, F, T, arrow down. Via Keyboard to open Quick Access Toolbar: Alt, F, T, arrow down.
Figure 1.8 Customizing the Quick Access Toolbar

In addition to the Ribbon and Quick Access Toolbar, you can also access many commands by right clicking anywhere on the worksheet. Figure 1.9 shows an example of the commands available in the right-click menu.

Excel for Mac icon There is no “Right-click” option for Excel for Mac. To access the same commands with Excel for Mac, hold down the Control key and click the mouse button.

Right click commands: font formatting, cut, copy, paste, insert, delete, clear, filter, sort, insert comment, cell formatting, define name and hyperlink.
Figure 1.9 Right-Click Menu

 

The File Tab

The File tab is also known as the Backstage view of the workbook. It contains a variety of features and commands related to the workbook that is currently open, new workbooks, or workbooks stored in other locations on your computer or network. Figure 1.10 shows the options available in the File tab or Backstage view. To leave the Backstage view and return to the worksheet, click the arrow in the upper left-hand corner as shown below.

File Tab/Backstage View displays workbook name; Esc Key: return to workbook Save As, Excel default settings. Includes Protect, Inspect, Manage Workbook Info.
Figure 1.10 File Tab or Backstage View of a Workbook

Included in the File tab are the default settings for the Excel application that can be accessed and modified by clicking the Options button. Figure 1.11 shows the Excel Options window, which gives you access to settings such as the default font style, font size, and the number of worksheets that appear in new workbooks.

Options window General tab: Font size, number of worksheets in a workbook can be changed. Other Excel options are Formulas, Proofing, Save, Language, Advanced, Customize Ribbon, Quick Access Toolbar, Add-Ins, Trust Center.
Figure 1.11 Excel Options Window

Excel for Mac icon To access these same options in Excel for Mac, you must click the “Excel” menu option and choose “Preferences” (see Figure 1.12 below)

Excel for Mac icon
Excel for Mac Preferences
Figure 1.12 The Excel for Mac “Excel” menu option

Saving Workbooks (Save As)

Once you create a new workbook, you will need to change the file name and choose a location on your computer or network to save that file. It is important to remember where you save this workbook on your computer or network as you will be using this file in the Section 1.2 “Entering, Editing, and Managing Data” to construct the workbook shown in Figure 1.1. The process of saving can be different with different versions of Excel.  Please be sure you follow the steps for the version of Excel you are using. The following steps explain how to save a new workbook and assign it a file name.

Saving Workbooks in Excel 365

  1. If you have not done so already, open a blank workbook in Excel.
  2. Click the File tab and then the Save As button in the left side of the Backstage view window. This will open the Save As dialog box.
  3. Determine a location for saving on your computer by clicking Browse on the left side to open the Save As dialog box.
  4. Click in the File Name box near the bottom of the Save As dialog box. Type the new file name: CH1 Merchandise City Sales Data
  5. Review the settings in the screen for correctness and click the Save button.

 

Save As dialog box for Excel 365 featuring saving and naming a workbook.
Figure 1.13 Save As Dialog entries for Excel 365

 

Keyboard Shortcuts

Save As

  • Press the F12 key and use the tab and arrow keys to navigate around the Save As dialog box. Use the ENTER key to make a selection.
  • Or press the ALT key on your keyboard. You will see letters and numbers, called Key Tips, appear on the Ribbon. Press the F key on your keyboard for the File tab and then the A key. This will open the Save As dialog box.
  • Excel for Mac icon The Mac shortcut is: Hold down the Command and Shift keys and press S

Skill Refresher

Saving Workbooks (Save As)

  1. Click the File tab on the Ribbon.
  2. Click the Save As option.
  3. Click on Browse to select a location on your PC to save.
  4. Click in the File name box and type a new file name if needed.
  5. Click the down arrow next to the “Save as type” box and select the appropriate file type if needed.  Excel will default to the file type of .xlsx
  6. Click the Save button.

The Status Bar

The Status Bar is located below the worksheet tabs on the Excel screen (see Figure 1.13). It displays a variety of information, such as the status of certain keys on your keyboard (e.g., CAPS LOCK), the available views for a workbook, the magnification of the screen, and mathematical functions that can be performed when data are highlighted on a worksheet. You can customize the Status Bar as follows:

  1. Place the mouse pointer over any area of the Status Bar and right click to display the “Customize Status Bar” list of options (see Figure 1.14).
    Excel for Mac icon Mac Users: use “Control-click” on the Status Bar to display the “Customize Status Bar” options.
  2. Select the Caps Lock option from the menu (see Figure 1.14).
  3. Press the CAPS LOCK key on your keyboard. You will see the Caps Lock indicator on the lower right side of the Status Bar.
  4. Press the CAPS LOCK on your keyboard again. The indicator on the Status Bar goes away.
Customize Status Bar drop-down menu with options for indicators including Caps Lock.
Figure 1.14 Customizing the Status Bar

Excel Help

The Help feature provides extensive information about the Excel application. Although some of this information may be stored on your computer, the Help window will automatically connect to the Internet, if you have a live connection, to provide you with resources that can answer most of your questions. You can open the Excel Help window by clicking the question mark in the upper right area of the screen or ribbon. With newer versions of Excel, use the query box to enter your question and select from helpful option links or select the question mark from the dropdown list to launch Excel Help windows.

Help link in Home Ribbon.
Figure 1.15 Excel Help Window

Keyboard Shortcuts

Excel Help

  • Press the F1 key on your keyboard.
  • Excel for Mac icon Mac Users: Press F1 or hold down the Command key and press /

Key Takeaways

  • Excel is a powerful tool for processing data for the purposes of making decisions.
  • You can find Excel commands throughout the tabs in the Ribbon.
  • You can customize the Quick Access Toolbar by adding commands you frequently use.
  • You can add or remove the information that is displayed on the Status Bar.
  • The Help window provides you with extensive information about Excel.

Attribution

Adapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.

License

Icon for the Creative Commons Attribution 4.0 International License

Beginning Excel 2019 by Noreen Brown, Barbara Lave, Hallie Puncochar, Julie Romey, Mary Schatz, Art Schneider, and Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.