Excel Essentials 2019

By M.L. Humphrey

Microsoft Excel is one of the most valuable software programs to master. It's useful for personal finances, school, and business.

 

But trying to learn Excel can sometimes be intimidating because Excel is capable of so much it can be overwhelming to try to figure out what matters and what can be ignored.

 

Excel Essentials 2019 contains Excel 2019 Beginner, Excel 2019 Intermediate, and Excel 2019 Formulas & Functions, a series of three titles meant to take any Excel user from novice to advanced intermediate level using a simple and straight forward approach that focuses on the information you need at that point in time to master Excel.

 

This book is written for Excel 2019. If you need a more generic guide to Excel, then check out the Excel Essentials series which begins with Excel for Beginners.

• Language: English
• Publisher: M.L. Humphrey
• Release date: Aug 3, 2021
• ISBN: 9798201211929

M.L. Humphrey

Hi there Sci Fi fans, my name is Maurice Humphrey.I am a Vermont native, husband, father, grandfather, well over 60, Navy veteran, retired IBM engineer, retired printer repairman, Graduated: Goddard Jr. College, VT Technical College, and Trinity College. Over the years I’ve written technical articles, taught technical classes, and presented at technical conventions.I’ve been reading science fiction for over 50 years now. First books were “Journey to the Centre of the Earth” by Jules Verne and “The Stars Are Ours” by Andre Norton. I’ve read and collected many great stories, and a considerable amount of junk ones as well. I’d say by now that I probably have a good idea of what I consider a good story.

Book preview

Excel Essentials 2019

ALSO BY M.L. HUMPHREY

Listing of all books by M.L. Humphrey

Excel Essentials 2019

Excel 2019 Beginner

Excel 2019 Intermediate

Excel 2019 Formulas & Functions

Word Essentials 2019

Word 2019 Beginner

Word 2019 Intermediate

PowerPoint Essentials 2019

PowerPoint 2019 Beginner

PowerPoint 2019 Intermediate

Access Essentials 2019

Access 2019 Beginner

Access 2019 Intermediate

EXCEL ESSENTIALS 2019

M.L. HUMPHREY

CONTENTS

Excel 2019 Beginner

Excel 2019 Intermediate

Excel 2019 Formulas & Functions

About the Author

Copyright

EXCEL 2019 BEGINNER

EXCEL ESSENTIALS 2019 BOOK 1

INTRODUCTION

Microsoft Excel is an amazing program and I am so grateful to have learned it because I use it all the time as a small business owner as well as personally.

It allows me to organize and track key information in a quick and easy manner and to automate a lot of the calculations I need.

For example, on a personal level I have a budget worksheet that lets me track whether my bills have been paid, how much I need to keep in my bank account, and where I am financially.

In my professional career I’ve used it in a number of ways, from analyzing a series of financial transactions to see if a customer was overcharged to performing a text-based comparison of regulatory requirements across multiple jurisdictions.

(While Excel works best for numerical purposes, it is often a good choice for text-based analysis as well, especially if you want to be able to sort your results or filter out and isolate certain results.)

The purpose of this specific guide is to teach you the basics of what you need to know to use Microsoft Excel on a daily basis. By the time you’re done with this book you should be able to do over 95% of what you need to do in Microsoft Excel and should have a solid enough grounding in how Excel works and the additional help resources available that you can learn the rest.

The series does continue with Excel 2019 Intermediate, which covers more advanced topics such as pivot tables, charts, and conditional formatting, and Excel 2019 Formulas & Functions, which goes into more detail about how formulas and functions work in Excel and then discusses about a hundred of those functions, sixty in detail.

You are welcome to continue with those books but you shouldn’t have to in order to work in Excel on a daily basis. This book should be enough for that.

It was written specifically for Excel 2019, so all of the screenshots in this book are from Excel 2019 which, as of the date I’m writing this, is the most recent version of Excel.

However, because this book is about the basics of Excel, even if you are working in a different version of Excel most of what we’ll cover here should be the same. The basic functions of Excel (like copy, paste, save, etc.) haven’t changed much in the twenty-five-plus years I’ve been using the program.

If you previously purchased Excel for Beginners which was written using Excel 2013, most of the content of this book is the same and you probably don’t need to buy this book as well.

The visual appearance of Excel 2019 has been changed just enough from the 2013 version to be annoying, so it may help to have the updated screenshots, but don’t feel that you need to buy this book to use Excel 2019 if you’ve already read Excel for Beginners.

This book is not a comprehensive guide to Excel. The goal here is to give you a solid grounding in Excel that will let you get started using it without bogging down in a lot of information you don’t need when you’re getting started.

In this book I will often cover multiple ways of doing the same thing to show you the various options available to you. I may not cover all of the possible ways of doing something (I think we’re up to five or six ways of doing the same thing on some of this stuff), but I will usually cover at least two ways.

I highly recommend learning any of the control shortcuts that I give you. For example, to copy something you can use the Control key and the C key (which I will write as Ctrl + C). The reason to learn these shortcuts is because they have not changed in all the years I’ve been using Excel. Which means that even when Microsoft issues the next version of Excel and moves things around a bit (which they will because that’s one major way they make money is through new product releases) you’ll still know at least one easy way to perform the core tasks.

Also, when in doubt go with the right-click and open a dialogue version of doing something because that too seems to have remained relatively stable over the years and versions of Excel.

If what I just said didn’t make sense to you, don’t worry. The first thing we’re going to do is cover basic terminology so that you know what I’m talking about when I say things.

Alright then. Let’s get started with that.

BASIC TERMINOLOGY

First things first, we need to establish some basic terminology so that you know what I’m talking about when I refer to a cell or a row or a column, etc.

COLUMN

Excel uses columns and rows to display information. Columns run across the top of the worksheet and, unless you've done something funky with your settings, are identified using letters of the alphabet. As you can see below, they start with A on the far left side and march right on through the alphabet (A, B, C, D, E, etc.).

If you scroll far enough to the right, you'll see that they continue on to a double alphabet (AA, AB, AC, etc.).

Columns A through E

ROW

Rows run down the side of the worksheet and are numbered starting at 1 and up to 1,048,576 in Excel 2019.

(Be aware that earlier versions of Excel have less rows in a worksheet so that if you have a lot of data that uses all of the available rows your file may not be compatible with earlier versions of Excel.)

You can click into any cell in a blank worksheet, hold down the ctrl key, and hit the down arrow to see just how many rows your version of Excel has. To return to the first row use the ctrl key and the up arrow.

Rows 1 through 6

CELL

A cell is a combination of a column and row that is identified by the letter of the column it's in and the number of the row it's in.

For example, Cell A1 is the cell in the first column and the first row of the worksheet. When you've clicked on a specific cell it will have a darker border around the edges like in the image below.

Cell A1

CLICK

If I tell you to click on something, that means to use your mouse (or trackpad) to move the arrow on the screen over to a specific location and left-click or right-click on the option. (See the next definition for the difference between left-click and right-click).

If you left-click, this selects the item. If you right-click, this generally creates a dropdown list of options to choose from.

If I don't tell you which to do, left- or right-click, then left-click.

LEFT-CLICK/RIGHT-CLICK

If you look at your mouse or your trackpad, you generally have two flat buttons to press. One is on the left side, one is on the right. If I say left-click that means to press down on the button on the left. If I say right-click that means press down on the button on the right.

(If you're used to using Word you may already do this without even thinking about it. So, if that's the case then think of left-click as what you usually use to select text and right-click as what you use to see a menu of choices.)

Not all track pads have the left- and right-hand buttons. In that case, you’ll basically want to press on either the bottom left-hand side of the track pad or the bottom right-hand side of the trackpad.

SPREADSHEET

I'll try to avoid using this term, but if I do use it, I'll mean your entire Excel file. It's a little confusing because it can sometimes also be used to mean a specific worksheet, which is why I'll try to avoid it as much as possible.

WORKSHEET

A worksheet is basically a combination of rows and columns that you can enter data in. When you open an Excel file, it opens to worksheet one.

Excel 2019 has one worksheet available by default when a new file is opened and that worksheet is originally labeled Sheet1.

It is possible to add more worksheets to a workbook (that’s the entire Excel file) and we will cover that later. When there are multiple worksheets, the name of the current worksheet is highlighted in white to show that it’s in use.

FORMULA BAR

The formula bar is the long white bar at the top of the screen with the ƒχ symbol next to it.

If you click in a cell and start typing, you'll see that what you type appears not only in that cell, but in the formula bar as well. When you input a formula into a cell and then hit enter, the value returned by the formula will be what displays in the cell, but the formula will appear in the formula bar when you have that cell highlighted.

Formula Bar and value for Cell C1

TAB

I refer to the menu choices at the top of the screen (File, Home, Insert, Page Layout, Formulas, Data, Review, View, and Help) as tabs. Note how they look like folder tabs from an old-time filing system when selected? That's why.

Home, Page Layout, Data, View, etc. tabs

Each menu tab you select will show you different options. On my Home tab I can do things like copy/cut/paste, format cells, edit cells, and insert/delete cells, for example.

SCROLL BAR

On the right side and along the bottom of the screen are two bars with arrows at the ends. If you left-click and hold on either bar you can move it back and forth between those arrows. This lets you see information that's off the page in your current view but part of the worksheet you're viewing.

You can also use the arrows at the ends of the scroll bar to do the same thing. Left-click on the arrow once to move it one line or column or left-click and hold to get it to move as far as it can go.

If you want to cover more rows/columns at a time you can click into the blank space on either side of the scroll bar to move an entire screen at a time, assuming you have enough data entered for that.

Using the scroll bars only lets you move to the end of the information you’ve already entered. You can use the arrows instead of clicking on the scroll bar to scroll all the way to the far end of the worksheet.

---

Side and bottom scroll bars

DATA

I use data and information interchangeably. Whatever information you put into a worksheet is your data.

TABLE

I may also refer to a table of data or data table on occasion. This is just a combination of rows and columns that contain information.

This should not be confused with the Word version of a table which is a set aside combination of rows and columns. Even if you create a table in Excel with a border around the edges and nothing else in the document, you’re still working in a worksheet that contains a set number of columns and rows that never changes no matter what you do.

SELECT

If I tell you to select cells, that means to highlight them. If the cells are next to each other, you can just left-click on the first one and drag the cursor (move your mouse or finger on the trackpad) until all of the cells are highlighted. When this happens, they'll all be surrounded by a dark box like below.

Cells A1 through C3 selected

If the cells aren't next to each other, then what you do is left-click on the first cell, hold down the Ctrl key (bottom left on my keyboard), left-click on the next cell, hold down the Ctrl key, left-click on the next cell, etc. until you've selected all the cells you want.

The cells you've already selected will be shaded in gray. The last cell you selected will be surrounded by a dark border.

In the image below cells A1, C1, A3, and C3 are selected. Cell C3 was selected last.

Cells A1, A3, C1, and C3 selected

CURSOR

If you didn't know this one already, it's what moves around when you move the mouse (or use the trackpad). In Excel it often looks like a three-dimensional squat cross or it will look like one of a couple of varieties of arrow. (You can open Excel and move the arrow to where the column and row labels are to see what I mean.) The different shapes the cursor takes represent different functions that are available.

ARROW

If I say that you can arrow to something that just means to use the arrow keys to navigate from one cell to another. For example, if you enter information in Cell A1 and hit Enter, that moves your cursor down to cell A2. If instead you wanted to move to the right to Cell B1, you could do so by using the right arrow.

DROPDOWN

I will occasionally refer to a dropdown or dropdown menu. This is generally a list of potential choices that you can select from. The existence of the list is indicated by an arrow next to the first available selection. You can see a number of examples in the image below.

Dropdown arrows for Paste, Copy, Font, Font Size, Cell Alignment, and Cell Format

I will also sometimes refer to the list of options you see when you click on a dropdown arrow as the dropdown menu.

DIALOGUE BOX

Dialogue boxes are pop-up boxes that contain a set of available options and appear when you need to provide additional information or make additional choices. For example, this is the Find and Replace dialogue box which appears when you select the Replace option from the Editing section of the Home tab:

Find and Replace dialogue box

ABSOLUTE BASICS

It occurs to me that there are a few absolute basics to using Excel that we should cover before we get into things like formatting.

OPENING AN EXCEL FILE

To start a brand new Excel file, I simply click on Excel from my applications menu or the shortcut icon I have on my computer’s taskbar, and it opens a new Excel file for me.

If you’re opening an existing Excel file, you can either go to the folder where the file is saved and double-click on the file name, or you can (if Excel is already open) go to the File tab and choose Open from the left-hand menu.

Open option in Excel

That will show you a list of Recent Workbooks. If it includes the one you’re looking for, you can just click on it once and it will open.

File open options

If you don’t see the file you’re looking for, you can click on the list of navigation options in between the left-hand menu and the list of Recent Workbooks and navigate to where the file is stored. When I click on This PC it gives me a list of recently used folders. If the document I want isn’t in one of those folders, I can use the Browse option instead. If you use the cloud, OneDrive is also an option.

Excel may show an error message when you open some files in Excel. The one I usually see is about Excel opening a file in Protected View because it’s a file I didn’t create.

Don’t panic, it’s fine. You can see the contents without doing anything or if you need to edit just click on Enable Editing as long as you trust the source of the file.

SAVING AN EXCEL FILE

Excel has two options for saving a file, Save and Save As. If you have an existing file that’s already been saved before and no changes to make to its name, location, or type, you can use Ctrl + S or click on the small computer disc image in the top left corner to save the file once more. Simply closing the file will also prompt Excel to ask if you’d like to save your changes to the document.

If the file you are saving is an .xlsx file type, you should really see nothing else at that point. The file is saved when you use Ctrl + S or the disc image or click on Save when you close the file..

If the file you’re trying to save is an .xls file type (so an older file type) or another type of file like a .csv file or a .txt file, you may see an additional message when you try to Save about compatibility. The dialogue box that appears will tell you the issue that saving the file as-is will create and you need to decide whether that’s okay or whether to go back into the file and fix the issue before saving.

(Usually I can just say continue when this happens because it opened from the old format and is saving to the old format so isn’t going to be a huge concern as long as I didn’t do some new fancy analysis in the meantime.)

All of the above options will also work for a brand new file, but they will bring up a dialogue box asking what you want to name the file and where you want to save it.

Clicking on More Options from that dialogue box will take you to the Save As screen that can also be reached by clicking on Save As on the left-hand side after clicking on the File tab.

Save this file dialogue box

With the Save As option available under the File tab, Excel will ask you to choose which folder to save the file into. You can either choose from the list of recent folders on the right-hand side, or navigate to the folder you want using the locations listing on the left-hand side (OneDrive, This PC, and Browse).

Save file location options

Once you choose Browse or select a folder, a dialogue box will appear where you can name the file and choose its format.

Save As dialogue box

You can also navigate to a different file location at this point if you’re more comfortable working in the Save As dialogue box.

To change the file type, click on the dropdown arrow for the option next to Save As Type under the File Name.

The default in Excel 2019 is to save to an .xlsx file type. Know that this file type is not compatible with versions of Excel prior to Excel 2007. At this point you’re probably safe working with the default .xlsx file type, but this can be an issue with older versions of the program.

If you routinely work with someone who has an older version of Excel I would highly recommend saving your files as Excel 97-2003 Workbook .xls files instead so that you don’t create a file that won’t work for them. It’s much easier to initially save down to an older version than try to do it after the fact.

As mentioned above, if functionality or content will be lost by saving to the format you chose, Excel will generate a warning message about compatibility when you save the file.

If you have an existing file that you want to rename, save to a new location, or save as a new file type, use the Save As option by going to the File tab and choosing Save As from there.

DELETING AN EXCEL FILE

You can’t delete an Excel file from within Excel. You’ll need to navigate to the folder where the file is stored and delete the file there without opening it.

To do this, first, click on the file name. (Only enough to select it. Make sure you haven’t double-clicked and highlighted the name which will then try to rename the file.) Then choose Delete from the menu at the top of the screen, or right-click and choose Delete from the dropdown menu.

RENAMING AN EXCEL FILE

You might want to rename an Excel file at some point. You can Save As and choose a new name for the file, but that will mean you now have two versions of the file, one with the old name and one with the new name.

A better option is to navigate to the folder where you have the file saved, click on it once to highlight the file, click on it a second time to highlight the name, and then type in the new name you want to use. If you do it that way, there will only be one version of the file, the one with the name you wanted.

However, if you do rename a file by changing the name in the source folder, know that you can’t then access it from the Recent Workbooks listing under Open file. Even though it might be listed there, Excel won’t be able to find it because it no longer has that name. (Same thing happens if you move a file from the location it was in when you were last working on it. I often run into this by moving a file into a new subfolder when I suddenly get inspired to organize my records.)

CLOSING AN EXCEL FILE

When you’re done with Excel you’re going to want to close your file. The easiest way to do so is to click on the X in the top right corner of the screen. Or you can use Alt +F4. (If you use Alt+F4 this will only work if the F functions are set up to be the default keys on your keyboard.)

Close file X in corner of screen

To just close a worksheet but keep Excel open you can use Ctrl + W.

NAVIGATING EXCEL

The next thing we’re going to discuss is basic navigation within Excel. These are all things you can do that don’t involve inputting, formatting, or manipulating your data.

BASIC NAVIGATION WITHIN A WORKSHEET

Excel will automatically open into cell A1 of Sheet1 for a new Excel file. For an existing file it will open in the cell and worksheet where you were when you last saved the file. (This means it can also open with a set of cells already highlighted if that’s what you were doing when you last saved the file.)

Within a worksheet, it’s pretty basic to navigate.

You can click into any cell you can see in the worksheet using your mouse or trackpad. Just place your cursor over the cell and left-click.

From the cell where you currently are (which will be outlined with a dark border), you can use the up, down, left, and right arrow keys to move one cell at a time in any of those directions.

You can also use the tab key to move one cell at a time to the right and the shift and tab keys combined (shift + tab) to move one cell at a time to the left.

To see other cells in the worksheet that aren’t currently visible, you can use the scroll bars on the right-hand side or the bottom of the worksheet. The right-hand scroll bar will let you move up and down. The bottom scroll bar will let you move right or left. Just remember that the bars themselves will only let you move as far as you’ve entered data or the default workspace. You need to use the arrows at the ends of the scroll bars to move farther than that.

For worksheets with lots of data in them, click on the scroll bar and drag it to move quickly to the beginning or end of the data. To move one view’s worth at a time, click in the blank gray space around the actual scroll bar.

If you’re using the scroll bars to navigate a large amount of data or records, know that until you click into a new cell with your mouse or trackpad you will still be in the last cell where you had clicked or made an edit. So be sure to click into one of the cells you see rather than try to immediately type or to use the tab or arrow keys to navigate. (I run into this frequently when I have Freeze Panes on and then try to use an arrow key to move from the first row of column labels into my data, forgetting that the data I’m actually seeing is hundreds of rows away from that top row. Don’t worry, we’ll discuss Freeze Panes later.)

F2

If you click in a cell and hit the F2 key, this will take you to the end of the contents of the cell. This can be very useful when you need to edit the contents of a cell or to work with a formula in that cell. I use it often enough that every time I get a new computer I make sure that the F keys are the default rather than the volume controls, etc.

ADDING A NEW WORKSHEET

When you open a new Excel file in Excel 2019, you'll have one worksheet you can use named Sheet1.

If you need another worksheet, simply click on the + symbol in a circle next to that Sheet1 tab.

Insert Sheet tab option

If you already have multiple worksheets in your workbook, the + sign will be located to the right of the last worksheet.

You can also go to the Home tab under the Cells section and left-click the arrow under Insert and then select Insert Sheet from the dropdown menu there.

Insert Sheet menu option

DELETING A WORKSHEET

Sometimes you’ll add a worksheet and then realize you don’t want it anymore. It’s easy enough to delete. Just right-click on the name of the worksheet you want to delete and choose the Delete option from the dropdown menu (which will actually drop upward.)

Delete sheet tab option

If there was any data in the worksheet you’re trying to delete, Excel will give you a warning message to that effect in a dialogue box.

If you don’t care, click Delete. If you do care and want to cancel the deletion, click Cancel.

Another way to delete a worksheet is to go to the Cells section in the Home tab, left-click on the arrow next to Delete, and choose Delete Sheet from the dropdown menu there.

Delete sheet menu option

Be sure you want to delete any worksheet you choose to delete, because you can’t get it back later. This is one place where undo (which we’ll discuss later) will not work.

BASIC NAVIGATION BETWEEN WORKSHEETS

Once you have multiple worksheets in your workbook, you can navigate between them by either clicking on the name of the worksheet you want at the bottom of the screen or by using Ctrl + Page Up to move one worksheet to the left or Ctrl + Page Dn to move one worksheet to the right.

The Ctrl shortcuts do not loop around, so if you’re at the first worksheet and want to reach the last one you need to use Ctrl + Page Dn to move through all of the other worksheets to get there.

Or you could just click onto the last one like I do and skip the ctrl shortcut.

If you have too many worksheets to see their names on one screen, use the arrows at the left-hand side of the worksheet names or the … at the ends (when visible) to see the rest. The … will take you all the way to the beginning or the end.

INSERT A CELL IN A WORKSHEET

Sometimes you will just want to insert one cell or a small handful of cells into your worksheet. This will happen when you already have data entered into the worksheet and realize that you need to put additional data in the midst of what you already have entered.

(Because, remember, Excel worksheets have a fixed unchanging number of cells based upon the numbers of rows and columns they contain. So inserting a cell isn’t really inserting a cell so much as telling Excel to move all of your information from that point over or down to make room for the new cell or cells.)

To insert a cell or cells, select the cell or cell range where you want to insert your new blank cells, right-click, and choose Insert from the dropdown menu.

Insert Cells dropdown option

You'll be given four choices, Shift Cells Right, Shift Cells Down, Entire Row, and Entire Column.

Insert dialogue box

Shift Cells Right will insert your cell or range of cells by moving every other cell in that row or rows to the right to make room for the new cell or cells.

Shift Cells Down will insert your cell or range of cells by moving every other