The Absolute

Beginner’s Guide
to Excel
What is Microsoft Excel? Who uses Excel?
Just about everyone, in so many different ways. Here is a short list of jobs that use this spreadsheet
Excel can be intimidating. Where do you even begin? What should you learn first? These are just two program. We’re willing to bet a few will surprise you.
of the questions we set out to answer when creating this comprehensive overview of 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

1
making lists and charts to organizing and tracking information.
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. Here are a few things project managers use Excel for:

Project planning Gantt charts

To plan and subsequently report progress The traditional way to plan and track a project
with the project environment.

Project reports/dashboards Project tracking

Provides a high-level overview of all the most Project managers must track a lot of things,
important information about a project. which is where tracking templates come in
handy. Think timesheets, issue trackers, to do
Other charts lists, and so on.
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.

The Absolute Beginner’s Guide to Excel [Link]

 2 4 6
Freelancers use Excel Teachers use Excel Entrepreneurs use Excel
One of the biggest reasons companies fail is due to poor
There are nearly 6,000 different marketing tools out there today, and Not only do teachers use Excel for planning and organizing, but they also financial planning. So even if you’re not fundraising to start
each one comes with its own set of data that marketers need to analyze use it for number crunching. Here are a few ways teachers use Excel: your company, you’ll still need to use Excel to plan and track
to make better (data-driven) decisions. your financial situation.
• Lesson planning
One thing these tools all have in common, for the most part, is Microsoft • Gradebooks Here are some things entrepreneurs must know how to do
Excel. In each one, you’ll usually notice an “Export” feature that allows • Weekly assignment calendars in Excel:
you to export your data or information into a CSV or xlsx file. • Class schedules
• Class lists • Financial forecasting
So, if a marketer is fluent in Excel, they can analyze data from multiple • Financial modeling
systems in one spreadsheet, enabling themselves to do analysis not • Budgeting and planning
possible in any one standalone tool. • Profit and loss statements

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

Freelancers use Excel

3 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.
7
While there are a ton of freelancer management tools on the market, a
Writers use Excel good, ol’ Excel spreadsheet can often do the trick. People use Excel for their personal lives
Here are a few types of spreadsheets that freelancers use:
Yes, even writers use Excel. Granted, they use it less for number You’d be surprised how many people use Excel in
crunching and more for planning and organizing information. their personal lives.
• Time sheets
If you’re a writer, consider using Excel for: • Invoices I know I was surprised when a former client shared
• Work schedules his personal vacation spreadsheet with me. Turns
• Outlining your novel • Project planning out, people actually plan ahead – sometimes years at
• Planning your editorial calendar • Job quotes a time.
• Outlining your blog posts • Budgets
• Creating a writing schedule • To-do lists Here are some cool Excel use cases for your personal
• Storing an inventory of all of your published content • Client CRMs life:

• Personal finances
• Lists
• Agendas
• Job search

The Absolute Beginner’s Guide to Excel [Link]

 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.

The 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.

The Absolute Beginner’s Guide to Excel [Link]

The Ribbon Tabs
The ribbon is simply the collection of icons at the top of your workbook. Think of the ribbon like a The ribbon is where you find commands. It’s broken down into clickable tabs, where you’ll find
hierarchy: groups of related commands.

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

Tabs span the top of the Ribbon 1 2

Contextual Tabs
Each tab contains groups of commands
Add-ins
There are also contextual tabs – or a tab that
Add-ins extend Microsoft Excel’s capabilities, appears automatically when you do something
and therefore, require a new tab. specific in Excel.

3
Customization
Groups of commands are comprised of buttons and other controls
To make Excel work for you, you can
customize your Ribbon, adding and/or
removing different tabs.

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

The Absolute Beginner’s Guide to Excel [Link]

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

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.

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.
Do something with data
Here’s how you do it:
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. • Click on the row underneath your column headers.
• Click on the “View” tab.
• Click the “Freeze Panes” button.
Apply conditional formatting
Scroll down and across your spreadsheet, and you’ll see that the information you need is always
While formatting is essential for easy reading, it’s not the only benefit of the feature. right there within view!

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

The Absolute Beginner’s Guide to Excel [Link]

Formulas & functions Interpret your data
One of the reasons Excel is so widely used in the business world is because it allows you to do 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. 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 In order to do that, you need to understand formulas and functions. Formulas are the most basic
way to do math in Excel. way to do math in Excel.

Sort data by column

Each formula begins with an “=” sign.
Sorting your data by column/category makes it more presentable. Here’s how:

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

Prepare your data to be sorted

If you want to subtract two numbers, you’d use the “-” sign.
Before you can sort your data, you must make sure that your data is ready to be sorted. Your data is
If you want to multiply two numbers, you’d use the “*” sign. ready to be sorted if the following is true:

• Your data is in tabular format.

And if you want to divide two numbers, you’d use the “/” sign. • There are no blank rows or columns interrupting the data you want to sort.
• Optional: you have a distinct header row for your data.

One of the reasons Excel is so widely used in the business world is because it allows you to do How to do single level sorts
advanced calculations and build business intelligence solutions out of it.
• Select any cell or range inside your data table
In order to do that, you need to understand formulas and functions. Formulas are the most basic • Go to Data > Sort.
way to do math in Excel. • 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 or range inside your data table

• 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.

The Absolute Beginner’s Guide to Excel [Link]

Use filtering
Filtering can be very useful for drilling down into data, finding only records that have specific
Summarize and highlight data with Pivot Tables
relevance to the situation you are investigating.

A Pivot Table is almost like a rubics cube because it allows you to mold your data and look at it in
To filter out duplicates, again, you must get your data ready to be filtered – make sure:
different ways.

• Your data is in tabular format.

• There are no blank rows or columns interrupting the data you want to sort.
• Optional: you have a distinct header row for your data.
• Select the top row of your data. Go to the Data tab, and choose Filter (this will add a row of drop-
How to create PivotTables
down arrows to your headers).
• 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.
Filter for specific words
• Drag fields containing text into the ROWS area as desired.
• 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.
How to remove items from a PivotTable
• Drag any field from the layout area back into the field list.
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 How to modify PivotTables
records you wish to see
• 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.
Filter multiple columns • Go back to the source data and update any cell to a new value.
• You can add filters to multiple columns, drilling down to an ever-decreasing amount of records. • Return to the PivotTable, right click it and choose Refresh

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.

The Absolute Beginner’s Guide to Excel [Link]

Use filtering
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

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

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).
What happens next?
[Link] is an online learning company that helps anyone learn business skills to
Record a macro reach their personal and professional goals. With a [Link] subscription,
members receive personalized courses consisting of high quality and to-the-point
• On the developer tab, click “Record Macro.” video tutorials, transcripts, cheat sheets, exercise files and short quizzes.
• 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. We select globally-recognized experts, who are passionate and brilliant instructors.
• Perform the tasks that you want the macro to do. Members tell us that a [Link] subscription helps them to learn exactly what
• On the Developer tab click “Stop Recording.” they need to know, and in a much more enjoyable and convenient way than is
available elsewhere.

Run the macro

You can run the macro in any one of three ways: Check out our Excel courses here: [Link]
• 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.

The Absolute Beginner’s Guide to Excel [Link]