Learn Excel Online

Everything you need to know to get started with Microsoft Excel.

I remember my first real-world job interview. It was for a marketing coordinator position at a local business.

I was so excited, and I felt quite confident in my abilities… until my interviewer whipped out a projection screen and asked me to open Microsoft Excel so he could watch me complete a few tasks to see how efficient I was in the program.

While I had used Excel before and was familiar with the software, I was by no means efficient in it. Hell, I was a writer – why should I have to be proficient in Excel? To my naive dismay, I was very, very wrong.

Turns out, everyone uses Microsoft Excel. (Who knew?!) In fact, it touches just about every profession out there, at every level.

For the longest time, I tried to escape the intimidating spreadsheet application, until, finally, I accepted the fact that Excel is the price of entry to the professional world.

So, I learned it. And you should too.

I understand why you might be hesitant though.

Excel can be intimidating. Where do you even begin? What should you learn first? These are just two of the questions we set out to answer when creating this comprehensive overview of Microsoft Excel.

What is Microsoft Excel?

Microsoft Excel is the most widely used spreadsheet program in the world. While it’s most popularly known for its ability to make use of heaps of data, Excel has an infinite number of capabilities, from making lists and charts to organizing and tracking information.

Who uses Excel?

Just about everyone, in so many different ways. Here is a short list of jobs that use this spreadsheet program. We’re willing to bet a few will surprise you.

Marketers use Excel

There are nearly 6,000 different marketing tools out there today, and each one comes with its own set of data that marketers need to analyze to make better (data-driven) decisions.

One thing these tools all have in common, for the most part, is Microsoft Excel. In each one, you’ll usually notice an “Export” feature that allows you to export your data or information into a CSV or xlsx file.

So, if a marketer is fluent in Excel, they can analyze data from multiple systems in one spreadsheet, enabling themselves to do analysis not possible in any one standalone tool.

Aside from crunching numbers, marketers also use Excel for planning things like: editorial calendars, budgets and SEO projects.

Project managers use Excel

Why would a modern-day project manager use Excel when they could use one of hundreds of project management tools out there today? Well, there are a few reasons.

For one, Excel is light and versatile, meaning you can efficiently customize Excel templates to fit your project’s specific needs. Then, once you have a template customized how you like it, you can easily reuse it for your next project.

Here are a few things project managers use Excel for:

  • Project planning
    To plan and subsequently report progress with the project environment.
  • Gantt charts
    The traditional way to plan and track a project.
  • Project reports/dashboards
    Provides a high-level overview of all the most important information about a project.
  • Project tracking
    Project managers must track a lot of things, which is where tracking templates come in handy. Think timesheets, issue trackers, to do lists, and so on.
  • Other charts
    Charts help project managers understand how a project is progressing. Some examples include: burndown charts, milestone charts, open vs. closed issue charts and budget charts.

Writers use Excel

Yes, even writers use Excel. Granted, they use it less for number crunching and more for planning and organizing information.

If you’re a writer, consider using Excel for:

Administrative assistants and managers use Excel

Because Excel is the backbone of many offices, office administrative staff and managers must know how to use Excel efficiently.

Teachers use Excel

Not only do teachers use Excel for planning and organizing, but they also use it for number crunching. Here are a few ways teachers use Excel:

  • Lesson planning
  • Gradebooks
  • Weekly assignment calendars
  • Class schedules
  • Class lists

Freelancers use Excel

Freelancers don’t just get to be creative all day long. They also have to stay on top of their business by tracking income, expenses, payments and much more.

While there are a ton of freelancer management tools on the market, a good, ol’ Excel spreadsheet can often do the trick.

Here are a few types of spreadsheets that freelancers use:

  • Time sheets
  • Invoices
  • Work schedules
  • Project planning
  • Job quotes
  • Budgets
  • To-do lists
  • Client CRMs

Entrepreneurs use Excel

One of the biggest reasons companies fail is due to poor financial planning. So even if you’re not fundraising to start your company, you’ll still need to use Excel to plan and track your financial situation.

Here are some things entrepreneurs must know how to do in Excel:

People use Excel for their personal lives

You’d be surprised how many people use Excel in their personal lives.

I know I was surprised when a former client shared his personal vacation spreadsheet with me. Turns out, people actually plan ahead – sometimes years at a time.

Here are some cool Excel use cases for your personal life:

Learn Excel Basics

Learn Excel basics

A Microsoft Office Excel workbook is an xlsx (if you’re using Excel 2007+) file that contains one or more worksheets that you can use to organize different types of related information, according to Microsoft.

Worksheets are comprised of a near infinite number of rows and columns, which together create cells.

Cells are where you store your information, such as text, numbers, pictures, dates, times and formulas.

You’ll notice the rows are numbered while the columns are labeled with letters. An example of a cell label (or reference) then is: A2, H5, etc.

Where to go to get things done in Excel

To get things done in Excel, you’ll need to understand the Ribbon.

Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

Quick Access Toolbar

Excel’s quick access toolbar (see screenshot below) gives you one-click access to the tools you use the most in Excel.

You can customize what appears here by clicking on the arrow icon furthest to the right in the left-hand corner.

Quick Access Toolbar: One-click access to command you use often
Quick Access Toolbar

The Ribbon

The ribbon is simply the collection of icons at the top of your workbook. Think of the ribbon like a hierarchy:

Tabs span the top of the Ribbon
Tabs span the top of the ribbon
Each tab contains groups of commands
Tabs contain groups of commands
Groups of commands are comprised of buttons and other controls
Groups of commands are comprised of buttons and other controls

Let’s dive deeper into each of these items.

Tabs

The ribbon (see screenshot) is where you find commands. It’s broken down into clickable tabs, where you’ll find groups of related commands.

Ribbon Tabs group related commands in Excel
Groups of commands are comprised of buttons and other controls

It’s important to note that your Excel Ribbon may appear different from my Excel Ribbon. There are a few reasons this could be.

1. Add-ins:
Add-ins extend Microsoft Excel’s capabilities, and therefore, require a new tab.

2. Contextual Tabs:
There are also contextual tabs – or a tab that appears automatically when you do something specific in Excel.

A contextual tab is a special type of tab that appears only when you select a certain object, such as a chart or table. Contextual tabs contain commands related to whatever object you are currently working on.

Contextual tab example

3. Customization:
To make Excel work for you, you can customize your Ribbon, adding and/or removing different tabs.

Command Groups

Groups of commands are comprised of buttons and other controls.
Groups of commands are comprised of buttons and other controls

Depending on which tab you click – home, insert, page layout, etc. – different command groups will appear below.

Most common, essential Microsoft Excel tasks you should know how to do

Using spreadsheets is usually a three-step process:


Enter data


Do something with
the data


Interpret that data

And occasionally, there is a step four:


Automating the process

Do something with data

Before doing something with your data, try stylizing your spreadsheet so it’s easier to navigate. Here are a few things you should know how to do in Excel.

Apply conditional formatting

While formatting is essential for easy reading, it’s not the only benefit of the feature.

Ever heard of conditional formatting? Conditional formatting turns things up a notch by styling cells based on what’s in them.

For example, in the example above, we have a spreadsheet with a list of distilleries and the dates they were founded. If I’m interested in visiting the oldest distilleries, I can use the conditional formatting feature to highlight the oldest three in my sheet.

Note: If you don’t see the conditional formatting command in your Ribbon, then visit the top, left-hand corner of your screen. Click on the downward arrow button, and click “More Commands.” Find the “Conditional Formatting” option in the scrolly list, and click the arrow to add it to your Ribbon or Quick Access Toolbar. Then you can use the command from there.

Here’s how I would use the conditional formatting feature to highlight the oldest distilleries.

Auto
  • HD
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

If Excel doesn’t have the option you want already, you can build your own style.

It’s important to note that the style you choose will be applied to every selected cell, so pay attention to the cells you’re selecting because it may affect how the style appears.

Freeze panes

We actually touched on this in a former blog post. Here’s an excerpt:

There’s nothing worse than scrolling through a huge spreadsheet that requires you to continuously go back up to the top to see what your column headers are.

Fortunately, you can make your column headers and your row numbers stay right where they are – meaning you can always see them, no matter how far down the spreadsheet you go. You can do this by using Excel’s handy “freeze panes” feature.

Here’s how you do it:

  • Click on the row underneath your column headers.
  • Click on the “View” tab.
  • Click the “Freeze Panes” button.

Freeze panes example

Scroll down and across your spreadsheet, and you’ll see that the information you need is always right there within view!

Formulas & functions

One of the reasons Excel is so widely used in the business world is because it allows you to do advanced calculations and build business intelligence solutions out of it.

In order to do that, you need to understand formulas and functions. Formulas are the most basic way to do math in Excel.

= Each formula begins with an “=” sign.
How you begin each formula

+ If you want to add two numbers, you’d use the “+” sign.
Add numbers

- If you want to subtract two numbers, you’d use the “-” sign.
Subtract numbers

* If you want to multiply two numbers, you’d use the “*” sign.
Multiply numbers

/ And if you want to divide two numbers, you’d use the “/” sign.
Divide numbers

Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

You can do what I did above – input the exact numbers – but that isn’t the optimal solution. Why? Because then you can’t copy and paste that formula into the remaining cells because the formula is hard-coded with numbers.

Here’s what I should’ve done instead:

Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

Notice how I input the cell references instead of the actual numbers.

What if you want to do more complex math though? How would you do it?

Well, you’d utilize functions. Functions, which are categorized by functionality, are just predefined formulas. Here are some of the most common ones. You can find the full list here and here.

  • SUM function
    Adds values. You can add individual values, cell references or ranges, or a mix of all three.
  • AVERAGE function
    Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
  • MAX function
    Returns the largest value in a set of values.
  • MIN function
    Returns the smallest number in a set of values.
  • COUNT function
    Counts the number of cells that contain numbers.

Interpret your data

Now that you have your data, you have to interpret it.

The thing is: it’s difficult to make sense of messy data. So you’ll need to visualize it first.

Here are a few ways you can make your data look better.

Sort data by column

What if you want to sort your data by column/category to make it more presentable?

Well, you can do that. Here’s how.

Prepare your data to be sorted

Before you can sort your data, you must make sure that your data is ready to be sorted. Your data is ready to be sorted if the following is true:

  • Your data is in tabular format.
  • There are no blank rows or columns interrupting the data you want to sort.
  • You have a distinct header row for your data. (Optional but highly recommended.)

Preparing your data to be sorted

How to do single level sorts

  • Select any cell inside your data table (or the entire range you want to sort).
  • Go to Data > Sort.
  • Configure the way you’d like it sorted, then click OK.

How to do multiple level sorts

Multi-level sorts are good for large data sets because you’ll likely want to sort your data by one column and then by another.

  • Select any cell inside your data table (or the entire range you want to sort),
  • Go to Data > Sort.
  • Configure the primary sort the way you’d like to see it.
  • Click Add Level.
  • Configure the secondary sort the way you’d like to sort the ties.
  • You can continue to add as many sorting levels as you need.

Use filtering

filtering

Filtering can be very useful for drilling down into data, finding only records that have specific relevance to the situation you are investigating.

To filter out duplicates, again, you must get your data ready to be filtered – make sure:

  • Your data is in tabular format.
  • There are no blank rows or columns interrupting the data you want to sort.
  • You have a distinct header row for your data. (Optional but highly recommended.)
  • Select the top row of your data. Go to the Data tab, and choose Filter (this will add a row of drop-down arrows to your headers).

Filter for specific words

  • Click the drop-down arrow of the column you wish to filter.
  • In the Search box, type the term you are looking for, then click OK.

Filter for specific dates

  • Click the drop-down arrow of the column you wish to filter.
  • Uncheck the Select all box to clear unwanted dates, then use the checkboxes to drill down to the records you wish to see.

Filter multiple columns

  • You can add filters to multiple columns, drilling down to an ever-decreasing amount of records.

Clear filters in one of three ways

  • Click the filter icons on the column headers, then click the “Clear filter from” button in the menu.
  • Go to the Data tab and click the Clear icon on the Sort & Filter group.
  • Go to the Data tab and turn off filtering by clicking the Filter button, then add the filter back again.

Summarize and highlight data with Pivot Tables

A Pivot Table is almost like a rubics cube because it allows you to mold your data and look at it in different ways.

How to create PivotTables

Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc
  • Select any cell in the data range.
  • Go to the Insert tab, choose PivotTable and place it on a new worksheet.
  • Drag a numeric field into the VALUES area.
  • Drag fields containing text into the ROWS area as desired.

How to remove items from a PivotTable

  • Drag any field from the layout area back into the field list.

How to modify PivotTables

  • Drag a field from ROWS to COLUMNS.
  • Drag a field from COLUMNS to ROWS.
  • Click the arrow beside any field in the VALUES area, choose Value Field Settings, and choose Average.
  • Go back to the source data and update any cell to a new value.
  • Return to the PivotTable, right click it and choose Refresh.

Automate with Macros and Scripts

If you have tasks you need to do over and over again, you should use Macros and Scripts to automate this process.

How to record Macros

Add the Developer tab to your ribbon (if it is not on your ribbon)

  • Right click any command on the ribbon and choose “Customize Ribbon.”
  • Click the box next to Developer tab in the list on the right.
  • Click OK to return to Excel and activate the Developer tab.

Prepare to record

  • Practice the steps you will go through, as the macro recorder will record your mistakes!
  • Be aware that the macro will do EXACTLY the same thing EVERY time it runs (this can be modified, but it involves learning to program).

Record a macro

  • On the developer tab, click “Record Macro.”
  • Give the macro a name (no spaces) and add a capital letter in the Shortcut box (like R) if desired.
  • Choose to place the macro in ThisWorkbook.
  • Perform the tasks that you want the macro to do.
  • On the Developer tab click “Stop Recording.”

Run the macro

You can run the macro in any one of three ways:

  • Press CTRL+SHIFT+R (or whatever letter you chose) to run the keyboard shortcut.
  • Press Alt+F8 to run the macro from the macro dialog.
  • Go to the Developer tab and choose to Insert a Form Control Button on the worksheet (it will prompt you to connect a macro to it). You can then click the button to run the macro.

Further Resources

This was just a high-level overview. But with Excel, the possibilities are endless so don’t stop here. We’ve gathered some more Excel resources to keep you moving.