Chapter 1: Microsoft Excel Overview

What We’ll Cover >>>

• Excel Prep
• Excel for Windows and Mac
• The Excel Workbook
• Navigating Worksheets
• The Excel Ribbon
• The File Backstage
• Right-click Menus
• Saving Workbooks
• The Status Bar
• Excel Help

Microsoft® Office contains a variety of tools that help people accomplish many personal and professional objectives. Microsoft Excel is a versatile and widely used program in all the Office applications, and a lot of job descriptions specifically mention it as a core skillset. 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.

Excel Prep

Microsoft® Excel® is a ‘spreadsheet’ program/application. Spreadsheets tend to be applications for mainly quantitative information (analysis, computations) although many spreadsheets may not actually compute anything at all.

Taking a very simple view, Excel is a tool that allows you to enter quantitative data into an electronic spreadsheet to organize it and to apply one or many mathematical computations. These computations ultimately convert that quantitative data into information for data analysis and decision-making. 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 small business retailer, to calculate grades for students in a course, 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 home, how much you spend on car lease payments, or how much you need to save to reach your retirement goals. So, let’s get started!

Starting the Excel application

• This content will focus on the full installation of the complete Excel application on a computer setup, such as your home desktop machine, your travelling laptop, and workplace / school use from server installations. The process for using Excel Online and mobile device smaller Excel apps will be different and not focused on here.
• Note: Spreadsheet = worksheet, or workbook (file with one or more worksheets)

ACTION: Try Me activity

• Locate Excel on your computer. This can be done with your computer’s search tool so that you can locate the application icon.
• Click Microsoft Excel icon/execution file to launch the Excel application. You will then be presented with workbook options to help get you started.
• Options will include a Blank workbook, tours of program information, templates installed with your application, and the choice of opening an existing Excel file.
• New Excel workbooks start with a blank file of worksheet(s) – which are like tabbed pages for you to start new work.
• Existing files can be opened from your computer, an external drive, and an online source like OneDrive (if you have/use that).
• For now, choose Blank Workbook, which should open a new, blank workbook with a default of 3 Sheet tabs.

Excel for Windows vs Excel for Mac

The Excel for Windows and Excel for Mac software versions are very similar, although the Mac version can be a little behind in all the options and features available and may use different names/phrasing. 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 PC Windows. This book attempts to provide alternate screenshots and instructions for the Mac version when the differences are significant, and if there is no Mac Users option mentioned the steps should be the same as for PCs.

TIP: Microsoft Updates. Information/MS features can and do routinely change as the current SaaS online application version adjusts the MS Office programs when Microsoft makes modifications.

The Excel Workbook

A workbook (spreadsheet file) is an Excel file that contains one or more worksheets (which are like separate pages in a file folder). Excel will assign a default file name to the workbook, such as Book1, Book2, Book3, and so on, depending on how many new workbooks are opened. See image below.

The images below show a blank workbook after starting Excel and choosing Blank workbook. Take some time to familiarize yourself with this screen. Your screen may be slightly different based on the version you’re using.

TIP: Excel for Mac. This textbook won’t usually have comparison images for Excel for the Mac. However, if you have trouble finding something, use the Excel for Mac HELP area to ask and the program should link to Excel for Mac information to help you.

When your workbook opens, it should open with a default of 3 worksheets (named Sheet1, Sheet2, Sheet3) and you will be on the first worksheet. Your worksheet should already be maximized (or shown at full size) once Excel is started. However, if your screen looks it is undersized after starting Excel, you should click the maximize button.

Your worksheet has various spaces to keep in mind.

MedAttrib: author-generated. MS Excel for Windows User Interface.

MedAttrib: author-generated. MS Excel for Mac User Interface.

• The program frame, which in Excel is usually medium green in color, has a top bar which will show the name of the workbook, the Quick Access Toolbar (if you use one), a search bar, and some application window sizing (minimize, full size, and exit) buttons. Note that recent updates to MS Office 365 may default the program frames to white, not green.
• The menu bar, which shows menu items (like Home), will also display a contextual ribbon of tool icons below it.
• A bar that shows a cell address field, a function selector area, and the formula bar, which displays the content of active worksheet cells and also formulas you use.
• The worksheet working space, which by default shows the faint outlines of cells that make up rows and columns. The working space also shows a tab at the bottom of it that will be where you can rename the active worksheet. More than one tab indicates that you have more than one worksheet in your workbook file.
• A Status Bar at the bottom of the application displays Excel messages, viewing options, and a slider to increase or decrease the magnification of the worksheet’s working space. This can be customized.

Navigating Worksheets

In Excel, spreadsheet files are called workbooks, and their native saved format will be .xlsx. An Excel workbook contains tabbed pages called worksheets. A worksheet is like a section in a file folder and can also feel like a page, and a worksheet itself can actually have many pages once printed out or saved in a PDF. On the screen the worksheet will present as a single ongoing document with many columns (fields) and rows (records) of cells in which to add and work with data.

Data are entered and managed in cells by entering numeric and non-numeric data. Each cell in an Excel worksheet contains an identification address, which is defined by a column letter followed by a row number. For example, the top left cell A1. This would be referred to as cell location A1 (or cell reference A1) – Column A Row 1. You can navigate in an Excel worksheet with your mouse pointer or using the arrow buttons on your keyboard.

ACTION: Try Me activity, continued

• In the blank workbook, place your mouse pointer over cell A1 and click.
• Check to make sure column letter A and row number 1 are highlighted – they should appear a slightly different intensity of gray than the rest of the column and row address segments.
• Look at the application’s upper left field (below the ribbon) where you should see A1 in the Name Field.
• Move the mouse pointer to cell D6.
• Click and hold the left mouse button and drag the mouse pointer back to cell A1.
• Release the left mouse button. You should see several cells highlighted because you ‘selected’ them. IF you have trouble holding and dragging, you can instead select cell A1, then press and hold the Shift key, then click the cell D6. This will also select the same set of cells. They will be cells A1-D6.

This is referred to as a cell range and would be documented in formulas and reference as: A1:D6. 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. This is important because cell ranges will be an important part of formulas later on.

TIP: Cell Ranges in this book. This book will refer to cell ranges as Cells XX-XX, or Cells XX through XX when you need to take some action. When you see Cells XX:XX, that usually refers to how Excel shows the range reference in a formula/function.

Keyboard Shortcuts > Worksheet Navigation

• Use the arrow keys on your keyboard to select (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.
• Mac Users: Hold down the Function (Fn) and CMD 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 menu tabs running across the top. Each tab provides access to a contextual set of Excel commands. In this book, Ribbon, Ribbon Tabs, and Menu will be used interchangeably, as they often are in a workplace.

• 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. You may also need to Google to ask.

MedAttrib: Beginning to Intermediate Excel. MS Excel Ribbon.

Group title names on the Excel menu ribbon

If you look closely at the Excel ribbons/tabs  you will see that the ribbon is separated in groups of tool buttons, and each group has a title name. For instance, on the Home ribbon/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. In this book, instructions will usually point you to the Ribbon/Tab and specific Group name to help you find the right icon / command to use. Ribbon and Tab will be used interchangeably.

Mac Users: The default “View” for the Excel for Mac ribbon does not display these group title names. It is a good idea to change this view so you can see the group title names. Here are steps.

• Click the Excel menu option at top left above the ribbon
• Choose Preferences
• Click the “View” button
• Scroll down and check the box for Group Titles
• Close the View dialog box. The group title names should now display as shown above

Overview for each tab of the ribbon

• File: Also known as the Backstage view of the Excel workbook (Windows PC). Includes 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. MAC users will have to find these spread out in different ribbons (such as File/Preferences) and in the Apple menu for the program.
• Home: Includes 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, tables, Pivot tables, Internet links, symbols,  and text boxes.
• Page Layout: Includes commands used to prepare a worksheet for printing/distribution. 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: Includes commands used when working with external data sources such as Microsoft® Access®, Azure, 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: Provides access to help and support features such as contacting Microsoft support, sending feedback, suggesting a new feature, and community discussion groups. This 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 and this book does not cover it. 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 and this book does not cover it.
• See below on how to customize the ribbon to add or remove tabs.

The ribbon defaults to 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 Ribbon Display Options button at the top near right of the application; a recent update has changed this to let you choose Full Screen mode, which you can exit by electing three dots at the upper left of the screen to show other Screen View options for the ribbons/tabs. 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, then the ribbon will hide again.

Keyboard Shortcuts > Minimizing or Maximizing the ribbon. Toggle: Hold down the CTRL key and press F1 key. Mac: CMD and Option keys and press R to minimize, and do this again to maximize.

The File Backstage

The File tab (on the MS Windows PC operating system) is also known as the Backstage view of the workbook. It allows you to do tasks related to the management of your file. It does not display a ribbon like the other menu tabs. Instead, it includes a left-hand bar that shows a variety of features and commands related to the workbook that is currently open, to new workbooks, and/or to workbooks stored in other locations on your computer or network. To leave the Backstage view and return to the worksheet, click the arrow in the upper left-hand corner.

MAC USERS: You can find some of this same information – though it will look different – by accessing your Excel File/Preferences option, and selecting from the choices there.

• 

MedAttrib: author-generated. MS Excel for Windows PC OS Backstage Info page.

ACTION: Try Me, continued.

Included in the File tab are the default settings for the Excel application that can be accessed and modified by clicking the Options button, which may be hidden and require you to select “more” at the bottom of the left-hand menu to see the choice for Options. This 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.

Excel has a number of defaults that the program starts with, and you can change these as you become familiar with the way you would like to work with your files, such as how you would like to view, save, backup, and set other standards. You need to explore this for yourself to get familiar with what is available

MedAttrib: Beginning to Intermediate Excel. MS Excel Options Window for Windows PC.

Mac Users: To access these same options in Excel for Mac, you need to click the “Excel” menu option and choose “Preferences.”

Customize the Excel ribbon

You can customize the Excel ribbon for any or all of the menu tabs; however, unless you are a proficient power-user of Excel, this is not recommended because you may move or hide important things and need to reset the Ribbon to default to find them again. The instructions below will also work for customizing the Quick Access Toolbar, which shows in the top green bar of the Excel Application if there are selections in it. The Quick Access Toolbar is more nimble and less risky to personalize than the actual program ribbons.

Here are the steps to add additional tabs to the Excel ribbon:

• Click the File tab and choose More (if needed) Options
• Click on “Customize ribbon” (or Customize Quick Access Toolbar) at the left side of the Options screen
• Click the checkbox next to the Tab name that you want to add.

MedAttrib: Beginning to Intermediate Excel. MS Excel Customizing the ribbon.

Quick Access Toolbar

The Quick Access Toolbar is found at the upper left side of the Excel screen above the ribbon. 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, use the same process as for customizing the ribbon, which 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.

MedAttrib: Beginning to Intermediate Excel. MS Excel Customizing the Quick Access Toolbar.

Right-Click Menus

In addition to the ribbon and Quick Access Toolbar, you can also access many commands by right clicking anywhere on the worksheet, which will enable a dropdown menu (often contextual) of choices that are relevant to what you need to accomplish.

Mac Users: There is no “Right-click” option for Excel for Mac. Instead, hold down the Control key and click the mouse button.

MedAttrib: Beginning to Intermediate Excel. MS Excel’s Right-Click dropdown menu.

Saving Workbooks

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 your workbooks, so that you can easily find them again. 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 generally explain how to save a new workbook and assign it a file name.

TIP: Save Work Immediately and Often. BEFORE you do any work in a new workbook, make a habit of naming and saving your file right away so that you can find it again easily if interrupted or if some technical glitch happens while you are working. Then SAVE your work frequently so your changes are updated and saved. The Quick Access Toolbar shows a little disk icon, and the common keybind is CTRL S / Mac: CMD S.

• If you have not done so already, open a blank workbook in Excel.
• 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.
• Determine a location for saving on your computer by looking for and clicking the Browse button to open the Save As dialog box.
• Click in the File Name box near the bottom of the Save As dialog box. I recommend you give the file a name such as: Ch1-ExcelIntro.docx
• Review the settings in the screen for correctness and click the Save button.

TIP: Save Locations. It is not good to use the default save location in Excel. A professional tip is that you should get into the habit of setting up a directory on your hard drive which allows you to save important documents. And/or get into the habit of saving your documents with the other documents of any project / course / workspace you set up for your workflow, like the files for the same class, or files for the same accounting client, etc.

MedAttrib: Beginning to Intermediate Excel. MS Excel’s Save As Dialog entries.

Keyboard Shortcuts > Save. CTRL S / Mac CMD S

The Status Bar

The Status Bar is located below the worksheet tabs on the Excel screen. 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, and the magnification of the screen. You can customize the Status Bar as follows:

• Place the mouse pointer over any area of the Status Bar and right click to display the “Customize Status Bar” list of options.
• Mac Users: use “Control-click” on the Status Bar to display the “Customize Status Bar” options.
• Select your preferred option from the menu.

MedAttrib: Beginning to Intermediate Excel. MS Excel’s Customizing the Status Bar.

Excel Help

The Excel 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 ask your question and select from helpful option links or select the question mark from the dropdown list to launch Excel Help windows.

MedAttrib: Beginning to Intermediate Excel. MS Excel’s Help Window.

Keyboard Shortcuts > Excel Help. F1 key.