Advanced Analytics with Excel 2019: Perform Data Analysis Using Excel’s Most Popular Features

CHAPTER 1

Getting Started with Excel

1.1 Workbook and Worksheets

When we talk about working in Excel, we basically work in a workbook, that is, the file that is created by Excel with the default extension ‘.xlsx’. Each workbook contains one or more worksheets and by default, they are named as Sheet1, Sheet2, Sheet3, and so on, in the sequence in which they get created. The users can rename the worksheets by right-clicking on the sheet name and selecting ‘Rename’ from the options, with a condition that each sheet name in a workbook is unique.

Sheets are structured as two-dimensional matrix with the columns named alphabetically (A-Z, AA-AZ, BA …, ZZ, AAA, AAB, and so on) and the rows numerically (1 through 1048576). Each sheet contains individual cells which can contain number, text, or formula. The intersection of a row and a column is a single cell which is referenced using the column letter and row number. For example, the first cell in the worksheet is referenced as A1, that is, column A row 1. A worksheet also has an invisible drawing layer which holds charts, images, and diagrams. Objects on the drawing layer sit over the top of the cells, however, they are not inside the cells like a number or a formula.

An open Excel workbook screen looks like the following screenshot. It also shows the important and useful elements that are often used:

Figure 1-1: Workbook Screen

1.2 Navigation with Keyboard

Users are quite familiar with and find it easy to navigate through the cells using their mouse. But you can also use the standard navigation keys on the keyboard to move around the worksheet. For example, the down arrow moves the active cell down by one row, the right arrow moves it one column to the right, and so on, the PgUp and PgDn keys move the active cell up or down by one full window. There are a lot of useful keyboard shortcuts available which are listed in Chapter 2 Perform Functions with Shortcut Keys.

1.3 Tabs and Ribbons

The user interface of Excel, like all other Microsoft products such as Word, PowerPoint, and so on, has Tabs and Ribbons. Each tab (Home, Insert, Page Layout, and so on) has an associated Ribbon with several group of buttons for working. For example, if you click the Formulas tab, you get a Ribbon with buttons that are useful for working with formulas. Also, when you hover the mouse pointer over a Ribbon button, it will show the description that contains the command’s name along with a brief description.

Figure 1-2: Tabs and Ribbons

1.3.1 Contextual Tabs

In addition to the standard tabs, Excel includes contextual tabs. Whenever an object (such as a chart, a table, or a SmartArt diagram) is selected, specific tools for working with that object are made available in the Ribbon of the Contextual tab. For example, when you select a chart, two tabs ‘Chart Design’ and ‘Format’ become visible. As shown in screenshot below, the Ribbon for the contextual tab ‘Chart Design’ is shown with options to modify the design of the selected chart.

Figure 1-3: Contextual Tabs and Ribbons

1.3.2 Tab and Ribbon Visibility

If you need more space, you can right-click on any Ribbon and select ‘Collapse the Ribbon’ button, you can then restore it in the same way. Also, note that depending on the width of the Excel window, some Ribbons might not show fully, however, everything is still available. To toggle the Ribbon’s visibility, use the keyboard shortcut Ctrl + F1 (or double-click the Tab at the top). You can also change the Tab and Ribbon visibility option using a control named ‘Ribbon Display Options’ (next to the Minimize button) on the title bar. The control has three options: ‘Auto-Hide Ribbon’, ‘Show Tabs’ and ‘Show Tabs and Commands’.

There is also an option for customizing the Ribbons; you can right-click on any Ribbon and select ‘Customize the Ribbon…’ or go to ‘File->Options’ to launch the Excel Options dialog box.

Figure 1-4: Customizing Ribbons

1.4 File Menu

The File menu takes you to the backstage view where you can perform file operations. You can also open the ‘Excel Options’ dialog box from there. To go back and leave backstage view, simply click on the back arrow at the top left.

Figure 1-5: File Menu

1.5 Quick Access Toolbar (QAT)

There is a Quick Access Toolbar (QAT) at the top-left corner of the Excel window. You can put your favorite buttons on this toolbar so that they are always visible and available. The QAT comes with a few favorite buttons, however, you can add more. The QAT can be customized by clicking on the drop-down arrow to the right of your QAT. You will see a list to the right, of the most used buttons, which you can then check to add them to your QAT.

Figure 1-6: More Commands option in QAT

You can also click on the ‘More Commands’ item in this list, as shown in preceding screenshot, to launch the dialog box which contains several other buttons that you can add to your QAT.

Figure 1-7: Quick Access Toolbar

Adding Superscript and Subscript commands

Excel allows you to add shortcuts to the Superscript and Subscript commands to your Quick Access Toolbar. Note that this feature is not available in previous versions of Excel, that is, Excel 2016 and older.

Go to ‘Excel Options -> Quick Access Toolbar’ and select Subscript or Superscript from the commands list and click ‘Add>>’.

Figure 1-8: Inserting Subscript and Superscript in QAT

Although Excel doesn’t have quick keyboard shortcuts to these commands, you can navigate the menus and dialogs with just the keyboard. Use Alt+HFNE for Superscript, and Alt+HFNB for Subscript.

1.6 Excel Options

Excel has many options for changing the software’s behavior. Click on the ‘File’ button in the upper-left corner of the screen and then on ‘Options’ to see all the options. Options are grouped in categories: General, Formulas, and others. Though the option names are self-explanatory, for more details on what each option means, you can either click on the information icon or the help button on the upper-right corner of the screen.

For example, under the Advanced group, you will see many Editing options which you can change based on your style of working, such as using the Enter key to move the selection down, allowing Automatic flash fill, and so on.

Figure 1-9: Advance Editing options

Most of the options are for Excel as a whole, not for any workbook or worksheet you might have opened. However, there are a few that are workbook-specific or worksheet-specific. For example, in the Advanced group, there are two sets of options, ‘Display options for this workbook’ and ‘Display options for this worksheet’ that can be varied from one workbook/worksheet to another.

Figure 1-10: Advance Options at Worksheet & Workbook level

You can also change the look and feel of all your Office programs by changing the Office Theme or Background. Go to ‘File > Options > General > Office Theme’

Figure 1-11: Change Office Theme / Background

The Black theme provides the highest-contrast visual for your Office theme. Note that the Black theme is not available in previous versions of Excel, that is, Excel 2016 and older.

1.7 Create a New Workbook, Print and Save

1.7.1 Create New File

You can create a new Excel file in your Window’s explorer by going to the New option on the Home Tab or by right-clicking on any empty space in the explorer and choose ‘New-> Microsoft Excel Worksheet’. Or launch Microsoft Excel application, select a Blank Workbook or use an existing Template, Take a Tour, and so on, as shown in the following screenshot. You can also use the shortcut key CTRL+N, to create a new workbook:

Figure 1-12: Create New Excel file

You can create as many worksheets required in a workbook and rename them as discussed earlier. There are many useful keyboard shortcuts for working in a worksheet, as detailed in Chapter 2 Perform Functions with Shortcut Keys. Working with data within the worksheet will be discussed in the later chapters of this book.

1.7.2 Print File

You can print the entire workbook, active sheets, or the selected area. The Print option is available on the File menu or you also can use the keyboard shortcut CTRL+P to launch the Print dialog box with all the print settings that can be configured, as in other Microsoft products.

Figure 1-13: Print Settings

1.7.2.1 Print Active Sheets

Select the worksheets that you want to print. Click on the tab of the sheet for selecting a single worksheet or press the SHIFT key and click the tabs of the adjacent sheets for multiple sheets selection or in case you want to select multiple non-adjacent sheets then press the CTRL key and click on the sheet tabs.

Go to ‘File->Print’ menu or use the keyboard shortcut CTRL+P to launch the Print dialog box, select ‘Print Active Sheets’ in ‘Settings’ (Figure 1-13) and then click on ‘Print’.

1.7.2.2 Print Entire Workbook

Open the workbook and go to ‘File->Print’, select ‘Print Entire Workbook’ (as shown in Figure 1-13) and click on ‘Print’. In case you want to print multiple workbooks, in the File Explorer, select the file, right-click and select ‘Print’ option. You can also Print the workbook as a file, using the ‘Print to File’ option in the Print dialog box under the ‘Printer’ setting as shown in below screenshot.

Figure 1-14: Printer setting

1.7.2.3 Print Selection

Open the worksheet and select the range of data that you want to print. Go to ‘File->Print’ menu, in the Print dialog box, select ‘Print Selection’ (as shown in Figure 1-13) and click on ‘Print’.

Print Selection with defined Print Area

Another way to print selection is by defining print area. A worksheet can have multiple print areas defined and each print area will print as a separate page. When the print areas are defined, only those are printed when you print the worksheet. You can add cells to expand the print area as needed, and you can clear the print area to print the entire worksheet.

Open the worksheet and select a single data range or multiple data ranges by pressing the CTRL key. Go to the ‘Page Layout’ tab, in the ‘Page Setup’ group, select ‘Print Area’ and click on ‘Set Print Area’ to define the print area as shown in screenshot below. Now, go to ‘File->Print’ to open the Print dialog box and select ‘Print Active Sheets’ which will print only the defined data ranges as the print area.

Figure 1-15: Set / Clear Print Area

Even if the file is closed and opened again, the defined print areas are saved, and you do not need to do the selection again and define. Note that in order to remove the print area, you need to click on ‘Clear Print Area’ as shown in preceding screen shot (Figure 1-15).

Print Tables and Objects

When you select an Excel table in the worksheet and go to ‘File->Print’ menu, under ‘Settings’ you will see a new option ‘Print Selected Table’ as shown in screenshot below.

Figure 1-16: Print Selected Table option

Sometimes you may find that certain objects like text boxes, buttons, and arrows do not get printed. To correct that, right-click on any such object, select ‘Size and Properties’, and click on the Properties tab. This contains a ’Print object’ option that you can check or uncheck.

Figure 1-17: Print Object option

1.7.3 Save and Save As File

Saving the Excel file is as simple as printing and similar to what you see in other Microsoft products, with only difference that it shows the relevant file types. The first time you save an Excel workbook, Excel takes you to the Backstage screen that lets you choose the location for the file; online location or local computer and launches a Save dialog box. In the dialog box you can enter the Filename and the file type or extension you want to save the file in, click on ‘Save’ button to save the file.

By default, Excel also saves a backup copy of your work automatically in every 10 minutes. AutoRecover setting can be changed, go to ‘File -> Options’ and click on the Save tab of the Excel Options dialog box.

The default file extension is ‘.xlsx’, however, it also gives other files type options as well, as shown in below screenshot. If you want to save in a version compatible with older Excel version, then you can choose Excel 97-2003 Workbook (*.xls) or if you require a macro-enabled version, then choose Excel Macro-Enabled Workbook (*.xlsm) or a comma delimited files as CSV UTF-8 (*.csv), and so on.

Figure 1-18: Save As options

On saving a file as CSV, Excel used to show a warning, that is, This file may contain features that are not compatible with CSV…. Excel has now removed this warning in the latest version of Excel that is Excel 2019.

1.8 Understanding Worksheet Basics

Worksheets are the playground for entering, editing, formatting, analyzing, and visualizing information or data. You can perform a lot of operations in the worksheet like renaming the sheet, adding/deleting a sheet, controlling the worksheet view, inserting/deleting rows and columns, grouping/ungrouping data, formatting data, and so on. In this chapter, we will learn the worksheet basics with the help of examples.

1.8.1 Commands for entering/editing data

You can use the mouse as well as keyboard to enter, modify, or delete data in the worksheet. There are many keyboard commands (see Chapter 2 Perform Functions with Shortcut Keys), but here, we will list only a few that are frequently used, will help in your daily work with Excel and in the following chapters.

You can also change few settings like pressing the ENTER key will move the selection in the right side instead of down, using ‘File->Options->Advanced->Edit Options’. In this book, we assume the preceding commands hold true, unless specified.

1.8.2 Insert/Delete Cells

Excel gives options in the ‘Home’ tab to insert new sheets, rows, columns, and even specific cells relative to the selection. Similarly, you can delete sheets, rows, columns, and cells.

Figure 1-19: Insert / Delete Rows & Columns menu options

These options are also available on right-click of the mouse.

Figure 1-20: Insert / Delete Rows & Columns options on right click

1.8.2.1 Insert/Delete/Rename Sheet

Open the workbook in which you want to insert/delete worksheets. By default, it has one worksheet named ‘Sheet1’.

To Insert, go to the ‘Home’ tab, in the ‘Cells’ group and click on ‘Insert Sheet’. The new sheet gets added on the left of the existing sheet with name ‘Sheet2’. The sheet number (in the sheet name) depends on the number of sheets that were already created in the workbook. Assuming your worksheet has ‘Sheet1’, ‘Sheet2’, and ‘Sheet3’, then the new sheet will be ‘Sheet4’. Now delete all the sheets except Sheet1 and again insert a new sheet. The new sheet in this case will be ‘Sheet5’, as Sheet4 was the last one that was created, even though you deleted it.

To Delete a single sheet, select the sheet tab and click on ‘Delete Sheet’. In case you want to delete multiple sheets, press CTRL and select the sheet tabs (non-adjacent) or press SHIFT and select the sheet tabs (adjacent) and click on the ‘Delete Sheet’ option. On delete, the sheets and its content are removed from the workbook.

You can Rename the sheet, either by double-clicking on the sheet tab and typing the new name or right-clicking on the mouse and selecting the ‘Rename’ option.

Figure 1-21: Rename Sheet

1.8.2.2 Insert/Delete Rows and Columns

The ‘Insert Sheet Rows’ option inserts an empty row above the selected row. In case you selected multiple adjacent rows (by pressing SHIFT key), say three in number, then it will insert the same number of empty rows, that is, three empty rows above the row on top of the selection. In case you selected multiple non-adjacent rows (by pressing CTRL key), then it inserts a single empty row above each selected row.

The ‘Insert Sheet Columns’ works in the same way; new columns are inserted on the left of the selected column.

To Delete, select the rows/columns to delete (single or multiple) and click on ‘Delete Sheet Rows’ / ‘Delete Sheet Columns’. Note that Insert and Delete options are also available by selecting the range through the keyboard and also through right-click (see Figure 1-20).

In the following example, the cell range A1:E4 has text entered as ‘Row 1’, ‘Row 2’, ‘Column 1’, ‘Column 2’ and so on.

Using the preceding example, we will understand the different Insert and Delete operations.

Select the cells or entire rows with values ‘Row 2’ and ‘Row 4’. Right-click and select option ‘Insert’ or go to ‘Home -> Insert Sheet Rows’ to insert rows. It will insert one empty row each above the selected cells or rows. Now select cells with text ‘Column 2’ and ‘Column 3’ (or entire columns C and D). Right-click and select option ‘Insert’ or go to ‘Home -> Insert Sheet Columns’, it will insert two empty columns on the left of the column with text ‘Column 2’.

1.8.2.3 Insert/Delete Cell range

The ‘Insert cells’ option adds the cells to the left of the selected cells and ‘Delete Cells’ deletes only the selected cells, unlike the Insert/Delete Rows and Columns that work on the entire Row and Column.

In the following example, we will see how inserting/deleting cells impact the other cells. Select the cells with values V2, V6, and V10, and insert cells by selecting the option to shift cells right. New cells are inserted to the left of the selected cells; the selected cells and all the cells beyond