I know what you’re thinking.
You know your way around Excel.
Maybe pretty well.
And yet I want to encourage you to stick around and go through this material. It will either serve as a review and a reinforcement of what you already know, or as I am often surprised to learn, some of you will tell me that while you know Excel really well, there are things you picked up here that you didn’t know before. Things that will make your life easier.
As I progress in this course I am going to assume that you know everything from before really well. If I make reference to something and you don’t remember or know how to do it, it was probably covered in an earlier lesson.
A spreadsheet is made up of Sheets. These are the tabs across the bottom. Those sheets make up an entire workbook. Each sheet contains cells. Cells are laid out in rows and columns.
Here’s some excel trivia you can play with your friends at your next party.
How many rows does a Microsoft Excel spreadsheet have?
How many columns does a Microsoft Excel spreadsheet have?
What is the column reference of that last, 16,384th column?
Microsoft Excel spreadsheets are quite large. Learning your way around is important.
Before we get into the second lesson which will go over Navigation tips and Keyboard Shortcuts, let’s look at the ribbon.
This is the region at the top. You have tabs:
- The Ribbon
- Page Layout
- Developer (advanced – you may not have this by default)
The Home Tab
The Home tab has a lot of the most common things you will use. Simple formatting, Copy and Paste functions, inserting and deleting are the basic things you will see here. Depending on your screen size and resolution you may also see the preset styles here.If not you will see them when you click where it says “Cell Styles.”
These are common styles used for specific and “standard” formatting. If your spreadsheet has (eg) formula based output, then you might use that standard style so people will recognize that this is not something they are supposed to enter.
The Insert Tab
The Insert tab has lots of things you can “insert” into a spreadsheet. Funny how that works. In the advanced course we’ll be inserting a Pivot Table. This is where we’ll go! The best way to get familiar with this is to go there, and observe all of the things you can insert into a spreadsheet.
Heck you can insert a Binomial Theorem into your spreadsheet!
I have no idea WTF that is either but it sure as hell will make you look smart if you stick one of those somewhere in all of your spreadsheets. Especially if you are going to show it in some sort of demo. You can reference it, and say that if time allows you’ll discuss it later. But don’t ever discuss it later. Unless of course you actually know what it means 🙂
In all seriousness, the things you will most likely, most often come here for are the pivot tables, and charts.
The Page Layout Tab
The page layout tab is where you’ll go when you need to print your spreadsheet. I hope you never use this, because it is the year 2018 and I hope you will never print a spreadsheet in 2018.
In fact doing so will cause you to lose a lot of the benefits of having information in a spreadsheet. A printout is not dynamic. Nothing can be changed. The point of a spreadsheet is to have a dynamic document, where information can flow freely from one place to another.
Printing a spreadsheet will take away all of the functionality of the spreadsheet. Besides, wouldn’t it be easier (in case you want to send your spreadsheet to someone without them being able to make changes) to save it as a PDF and email it?
If you MUST print your spreadsheet, then the Page Layout tab is where you go to make sure the printed version will not look like something that came out of a dot matrix printer from 1986.
The Formulas Tab
You guessed it! This is where you’ll go when you want to write your first formula. Actually your first formula will probably be something simple like “=B2.” You would need the formula tab for that. But any time I am writing a complicated formula, or want to see if a certain function exists, this is where I go. This is also where I go to learn new functions.
Often times this is a “necessity is the mother of invention” kind of thing. Have an idea of what you want to accomplish in terms of a calculation? Head over here and research it.
Hint: Click the function button right next to the formula bar (just below the ribbon where you can see the contents of the selected cell. Then you can search functions all day, and learn how they work.
Many years ago when I wanted to develop a timesheet in Excel, I needed to know how to perform operations on time. I needed to be able to subtract the starting time from the ending time to determine the duration of the entry. So I went in here, and started searching Date and Time functions. Turns out it was a simple subtraction formula that I needed. Then I needed to multiply the result by 24 to get it into a decimal format, as in .25 for 15 minutes. I learned that by a lucky guess. Meanwhile during my research I learned all kinds of things you can do with dates and times. This proved to come in very handy later in my financial modeling work.
If you really want to get a skills upgrade in Excel, spend some time in the formulas tab. Research a function. Look at how the formula is written. Think about how you can use this in a real life use case, and play with it. Write from formulas. Who cares if what you come up with doesn’t make sense? Keep playing with it until it does.
The Data Tab
This is another tab where you will want to spend some time. We will take a close look at this in the intermediate class. Anything you want to do to your data will happen here:
- Splitting text from one column into more than one
- Scenarios – I haven’t use this in a while, but it is really cool!
When you have a lot of data such as a big list, or a lot of transactions, the Data functions in Excel will make it really easy to analyze that data very quickly. Later in this course (Section 3) you will see how we can filter a large contact list for the people who are using AOL e-mail so we can make fun of them!
You don’t have an AOL email do you?
The Review Tab
This is for collaboration. This is another tab I never use, because if I am collaborating on a spreadsheet, I will go straight to Google Sheets these days. In fact I use Google Sheets almost exclusively these days, but I know a lot of you out there still want so use Excel So I did this course in Excel. I’ll probably do the google sheets version. It might look something like this – “You know all the stuff we did in that Excel course? It all works the same in Google Sheets. It just looks a little different.”
Meanwhile here in the review tab you can add comments to a cell so that others using your spreadsheet can view them, and reply. This is how you can have a conversation around the development of a spreadsheet without actually altering it.
This is also where you can go to protect the sheet, so that people can destroy all of your hard work.
The View Tab
The View tab as you can imagine is where you go to make changes to what your spreadsheet looks like. One of my favorite things to do here is to turn off the gridlines. I often find them distracting, and I prefer to use borders to outline sections of my spreadsheet. Oddly the borders feature is not present here. You can find it on the Home tab, and also in the Format Cells dialogue (CTRL+1).
Head in here and play around with your options. You are bound to find some tweak here that you will want to use on all of your spreadsheets.
The Developer Tab
You may not even have this tab on yours. It has to be turned on in the settings. This is where you will go to record Macros (if you don’t know what that is don’t worry about it, and nevermind about this tab.
This tab in and of itself is very advanced. If you are a beginner don’t worry about this. If you are experienced and looking for a serious skill upgrade, spend some time in here.
Macros are not even covered in the advanced part of this course.
Just below the ribbon you will find the cell address for the selected cell (wherever you may happen to be on the spreadsheet). Next to that you’ll find the formula bar. Whatever cell you have selected below, this formula bar will reveal that cell’s true contents. The cell may show a number, but if that number is derived from a formula, you will see that formula in the formula bar.
When I am writing complex formulas I often work directly in the formula bar. As you’ll see in the video, it can be re-sized. This makes it easy to develop long formulas.
Make sure you are familiar with everything in the ribbon. Later I am going to show you how to “Freeze Panes.” If you have gone through this section (and the video) and taken notes, you will know exactly where to go to do this, and many other things.