How To Use Excel:

A Beginner’s Guide To Getting Started

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel is a powerful application—but it can also be very intimidating.

That’s why we’ve put together this beginner’s guide to getting started with Excel.

It will take you from the very beginning (opening a spreadsheet), through entering and
working with data, and finish with saving and sharing.

It’s everything you need to know to get started with Excel.

If you want to tag along as you read, please download the free sample Excel workbook here.

Table of Contents
 1: Opening a spreadsheet
 2: Working with the Ribbon
 3: Managing your worksheets
 4: Entering data
 5: Basic calculations
 6: Unlocking the power of functions
 7: Saving and sharing your work
 8: Welcome to Excel

Opening an Excel spreadsheet

When you first open Excel (by double-clicking the icon or selecting it from the Start menu),
the application will ask what you want to do.
If you want to open a new Excel spreadsheet, click Blank workbook.

To open an existing spreadsheet (like the example workbook you just downloaded),
click Open Other Workbooks in the lower-left corner, then click Browse on the left side of
the resulting window.

Then use the file explorer to find the Excel workbook you’re looking for, select it, and
click Open.

Workbooks vs. spreadsheets

There’s something we should clear up before we move on.

A workbook is an Excel file. It usually has a file extension of .XLSX (if you’re using an
older version of Excel, it could be .XLS).

A spreadsheet is a single sheet inside a workbook. There can be many sheets inside of a
workbook, and they’re accessed via the tabs at the bottom of the screen.

A spreadsheet (a.k.a. a sheet/tab) contains all the cells you can see and use in the >1 million
rows >16,000 columns.
Working with the Ribbon
The Ribbon is the central control
panel of Excel. You can do just
about everything you need to
directly from the Ribbon.

Where is this powerful tool? At the

top of the window:

There are a number of tabs,

including the File tab, Home
tab, Insert tab, Data tab, Review
tab, and a few others. Each tab contains different buttons.

Try clicking on a few different tabs to see which buttons appear below them.

There’s also a very useful search bar

in the Ribbon. It says Tell me what
you want to do. Just type in what
you’re looking for, and Excel will
help you find it.

Most of the time, you’ll be in

the Home tab of the Ribbon.
But Formulas and Data are also very
useful (we’ll be talking about
formulas shortly).

Pro tip: Ribbon sections

In addition to tabs, the Ribbon also

has some smaller sections. And when
you’re looking for something specific,
those sections can help you find it.

For example, if you’re looking

for sorting and filtering options, you
don’t want to hover over dozens of
buttons finding out what they do.

Instead, skim through the section

names until you find what you’re
looking for:
Managing your sheets
As we saw, workbooks can contain
multiple sheets.

You can manage those sheets with the

sheet tabs near the bottom of the screen.
Click a tab to open that particular
worksheet.

If you’re using our example workbook,

you’ll see two sheets,
called Welcome and Thank You:

To add a new worksheet, click

the + (plus) button at the end of the list of sheets.

You can also reorder the sheets in your

workbook by dragging them to a new
location.

And if you right-click a worksheet tab,

you’ll get a number of options:

For now, don’t worry too much about

these options. Rename and Delete are
useful, but the rest needn’t concern you.

Entering data
Now it’s time to enter some data!

And while entering data is one of the most central and important things you can do in Excel,
it’s almost effortless.

Just click into a blank cell and start typing.

Go ahead, try it! Type your name, birthday, and your favorite number into some blank cells.
You can also copy (Ctrl + C), cut (Ctrl + X), and paste (Ctrl + V) any data you’d like (or
read our full guide on copying and pasting here).

Try copying and pasting the data from multiple cells inthe example spreadsheet into another
column.
 You can also copy data from
other programs into Excel.
Try copying this list of numbers
and pasting it into your sheet:

 17
 24
 9
 00
 3
 12

That’s all we’re going to cover

for basic data entry. Just know
that there are lots of other ways to get data into your spreadsheets if you need them.

Basic calculations
Now that we’ve seen how to get some basic data into our spreadsheet, we’re going to do
some things with it.

Running basic calculations in Excel is easy. First, we’ll look at how to add two numbers.

Important: start calculations with = (equals)

When you’re running a calculation (or a formula, which we’ll discuss next), the first thing
you need to type is an equals sign. This tells Excel to get ready to run some sort of
calculation.

So when you see something like =MEDIAN(A2:A51), make sure you type it exactly as it
is—including the equals sign.

Let’s add 3 and 4. Type the following formula in a blank cell:

=3+4

Then hit Enter.

 When you hit Enter, Excel evaluates
your equation and displays the
result, 7.
But if you look above at
the formula bar, you’ll still see the
original formula.

That’s a useful thing to keep in

mind, in case you forget what you
typed originally.

You can also edit a cell in the

formula bar. Click on any cell, then
click into the formula bar and start typing.
Performing subtraction, multiplication, and division is just as easy. Try these formulas:

 =4-6
 =2*5
 =-10/3

What we’re going to cover next is one of the most important things in Excel. We’re giving it
a very basic overview here, but feel free to read our post on cell references to get the details.
Now let’s try something different. Open up the first sheet in the example workbook, click
into cell C1, and type the following:

=A1+B1

Hit Enter.

You should get 82, the sum of the numbers in cells A1 and B1.

Now, change one of the numbers in A1

or B1 and watch what happens:

Because you’re adding A1 and B1,

Excel automatically updates the total
when you change the values in one of
those cells.

Try doing different types of arithmetic

on the other numbers in columns A and
B using this method.

Unlocking the power of functions

Excel’s greatest power lies in functions. These let you run complex calculations with a few
keypresses.
We’ll barely scratch the surface of functions here. Check out our other blog posts to see some
of the great things you can do with functions!
Many formulas take sets of numbers and give you information about them.

For example, the AVERAGE function gives you the average of a set of numbers. Let’s try
using it.

Click into an empty cell and type the following formula:

=AVERAGE(A1:A4)

Then hit Enter.

The resulting number, 0.25, is the average of the numbers in cells A1, A2, A3, and A4.

Cell range notation

In the formula above, we used “A1:A4” to tell Excel to look at all the cells between A1 and
A4, including both of those cells. You can read it as “A1 through A4.”

You can also use this to include numbers in different columns. “A5:C7” includes A5, A6, A7,
B5, B6, B7, C5, C6, and C7.

There are also functions that work on text.

Let’s try the CONCATENATE function!

Click into cell C5 and type this formula:

=CONCATENATE(A5, ” “, B5)

Then hit Enter.

You’ll see the message “Welcome to Spreadsheeto” in the cell.

How did this happen? CONCATENATE takes cells with text in them and puts them together.

We put the contents of A5 and B5 together. But because we also needed a space between “to”
and “Spreadsheeto,” we included a third argument: the space between two quotes.

Remember that you can mix cell references (like “A5″) and typed values (like ” “) in
formulas.
Excel has dozens of useful functions. To find the function that will solve a particular
problem, head to the Formulas tab and click on one of the icons:

Scroll through the list of available functions, and select the one you want (you may have to
look around for a while).

Then Excel will help you get the right numbers in the right places:

If you start typing a formula, starting with the equals sign, Excel will help you by showing
you some possible functions that you might be looking for:
And finally, once you’ve typed the name of a formula and the opening parenthesis, Excel will
tell you which arguments need to go where:

If you’ve never used a function before, it

might be difficult to interpret Excel’s
reminders. But once you get more
experience, it’ll become clear.

This is a tiny preview of how functions

work and what they can do. It should be
enough to get you going on the tasks you
need to accomplish right away.

Saving and sharing your work

After you’ve done a bunch of work with your spreadsheet, you’re going to want to save your
changes.

Hit Ctrl + S to save. If you haven’t yet saved your spreadsheet, you’ll be asked where you
want to save it and what you want to call it.

You can also click the Save button in the Quick Access Toolbar:
 It’s a good idea to get into the
habit of saving often. Trying
to recover unsaved changes is a
pain!
The easiest way to share your
spreadsheets is via OneDrive.

Click the Share button in the

top-right corner of the window,
and Excel will walk you through
sharing your document.

You can also save your document and email it, or use any other cloud service to share it with
others.

That’s it – Now what?

This was how to use Excel.

Or… at least a small fraction of it.

Microsoft Excel can be intimidating, but once you get the basics down, it’s easier to learn the
more advanced functions.

This was your introduction to “the basics”. So, if you’re not ready to get some advanced

Excel knowledge, go ahead and practice with some of the existing data at the office

If you’re ready to take your next steps, go ahead and enroll in my 30-minute free online
course where you learn: IF, SUMIF, VLOOKUP, and data cleaning.

These are some of the most important topics of Excel

Other resources
Now, you can’t excel at Excel without mastering some of the lookup functions
like VLOOKUP and the new XLOOKUP.

But also, you don’t wanna miss out on pivot tables. You can use these to transform your

Microsoft Excel data into insightful reports in just a few clicks

Or if you’re into automating Excel spreadsheet formatting, go ahead and read my guide
to conditional formatting here.
 "By far this is the best training on the
internet"
I've released a free Excel course that I think fits you perfectly.
It's 3x10-minute lessons and has free exercises.
You learn all about the functions SUM, AVERAGE, COUNT, and much more!
+100,000 students have already signed up. Now it's your turn!
Microsoft

Support
Microsoft 365 Office Products Devices More

Basic tasks in Excel

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016

You're welcome to try Microsoft 365 for free

Unlock now

Excel is an incredibly powerful tool for getting meaning out of vast amounts of data.
But it also works really well for simple calculations and tracking almost any kind of
information. The key for unlocking all that potential is the grid of cells. Cells can
contain numbers, text, or formulas. You put data in your cells and group them in
rows and columns. That allows you to add up your data, sort and filter it, put it in
tables, and build great-looking charts. Let’s go through the basic steps to get you
started.

Create a new workbook

Excel documents are called workbooks. Each workbook has sheets, typically called spreadsheets.
You can add as many sheets as you want to a workbook, or you can create new workbooks to
keep your data separate.

1. Click File, and then click New.

2. Under New, click the Blank workbook.

Enter your data

1. Click an empty cell.
 For example, cell A1 on a new sheet. Cells are referenced by their location in the row and
column on the sheet, so cell A1 is in the first row of column A.

2. Type text or a number in the cell.

3. Press Enter or Tab to move to the next cell.

Apply cell borders

1. Select the cell or range of cells that you want to add a border to.

2. On the Home tab, in the Font group, click the arrow next to Borders, and then click the
border style that you want.

For more information, see Apply or remove cell borders on a worksheet .

Apply cell shading

1. Select the cell or range of cells that you want to apply cell shading to.

2. On the Home tab, in the Font group, choose the arrow next to Fill Color, and then
under Theme Colors or Standard Colors, select the color that you want.

For more information about how to apply formatting to a worksheet, see Format a worksheet.

Use AutoSum to add your data

When you’ve entered numbers in your sheet, you might want to add them up. A fast way to do
that is by using AutoSum.

1. Select the cell to the right or below the numbers you want to add.

2. Click the Home tab, and then click AutoSum in the Editing group.

AutoSum adds up the numbers and shows the result in the cell you selected.

For more information, see Use AutoSum to sum numbers

Create a simple formula
Adding numbers is just one of the things you can do, but Excel can do other math as well. Try
some simple formulas to add, subtract, multiply, or divide your numbers.

1. Pick a cell, and then type an equal sign (=).

That tells Excel that this cell will contain a formula.

2. Type a combination of numbers and calculation operators, like the plus sign (+) for addition,
the minus sign (-) for subtraction, the asterisk (*) for multiplication, or the forward slash (/)
for division.

For example, enter =2+4, =4-2, =2*4, or =4/2.

3. Press Enter.

This runs the calculation.

You can also press Ctrl+Enter if you want the cursor to stay on the active cell.

For more information, see Create a simple formula.

Apply a number format

To distinguish between different types of numbers, add a format, like currency, percentages, or
dates.

1. Select the cells that have numbers you want to format.

2. Click the Home tab, and then click the arrow in the General box.

3. Pick a number format.

 If you don’t see the number format you’re looking for, click More Number Formats. For
more information, see Available number formats.

Put your data in a table

A simple way to access Excel’s power is to put your data in a table. That lets you quickly filter or
sort your data.

1. Select your data by clicking the first cell and dragging to the last cell in your data.

To use the keyboard, hold down Shift while you press the arrow keys to select your data.

2. Click the Quick Analysis button in the bottom-right corner of the selection.
3. Click Tables, move your cursor to the Table button to preview your data, and then click
the Table button.

4. Click the arrow in the table header of a column.

5. To filter the data, clear the Select All check box, and then select the data you want to show
in your table.
6. To sort the data, click Sort A to Z or Sort Z to A.

7. Click OK.

For more information, see Create or delete an Excel table

Show totals for your numbers using Quick Analysis
The Quick Analysis tool (available in Excel 2016 and Excel 2013 only) let you total your numbers
quickly. Whether it’s a sum, average, or count you want, Excel shows the calculation results right
below or next to your numbers.

1. Select the cells that contain numbers you want to add or count.

2. Click the Quick Analysis button in the bottom-right corner of the selection.

3. Click Totals, move your cursor across the buttons to see the calculation results for your data,
and then click the button to apply the totals.

Add meaning to your data using Quick Analysis

Conditional formatting or sparklines can highlight your most important data or show data trends.
Use the Quick Analysis tool (available in Excel 2016 and Excel 2013 only) for a Live Preview to try
it out.

1. Select the data you want to examine more closely.

2. Click the Quick Analysis button in the bottom-right corner of the selection.

3. Explore the options on the Formatting and Sparklines tabs to see how they affect your
data.

For example, pick a color scale in the Formatting gallery to differentiate high, medium, and
low temperatures.
 4. When you like what you see, click that option.

Learn more about how to analyze trends in data using sparklines.

Show your data in a chart using Quick Analysis

The Quick Analysis tool (available in Excel 2016 and Excel 2013 only) recommends the right chart
for your data and gives you a visual presentation in just a few clicks.

1. Select the cells that contain the data you want to show in a chart.

2. Click the Quick Analysis button in the bottom-right corner of the selection.

3. Click the Charts tab, move across the recommended charts to see which one looks best for
your data, and then click the one that you want.

Note: Excel shows different charts in this gallery, depending on what’s recommended for
your data.

Learn about other ways to create a chart.

Sort your data

To quickly sort your data

1. Select a range of data, such as A1:L5 (multiple rows and columns) or C1:C80 (a single
column). The range can include titles that you created to identify columns or rows.

2. Select a single cell in the column on which you want to sort.

3. Click to perform an ascending sort (A to Z or smallest number to largest).

4. Click to perform a descending sort (Z to A or largest number to smallest).

To sort by specific criteria

1. Select a single cell anywhere in the range that you want to sort.

2. On the Data tab, in the Sort & Filter group, choose Sort.

3. The Sort dialog box appears.

4. In the Sort by list, select the first column on which you want to sort.
 5. In the Sort On list, select either Values, Cell Color, Font Color, or Cell Icon.

6. In the Order list, select the order that you want to apply to the sort operation —
alphabetically or numerically ascending or descending (that is, A to Z or Z to A for text or
lower to higher or higher to lower for numbers).

For more information about how to sort data, see Sort data in a range or table .

Filter your data

1. Select the data that you want to filter.

2. On the Data tab, in the Sort & Filter group, click Filter.

3. Click the arrow in the column header to display a list in which you can make filter
choices.

4. To select by values, in the list, clear the (Select All) check box. This removes the check marks
from all the check boxes. Then, select only the values you want to see, and click OK to see
the results.

For more information about how to filter data, see Filter data in a range or table.

Save your work

1. Click the Save button on the Quick Access Toolbar, or press Ctrl+S.

If you’ve saved your work before, you’re done.

2. If this is the first time you've save this file:

a. Under Save As, pick where to save your workbook, and then browse to a folder.

b. In the File name box, enter a name for your workbook.

c. Click Save.

Print your work

1. Click File, and then click Print, or press Ctrl+P.

2. Preview the pages by clicking the Next Page and Previous Page arrows.
 The preview window displays the pages in black and white or in color, depending on your
printer settings.

If you don’t like how your pages will be printed, you can change page margins or add page
breaks.

3. Click Print.

Activate and use an add-in

1. On the File tab, choose Options, and then choose the Add-Ins category.

2. Near the bottom of the Excel Options dialog box, make sure that Excel Add-ins is selected
in the Manage box, and then click Go.

3. In the Add-Ins dialog box, select the check boxes the add-ins that you want to use, and
then click OK.

If Excel displays a message that states it can't run this add-in and prompts you to install it,
click Yes to install the add-ins.

For more information about how to use add-ins, see Add or remove add-ins.

Find and apply a template

Excel allows you to apply built-in templates, to apply your own custom templates, and to search
from a variety of templates on [Link]. [Link] provides a wide selection of popular Excel
templates, including budgets.

For more information about how to find and apply templates, see Download free, pre-built
templates.
Excel Definition
The ultimate software tool for financial analysis

Over 1.8 million professionals use CFI to learn accounting, financial analysis,
modeling and more. Start with a free account to explore 20+ always-free
courses and hundreds of finance templates and cheat sheets.

Microsoft Excel Definition

Excel definition: a software program created by Microsoft that uses
spreadsheets to organize numbers and data with formulas and functions.
Excel analysis is ubiquitous around the world and used by businesses of all
sizes to perform financial analysis.

Check out CFI’s free Excel Crash Course here!

What is Excel used for?

Excel is typically used to organize data and perform financial analysis. It is

used across all business functions and at companies from small to large.

The main uses of Excel include:

  Data entry
 Data management
 Accounting
 Financial analysis
 Charting and graphing
 Programming
 Time management
 Task management
 Financial modeling
 Customer relationship management (CRM)
 Almost anything that needs to be organized!

Data functions, formulas, and shortcuts

The Excel software program includes many functions, formulas, and

shortcuts that can be used to enhance its functionality.

We have defined the key functions and formulas below in our Excel guide:

 Free Guide: Excel shortcuts, functions and definitions

Financial and accounting uses

Excel is used extensively in finance and accounting functions. In fact, many
organizations run their entire budgeting, forecasting, and
accounting functions entirely out of Excel spreadsheets.

While Excel is defined as a “data” management tool, the data that is most
commonly managed is financial. At CFI, we would define Excel as the
ultimate financial software. While there are other pieces of financial
software that are tailored toward performing specific tasks, the strongest
point about Excel is its robustness and openness. Excel models are as
powerful as the analyst wishes them to be.

Accountants, investment bankers, analysts, and people in all types

of financial career paths rely on excel to perform their daily job functions.

Additional Resources

Thank you for reading CFI’s guide to Microsoft Excel. To keep learning and
developing your career, these additional CFI resources will be helpful:

Free Excel Fundamentals Course

 Free Excel Course

 Keyboard Shortcuts
 Advanced Formulas in Excel
 Financial Modeling
 See all Excel resources
 New to Excel? Here's Super Easy Tricks to
Get You Started
SECTIONS

1Understanding Workbooks and Worksheets

2Entering and Formatting Data

3Creating, Sorting, and Filtering Tables

+Show 4 more...

OTHER SECTIONS

Questions & Answers

Video

References

Article Summary

Written by Nicole Levine, MFA

Last Updated: October 29, 2022 Fact Checked

Are you new to Microsoft Excel and need to work on a spreadsheet? Excel is so overrun with
useful and complicated features that it might seem impossible for a beginner to learn. But
don't worry—once you learn a few basic tricks, you'll be entering, manipulating, calculating,
and graphing data in no time! This wikiHow tutorial will introduce you to the most important
features and functions you'll need to know when starting out with Excel, from entering and
sorting basic data to writing your first formulas.

Things You Should Know

 Use Quick Analysis in Excel to perform quick calculations and create helpful graphs without any
prior Excel knowledge.

 Adding your data to a table makes it easy to sort and filter data by your preferred criteria.

 Even if you're not a math person, you can use basic Excel math functions to add, subtract, find
averages and more in seconds.
Understanding Workbooks and Worksheets

1
Create or open a [Link] people refer to "Excel files," they are referring
to workbooks, which are files that contain one or more sheets of data on individual tabs. Each tab is
called a worksheet or spreadsheet, both of which are used interchangeably. When you open Excel,
you'll be prompted to open or create a workbook.

 To start from scratch, click Blank workbook. Otherwise, you can open an existing
workbook or create a new one from one of Excel's helpful templates, such as those designed
for budgeting.

2
Explore the [Link] you create a new blank workbook, you'll have a single worksheet
called Sheet1 (you'll see that on the tab at the bottom) that contains a grid for your data. Worksheets
are made of individual cells that are organized into columns and rows.

 Columns are vertical and labeled with letters, which appear above each column.
  Rows are horizontal and are labeled by numbers, which you'll see running along the left side
of the worksheet.
 Every cell has an address which contains its column letter and row number. For example, the
top-left cell in your worksheet's address is A1 because it's in column A, row 1.
 A workbook can have multiple worksheets, all containing different sets of data. Each
worksheet in your workbook has a name—you can rename a worksheet by right-clicking its
tab and selecting Rename.
 To add another worksheet, just click the + next to the worksheet tab(s).

3
Save your [Link] you save your workbook once, Excel will automatically save any
changes you make by default.[1] This prevents you from accidentally losing data.

 Click the File menu and select Save As.

 Choose a location to save the file, such as on your computer or in OneDrive.
 Type a name for your workbook. All workbooks will automatically inherit the the .XLSX
file extension.
 Click Save.

Entering and Formatting Data

1
Click a cell to select [Link] you click a cell, it will highlight to indicate that it's selected.

 When you type something into a cell, the input text is called a value. Entering data into
Excel is as simple as typing values into each cell.
 When entering data, the first row of your worksheet (e.g., A1, B1, C1) is typically used as
headers for each column. This is helpful when creating graphs or tables which require labels.
 For example, if you're adding a list of dates in column A, you might click cell A1
and type D a t e into the cell as the column header.

2
Type a word or number into the [Link] you're typing, you'll see the letters and/or numbers
appear in the cell, as well as in the formula bar at the top of the worksheet.

 When you start practicing more advanced Excel features like creating formulas, this bar will
come in handy.
 You can also copy and paste text from other applications into your worksheet, tables from
PDFs and the web.
3
Press ↵ Enter or ⏎ Return .This enters the data into the cell and moves to the next cell in the
column.

4
Automatically fill columns based on existing [Link]'s say you want to make a list of
consecutive dates or numbers. Or what if you want to fill a column with many of the same values that
follow a pattern? As long as Excel can recognize some sort of pattern in your data, such as a particular
order, you can use Autofill to automatically populate data into the rest of your column. Here's a trick
to see it in action.

 In a blank column, type 1 into the first cell, 2 into the second cell, and then 3 into the third
cell.
 Hover your mouse cursor over the bottom-right corner of the last cell in your series—it will
turn to a crosshair.
 Click and drag the crosshair down the column, then release the mouse button once you've
gone down as far as you like. By default, this will fill the remaining cells with the value of
the selected cell—at this point, you'll probably have something like 1, 2, 3, 3, 3, 3, 3, 3.
  Click the small icon at the bottom-right corner of the filled data to open AutoFill options,
and select Fill Series to automatically detect the series or pattern. Now you'll have a list of
consecutive numbers. Try this cool feature out with different patterns!
 Once you get the hang of AutoFill, you'll have to try flash fill, which you can use to join two
columns of data into a single merged column.

5
Adjust the column sizes so you can see all of the [Link] typing long values
into a cell hides the value and displays hash symbols ### instead of what you've typed. If you want to
be able to see everything, you can snap the cell contents to the width of the widest cell. For example,
let's say we have some long values in column B:

 To expand the contents of column B, hover the cursor over the dividing line between the B
and C at the top of the worksheet—once your cursor is right on the line, it will turn to two
arrows pointing in either direction.[2]
 Click and drag the separator until the column is wide enough to accommodate your data, or
just double-click the separator to instantly snap the column to the size of the widest value.

6
Wrap text in a [Link] your longer values are now awkwardly long, you can enable text wrapping
in one or more cells. Just click a cell (or drag the mouse to select multiple cells), click the Home tab,
and then click Wrap Text on the toolbar.

7
Edit a cell [Link] you need to make a change to a cell, you can double-click the cell to activate
the cursor, and then make any changes you need. When you're finished, just
press Enter or Return again.

 To delete the contents of a cell, click the cell once and press delete on your keyboard.

8
Apply styles to your [Link] you want to highlight certain values with color so they
stand out or just want to make your data look pretty, changing the colors of cells and their containing
values is easy—especially if you're used to Microsoft Word:
  Select a cell, column, row, or multiple cells at once.
 On the Home tab, click Cell Styles if you'd like to quickly apply quick color styles.
 If you'd rather use more custom options, right-click the selected cell(s) and select Format
Cells. Then, use the colors on the Fill tab to customize the cell's background, or the colors
on the Font tab for value colors.

9
Apply number formatting to cells containing [Link] you have data that contains
numbers such as prices, measurements, dates, or times, you can apply number formatting to the data
so it will display consistently.[3] By default, the number format is General, which means numbers
display exactly as you type them.

 Select the cell you want to format. If you're working with an entire column or row, you can
just click the column letter or row number to select the whole thing.
 On the Home tab, click the drop-down menu at the top-center—it'll say General by default,
unless you selected cells that Excel recognizes as a different type of number
like Currency or Time.
 Choose one of the formatting options in the list, such as Short Date or Percentage, or
click More Number Formats at the bottom to expand all options (we recommend this!).
 If you selected More Number Formats, the Format Cells dialog will expand to
the Number tab, where you'll see several categories for number types.
 Select a category, such as Currency if working with money, or Date if working with dates.
Then, choose your preferences, such as a currency symbol and/or decimal places.
 Click OK to apply your formatting.

Creating, Sorting, and Filtering Tables

1
Select all of the data you've entered so [Link] your data to a table is the easiest way
to work with and analyze data.[4] Start by highlighting the values you've entered so far, including
your column headers. Tables also make it easy to sort and filter your data based on values.

 Tables traditionally apply different or alternating colors to every other row for easy viewing.
Many table options also add borders between cells and/or columns and rows.

2
Click Format as Table .You'll see this at the top-center part of the Home tab.[5]
3
Select a table [Link] any of Excel's default table styles to get started. You'll see a small
window titled "Create Table" once selected.

 Once you get the hang of tables, you can return here to customize your table further by
selecting New Table Style.

4
Make sure "My table has headers" is selected and click OK .This tells Excel to turn
your column headers into drop-down menus that you can easily sort and filter. Once you click OK,
you'll see that your data now has a color scheme and drop-down menus.
5
Click the drop-down menu at the top of a [Link] you'll see options for sorting
that column, as well as several options for filtering all of your data based on its values.

6
Choose which data to display based on values in this [Link] simplest way to
do this is to uncheck the values you don't want to display—if you uncheck a particular date, for
example, you'll prevent rows that contain the selected date in from appearing in your data. You can
also use Text Filters or Number Filters, depending on the type of data in the column:

 If you chose a numerical column, select Number Filters, then choose an option
like Greater Than… or Does Not Equal to be extra specific about which values to hide.
 For text columns, you can choose Text Filters, where you can specify things like Begins
with or Contains.
 You can also filter by cell color.
7
Click OK .Your data is now filtered based on your selections. You'll also see a small funnel icon in
the drop-down menu, which indicates that the data is filtering out certain values.

 To unfilter your data, click the funnel icon, click Clear filter from (column name), and
then click OK.
 You can also filter columns that aren't in tables. Just select a column and click Filter on
the Data tab to add a drop-down to that column.

8
Sort your data in ascending or descending [Link] the drop-down arrow at the top
of a column to view sorting options—these allow you to sort all of your data in order based on the
current column.

 If you're working with numbers, click Smallest to Largest to sort in ascending order,
or Largest to Smallest for descending order.[6]
 If you're working with text values, Sort A to Z will sort in ascending order, while Sort Z to
A will sort in reverse.
  When it comes to sorting dates and times, Sort Oldest to Newest will sort with the earliest
date at the top and the oldest date at the bottom, and Newest to Oldest displays the dates in
descending order.
 When you sort a column, all other columns in the table adjust based on the sort.

Doing Quick Analysis

1
Select the data in your [Link]'s Quick Analysis feature is the easiest way to
perform basic calculations (including totals, averages, and counts) and create meaningful tables or
graphs without the need for advanced Excel knowledge.[7] Use your mouse to select your data
(including your column headers) to get started.

2
Click the Quick Analysis [Link] is the small icon that pops up at the bottom-right corner
of your selection. It looks like a window with some colored lines.
3
Select an analysis [Link]'ll see several tabs running along the top of the window, each of
which gives you different option for visualizing your data:

 For math calculations, click the Totals tab, where you can
select Sum, Average, Count, %Total, or Running Total. You'll be able to choose whether
to display the results at the bottom of each column or to the right.
 To create a chart, click the Charts tab, then select a chart to visualize your data. Before you
settle on a chart, just hover the cursor over each option to see a preview.
 To add quick chart data to individual cells, click the Sparklines tab and choose a format.
Again, you can hover the cursor over each option to see a preview.
 To instantly apply conditional formatting (which is usually a little more complex in Excel)
based on your data, use the Formatting tab. Here you can choose an option
like Color or Data Bars, which apply colors to your data based on trends.

Performing Basic Calculations

1
Quickly add data with [Link] is a built-in Excel function that makes it easy to
find the total of one or more columns in a few clicks. Functions or formulas that perform calculations
and other tasks based on the values of cells. When you use a function to get something done, you're
creating a formula, which is like a math equation. If you have a column or row of numbers you want
to add:

 Click the cell below the numbers you want to add (if a column) or to the right (if a row).[8]
 On the Home tab, click AutoSum toward the upper-right corner of the app. A formula
beginning with = S U M ( c e l l + c e l l ) will appear in the field, and a dotted line will
surround the numbers you're adding.
 Press Enter or Return. You should now see the total of the numbers in the selected field.
This is here because you created your first formula—which you didn't have to write by hand!
 If you change any numbers in your data after using AutoSum, the AutoSum value will
update automatically.

2
Write a simple math [Link] is just the beginning—Excel is famous for its ability
to do all sorts of simple and complex math calculations on data. Fortunately, you don't have to be a
math whiz to create simple formulas to create everyday math formulas, like adding, subtracting, and
multiplying. Here's some basic formulas to get you started:

 Add: — Type = S U M ( c e l l + c e l l ) (e.g., =SUM(A3+B3)) to add two cells' values

together, or type = S U M ( c e l l , c e l l , c e l l ) (e.g., =SUM(A2,B2,C2)) to add a
series of cell values together.
 If you want to add all of the numbers in a whole column (or in a section of a
column), type = S U M ( c e l l : c e l l ) (e.g., =SUM(A1:A12)) into the cell you
want to use to display the result.
  Subtract: Type = S U M ( c e l l - c e l l ) (e.g., =SUM(A3-B3)) to subtract one cell value
from another cell's value.
 Divide: Type = S U M ( c e l l / c e l l ) (e.g., =SUM(A6/C5)) to divide one cell's value by
another cell's value.
 Multiply: Type = S U M ( c e l l * c e l l ) (e.g., =SUM(A2*A7)) to multiply two cell
values together.

Creating Advanced Formulas

1
Select a cell for an advanced [Link] if you need to do something more complicated
than just adding numbers? Even if you don't know how to write formulas by hand, you can still create
useful formulas that work with your data in various ways. Start by clicking the cell in which you want
to display your formula.

2
Click the Formulas [Link]'s a tab at the top of the Excel window.
3
Explore the Function [Link] function categories appear in the toolbar, such
as Financial, Text, and Math & Trig. Click the options to check out the types of functions available,
though they might not make a whole lot of sense just yet.

4
Click Insert Function .This option is in the far-left side of the Formulas toolbar. This opens the
Insert Function window, which gives you a more detailed breakdown of each function.
5
Click a function to learn about [Link] can type what you want to do (such as r o u n d ), or
choose a category to filter the list of functions. Then, click any function to read a description of how it
works and view its syntax.

 For example, to select the formula for finding the tangent of an angle, you would scroll
down and click the TAN option.

6
Select a function and click OK .This creates a formula based on the selected function.
7
Fill out the function's [Link] prompted, type in the number or select a cell for which
you want to use the formula.

 For example, if you select the TAN function, you'll type in the number for which you want
to find the tangent, or select the cell that contains that number.
 Depending on your selected function, you may need to click through a couple of on-screen
prompts.

8
Press ↵ Enter or ⏎ Return to run the [Link] so applies your function and
displays it in your selected cell.

Building Charts & Graphs

1
Set up the chart's [Link] you're creating a line graph or a bar graph, for example, you'll want to
use one column of cells for the horizontal axis and one column of cells for the vertical axis. The best
way to do this is to place your data in a table.

 Typically speaking, the left column is used for the horizontal axis and the column
immediately to the right of it represents the vertical axis.

2
Select the data in your [Link] and drag your mouse from the top-left cell of the data
down to the bottom-right cell of the data.
3
Click the Insert [Link]'s a tab at the top of the Excel window.

4
Click Recommended Charts .You'll find this option in the "Charts" section of the Insert toolbar.
A window with different chart templates will appear.

5
Select a chart [Link] the chart template you want to use based on the type of data
you're working with. If you don't see a chart type you like, click the All Charts tab to explore by
category, such as Pie, Bar, and X Y Scatter.

6
Click OK .It's at the bottom of the window. This creates your chart.

7
Use the Chart Design tab to customize your [Link] time you click your chart,
the Chart Design tab will appear at the top of Excel. You can adjust the chart style here, change
colors, and add additional elements.
 8
Double-click a chart element to manage it in the Format [Link] you double-
click something on your chart, such as a value, line, or bar, you'll see options you can edit in the panel
on the right side of excel. Here you can change the axis labels, alignment, and legend data.

Community Q&A

How do you add a check mark or an X mark to a cell?

Community Answer

You can go into Insert, then Symbol, and choose the symbol you want. After that, you can just copy
and paste the symbol from one cell to another.

Can I add work sheets on Excel?

Community Answer

Yes. At the bottom left of the Excel you will see the list of sheets. To the left of those sheets you will
find a "+" sign. Click on it.

How do I move cell contents to another cell?

Community Answer

Highlight the cell, right-click, and click Copy. Click destination cell, right-click and Paste.

 How do I make cells large enough to fit the data I put into a cell?
Community Answer

Click the cell, then drag the column of the cell, from the top to the desired size

How can I sort columns alphabetically?

Community Answer
Highlight the column that you would like to sort. Then, go to "Sort and Filter" on the Home tab and
click the option to sort from A-Z.

How do I carry headings from one sheet to the next?

Community Answer

Highlight the row with the headings and copy. Open a new sheet. Highlight the first cell (A1) in the
new sheet, and paste.

How do I download Excel?

Community Answer

Excel comes with the OS (operating system) you already have, if it is Microsoft. If you use Microsoft,
it should be on your computer, just search for it in the Start Menu. If you are using a Mac, you will
not find "Microsoft Excel", but a program similar to it.

How do I print titles on columns?

Community Answer

Click on the cell where you want the title to highlight it, and type in your title.

The arrows do not work. How do I turn this function on?

Community Answer

Click in the cell you are wanting to move. You will see a dark boarder around the cell. Hold you
pointer over one of the sides and the arrows will appear.

How do I delete unwanted cells in Excel?

Community Answer

Highlight the cells you don't want and go to the Home tab and click on Delete Cells.

How do I create a multiplication or percentage function?

Community Answer

Go to the accounting box and click on the function, then highlight the columns you want changed.

How do I extend a column in Excel?

Community Answer
Click on the right side of the column, and once the arrow appears, then stretch the lines to the right to
extend the size of the column.

How do I print only certain columns instead of the whole spread sheet?
Community Answer

Highlight just the area you want to print. When you go to the print menu where it defaults to "Print
Active Sheets", change it to "Print Selection".

How do I make my columns the width I want them?

Community Answer

You can select the lines on the sides of the column and drag them left or right to increase or decrease
your column width.

How do I insert a line?

Community Answer

Right click the row you would like to insert above, and click "Insert" in the right-click menu.

How do I merge cells or sheets in Excel?

Community Answer

Click the "home" tab then find "merge cell", click the drop-down button and select "merge across".

How do I carry headings on the next sheet?

Community Answer

Highlight the headings within the sheet you're working in, right click, and do a copy of the headings.
Click on the new sheet, insert the cursor within the row that the headings are to appear in, right click
and paste.

How do I join cells in Excel?

Community Answer

Highlight the cells to be joined. Right click within the cells and click on format cells. Within the text
control area click on the merge cell option, then click on OK.

How do I change columns and rows without affecting the ones above them?
Community Answer
 Click on the original column and click the right button. Then choose Insert and the same highlight on
the row will appear, then right click on row and B and Insert.

How do I change the background color of a cell?

Community Answer

Highlight the cell you want colored and then go to the highlighter button and select the colour you
want. Choose it and the cell will fill with that color.

References
1. ↑[Link]
720a
2. ↑[Link]
4539-b1be-499fedc14fe2
3. ↑[Link]
972-4b46e9f1e8d2
4. ↑[Link]
334e492c
5. ↑[Link]
df0ac664
6. ↑[Link]
e545c4a4654
7. ↑[Link]
a8dc-be8225b1bb78
8. ↑[Link]
7d1bb85fe706
How to Use Excel Like a Pro: 29 Easy Excel
Tips, Tricks, & Shortcuts
Written by: Caroline Forsey

Updated: 11/08/23

Sometimes, Excel seems too good to be true. All I have to do is enter a formula, and pretty
much anything I'd ever need to do manually can be done automatically.

Need to merge two sheets with similar data? Excel can do it.

Need to do simple math? Excel can do it.

Need to combine information in multiple cells? Excel can do it.

In this post, I’ll go over the best tips, tricks, and shortcuts you can use right now to take your
Excel game to the next level. No advanced Excel knowledge required.

Chapters
1. What is Excel?
2. Excel Basics
3. How to Use Excel
4. Excel Tips
5. Excel Keyboard Shortcuts

What is Excel?
 Microsoft Excel is powerful data visualization and analysis software, which uses spreadsheets
to store, organize, and track data sets with formulas and functions. Excel is used by
marketers, accountants, data analysts, and other professionals. It's part of the Microsoft
Office suite of products. Alternatives include Google Sheets and Numbers.

Find more Excel alternatives here.

What is Excel used for?

Excel is used to store, analyze, and report on large amounts of data. It is often used by
accounting teams for financial analysis, but can be used by any professional to manage long
and unwieldy datasets. Examples of Excel applications include balance sheets, budgets, or
editorial calendars.

Excel is primarily used for creating financial documents because of its strong computational
powers. You’ll often find the software in accounting offices and teams because it allows
accountants to automatically see sums, averages, and totals. With Excel, they can easily make
sense of their business’ data.

While Excel is primarily known as an accounting tool, professionals in any field can use its
features and formulas — especially marketers — because it can be used for tracking any type
of data. It removes the need to spend hours and hours counting cells or copying and pasting
performance numbers. Excel typically has a shortcut or quick fix that speeds up the process.

You can also download Excel templates below for all of your marketing needs.

After you download the templates, it’s time to start using the software. Let’s cover the basics
first.

Excel Basics
If you're just starting out with Excel, there are a few basic commands that we suggest you
become familiar with. These are things like:

 Creating a new spreadsheet from scratch.

 Executing basic computations like adding, subtracting, multiplying, and dividing.

 Writing and formatting column text and titles.

 Using Excel's auto-fill features.

 Adding or deleting single columns, rows, and spreadsheets. (Below, we'll get into how to add things
like multiple columns and rows.)

 Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what
data you're filling as you move further down the document.

 Sorting your data in alphabetical order.

Let's explore a few of these more in-depth.

For instance, why does auto-fill matter?

If you have any basic Excel knowledge, it’s likely you already know this quick trick. But to
cover our bases, allow me to show you the glory of autofill. This lets you quickly fill adjacent
cells with several types of data, including values, series, and formulas.

There are multiple ways to deploy this feature, but the fill handle is among the easiest. Select
the cells you want to be the source, locate the fill handle in the lower-right corner of the cell,
and either drag the fill handle to cover cells you want to fill or just double click:

Similarly,
sorting is an important feature you'll want to know when organizing your data in Excel.

Sometimes you may have a list of data that has no organization whatsoever. Maybe you
exported a list of your marketing contacts or blog posts. Whatever the case may be, Excel’s
sort feature will help you alphabetize any list.

Click on the data in the column you want to sort. Then click on the “Data” tab in your toolbar
and look for the “Sort” option on the left. If the “A” is on top of the “Z,” you can just click on
that button once. If the “Z” is on top of the “A,” click on the button twice. When the “A” is
on top of the “Z,” that means your list will be sorted in alphabetical order. However, when
the “Z” is on top of the “A,” that means your list will be sorted in reverse alphabetical order.

Let's explore more of the basics of Excel (along with advanced features) next.

How to Use Excel

To use Excel, you only need to input the data into the rows and columns. And then you'll use
formulas and functions to turn that data into insights.

We‘re going to go over the best formulas and functions you need to know. But first, let’s take
a look at the types of documents you can create using the software. That way, you have an
overarching understanding of how you can use Excel in your day-to-day.
 Documents You Can Create in Excel

Not sure how you can actually use Excel in your team? Here is a list of documents you can
create:

 Income Statements: You can use an Excel spreadsheet to track a company’s sales activity and
financial health.
 Balance Sheets: Balance sheets are among the most common types of documents you can create with
Excel. It allows you to get a holistic view of a company’s financial standing.
 Calendar: You can easily create a spreadsheet monthly calendar to track events or other date-sensitive
information.

Here are some documents you can create specifically for marketers.

 Marketing Budgets: Excel is a strong budget-keeping tool. You can create and track marketing
budgets, as well as spend, using Excel. If you don’t want to create a document from scratch,
download our marketing budget templates for free.
 Marketing Reports: If you don’t use a marketing tool such as Marketing Hub, you might find yourself
in need of a dashboard with all of your reports. Excel is an excellent tool to create marketing
reports. Download free Excel marketing reporting templates here.
 Editorial Calendars: You can create editorial calendars in Excel. The tab format makes it extremely
easy to track your content creation efforts for custom time ranges. Download a free editorial content
calendar template here.
 Traffic and Leads Calculator: Because of its strong computational powers, Excel is an excellent tool
to create all sorts of calculators — including one for tracking leads and traffic. Click here to download
a free premade lead goal calculator.

This is only a small sampling of the types of marketing and business documents you can
create in Excel. We’ve created an extensive list of Excel templates you can use right now for
marketing, invoicing, project management, budgeting, and more.

In the spirit of working more efficiently and avoiding tedious, manual work, here are a few
Excel formulas and functions you’ll need to know.

Excel Formulas

It’s easy to get overwhelmed by the wide range of Excel formulas that you can use to make
sense out of your data. If you’re just getting started using Excel, you can rely on the
following formulas to carry out some complex functions — without adding to the complexity
of your learning path.

 Equal sign: Before creating any formula, you’ll need to write an equal sign (=) in the cell where you
want the result to appear.
 Addition: To add the values of two or more cells, use the + sign. Example: =C5+D3.
 Subtraction: To subtract the values of two or more cells, use the - sign. Example: =C5-D3.
 Multiplication: To multiply the values of two or more cells, use the * sign. Example: =C5*D3.
 Division: To divide the values of two or more cells, use the / sign. Example: =C5/D3.

Putting all of these together, you can create a formula that adds, subtracts, multiplies, and
divides all in one cell. Example: =(C5-D3)/((A5+B6)*3).
 For more complex formulas, you’ll need to use parentheses around the expressions to avoid
accidentally using the PEMDAS order of operations. Keep in mind that you can use plain
numbers in your formulas.

Excel Functions

Excel functions automate some of the tasks you would use in a typical formula. For instance,
instead of using the + sign to add up a range of cells, you’d use the SUM function. Let’s look
at a few more functions that will help automate calculations and tasks.

 SUM: The SUM function automatically adds up a range of cells or numbers. To complete a sum, you
would input the starting cell and the final cell with a colon in between. Here’s what that looks
like: SUM(Cell1:Cell2). Example: =SUM(C5:C30).
 AVERAGE: The AVERAGE function averages out the values of a range of cells. The syntax is the
same as the SUM function: AVERAGE(Cell1:Cell2). Example: =AVERAGE(C5:C30).
 IF: The IF function allows you to return values based on a logical test. The syntax is as
follows: IF(logical_test, value_if_true, [value_if_false]). Example: =IF(A2>B2,“Over
Budget”,“OK”).
 VLOOKUP: The VLOOKUP function helps you search for anything on your sheet’s rows. The syntax
is: VLOOKUP(lookup value, table array, column number, Approximate match (TRUE) or
Exact match (FALSE)). Example: =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
 INDEX: The INDEX function returns a value from within a range. The syntax is as
follows: INDEX(array, row_num, [column_num]).
 MATCH: The MATCH function looks for a certain item in a range of cells and returns the position of
that item. It can be used in tandem with the INDEX function. The syntax is: MATCH(lookup_value,
lookup_array, [match_type]).
 COUNTIF: The COUNTIF function returns the number of cells that meet a certain criteria or have a
certain value. The syntax is: COUNTIF(range, criteria). Example: =COUNTIF(A2:A5,“London”).

Okay, ready to get into the nitty-gritty? Let‘s get to it. (And to all the Harry Potter fans out
there ... you’re welcome in advance.)

Excel Tips
1. Use Pivot tables to recognize and make sense of data.
2. Add more than one row or column.
3. Use filters to simplify your data.
4. Remove duplicate data points or sets.
5. Transpose rows into columns.
6. Split up text information between columns.
7. Use these formulas for simple calculations.
8. Get the average of numbers in your cells.
9. Use conditional formatting to make cells automatically change color based on data.
10. Use IF Excel formula to automate certain Excel functions.
11. Use dollar signs to keep one cell's formula the same regardless of where it moves.
12. Use the VLOOKUP function to pull data from one area of a sheet to another.
13. Use INDEX and MATCH formulas to pull data from horizontal columns.
14. Use the COUNTIF function to make Excel count words or numbers in any range of cells.
15. Combine cells using ampersand.
16. Add checkboxes.
17. Hyperlink a cell to a website.
18. Add drop-down menus.
 19. Use the format painter.
20. Create tables with data.
21. Use tables to conduct a what-if analysis.
22. Make formulas easier to comprehend with named ranges.
23. Group data to improve organization.
24. Use Find & Select to streamline formatting.
25. Protect your work.
26. Create custom number formats.
27. Customize the Excel ribbon.
28. Improve visual presentation with text wrapping.
29. Add emojis.

Note: Some of the GIFs and visuals are from a previous version of Excel. When applicable,
the copy has been updated to provide instruction for users of both newer and older Excel
versions.

1. Use Pivot tables to recognize and make sense of data.

Pivot tables are used to reorganize data in a spreadsheet. They won‘t change the data that you
have, but they can sum up values and compare different information in your spreadsheet,
depending on what you’d like them to do.

Let‘s take a look at an example. Let’s say I want to take a look at how many people are in
each house at Hogwarts. You may be thinking that I don't have too much data, but for longer
data sets, this will come in handy.

To create the Pivot Table, I go to Data > Pivot Table. If you’re using the most recent version
of Excel, you’d go to Insert > Pivot Table. Excel will automatically populate your Pivot
Table, but you can always change around the order of the data. Then, you have four options
to choose from.

 Report Filter: This allows you to only look at certain rows in your dataset. For example, if I wanted
to create a filter by house, I could choose to only include students in Gryffindor instead of all
students.
 Column Labels: These would be your headers in the dataset.
 Row Labels: These could be your rows in the dataset. Both Row and Column labels can contain data
from your columns (e.g. First Name can be dragged to either the Row or Column label — it just
depends on how you want to see the data.)
 Value: This section allows you to look at your data differently. Instead of just pulling in any numeric
value, you can sum, count, average, max, min, count numbers, or do a few other manipulations with
your data. In fact, by default, when you drag a field to Value, it always does a count.

Since I want to count the number of students in each house, I'll go to the Pivot table builder
and drag the House column to both the Row Labels and the Values. This will sum up the
number of students associated with each house.
2. Add more than one row or column.

As you play around with your data, you might find you‘re constantly needing to add more
rows and columns. Sometimes, you may even need to add hundreds of rows. Doing this one-
by-one would be super tedious. Luckily, there’s always an easier way.

To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting
rows or columns that you want to add. Then, right-click and select “Insert.”

In the example below, I want to add an additional three rows. By highlighting three rows and
then clicking insert, I'm able to add an additional three blank rows into my spreadsheet
quickly and easily.

3. Use filters to simplify your data.

When you‘re looking at very large data sets, you don’t usually need to be looking at every
single row at the same time. Sometimes, you only want to look at data that fit into certain
criteria.

That's where filters come in.

Filters allow you to pare down your data to only look at certain rows at one time. In Excel, a
filter can be added to each column in your data — and from there, you can then choose which
cells you want to view at once.

Let‘s take a look at the example below. Add a filter by clicking the Data tab and selecting
"Filter." Clicking the arrow next to the column headers and you’ll be able to choose whether
you want your data to be organized in ascending or descending order, as well as which
specific rows you want to show.

In my Harry Potter example, let's say I only want to see the students in Gryffindor. By
selecting the Gryffindor filter, the other rows disappear.

Pro Tip: Copy and paste the values in the spreadsheet when a Filter is on to do additional
analysis in another spreadsheet.

4. Remove duplicate data points or sets.

Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a
company and only want to see the number of companies you have. In situations like this,
removing the duplicates comes in quite handy.

To remove your duplicates, highlight the row or column that you want to remove duplicates
of. Then, go to the Data tab and select “Remove Duplicates” (which is under the Tools
subheader in the older version of Excel). A pop-up will appear to confirm which data you
want to work with. Select “Remove Duplicates,” and you're good to go.
You can also use this feature to remove an entire row based on a duplicate column value. So
if you have three rows with Harry Potter's information and you only need to see one, then you
can select the whole dataset and then remove duplicates based on email. Your resulting list
will have only unique names without any duplicates.

5. Transpose rows into columns.

When you have rows of data in your spreadsheet, you might decide you actually want to
transform the items in one of those rows into columns (or vice versa). It would take a lot of
time to copy and paste each individual header — but what the transpose feature allows you to
do is simply move your row data into columns, or the other way around.

Start by highlighting the column that you want to transpose into rows. Right-click it, and then
select “Copy.” Next, select the cells on your spreadsheet where you want your first row or
column to begin. Right-click on the cell, and then select “Paste Special.” A module will
appear — at the bottom, you'll see an option to transpose. Check that box and select OK.
Your column will now be transferred to a row or vice-versa.

On newer versions of Excel, a drop-down will appear instead of a pop-up.

 6. Split up text information between columns.

What if you want to split out information that‘s in one cell into two different cells? For
example, maybe you want to pull out someone’s company name through their email address.
Or perhaps you want to separate someone's full name into a first and last name for your email
marketing templates.

Thanks to Excel, both are possible. First, highlight the column that you want to split up. Next,
go to the Data tab and select “Text to Columns.” A module will appear with additional
information.

First, you need to select either “Delimited” or “Fixed Width.”

 “Delimited” means you want to break up the column based on characters such as commas, spaces, or
tabs.

 “Fixed Width” means you want to select the exact location on all the columns that you want the split
to occur.

In the example case below, let's select “Delimited” so we can separate the full name into first
name and last name.

Then, it‘s time to choose the Delimiters. This could be a tab, semi-colon, comma, space, or
something else. ("Something else" could be the "@" sign used in an email address, for
example.) In our example, let’s choose the space. Excel will then show you a preview of what
your new columns will look like.

When you‘re happy with the preview, press "Next." This page will allow you to select
Advanced Formats if you choose to. When you’re done, click “Finish.”
 7. Use formulas for simple calculations.

In addition to doing pretty complex calculations, Excel can help you do simple arithmetic like
adding, subtracting, multiplying, or dividing any of your data.

 To add, use the + sign.

 To subtract, use the - sign.

 To multiply, use the * sign.

 To divide, use the / sign.

You can also use parentheses to ensure certain calculations are done first. In the example
below (10+10*10), the second and third 10 were multiplied together before adding the
additional 10. However, if we made it (10+10)*10, the first and second 10 would be added
together first.

8. Get the average of numbers in your cells.

If you want the average of a set of numbers, you can use the
formula =AVERAGE(Cell1:Cell2). If you want to sum up a column of numbers, you can
use the formula =SUM(Cell1:Cell2).

9. Use conditional formatting to make cells automatically change color based

on data.

Conditional formatting allows you to change a cell's color based on the information within
the cell. For example, if you want to flag certain numbers that are above average or in the top
10% of the data in your spreadsheet, you can do that. If you want to color code
commonalities between different rows in Excel, you can do that. This will help you quickly
see information that is important to you.

To get started, highlight the group of cells you want to use conditional formatting on. Then,
choose “Conditional Formatting” from the Home menu and select your logic from the
dropdown. (You can also create your own rule if you want something different.) A window
will pop up that prompts you to provide more information about your formatting rule. Select
“OK” when you're done, and you should see your results automatically appear.

10. Use the IF Excel formula to automate certain Excel functions.

Sometimes, we don't want to count the number of times a value appears. Instead, we want to
input different information into a cell if there is a corresponding cell with that information.

For example, in the situation below, I want to award ten points to everyone who belongs in
the Gryffindor house. Instead of manually typing in 10‘s next to each Gryffindor student’s
name, I can use the IF Excel formula to say that if the student is in Gryffindor, then they
should get ten points.

The formula is: IF(logical_test, value_if_true, [value_if_false])

Example Shown Below: =IF(D2=“Gryffindor”,“10”,“0”)

In general terms, the formula would be IF(Logical Test, value of true, value of false). Let's
dig into each of these variables.
 Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is
D2=“Gryffindor” because we want to make sure that the cell corresponding with the student says
“Gryffindor.” Make sure to put Gryffindor in quotation marks here.
 Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the
cell to show “10” to indicate that the student was awarded the 10 points. Only use quotation marks if
you want the result to be text instead of a number.
 Value_if_False: This is what we want the cell to show if the value is false. In this case, for any
student not in Gryffindor, we want the cell to show “0”. Only use quotation marks if you want the
result to be text instead of a number.

Note: In the example above, I awarded 10 points to everyone in Gryffindor. If I later wanted
to sum the total number of points, I wouldn‘t be able to because the 10’s are in quotes, thus
making them text and not a number that Excel can sum.

The real power of the IF function comes when you string multiple IF statements together, or
nest them. This allows you to set multiple conditions, get more specific results, and
ultimately organize your data into more manageable chunks.

Ranges are one way to segment your data for better analysis. For example, you can categorize
data into values that are less than 10, 11 to 50, or 51 to 100. Here's how that looks in practice:

=IF(B3<11,“10 or less”,IF(B3<51,“11 to 50”,IF(B3<100,“51 to 100”)))

It can take some trial-and-error, but once you have the hang of it, IF formulas will become
your new Excel best friend.

11. Use dollar signs to keep one cell's formula the same regardless of where it
moves.

Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn't
representing an American dollar; instead, it makes sure that the exact column and row are
held the same even if you copy the same formula in adjacent rows.

You see, a cell reference — when you refer to cell A5 from cell C5, for example — is
relative by default. In that case, you‘re actually referring to a cell that’s five columns to the
left (C minus A) and in the same row (5). This is called a relative formula. When you copy a
relative formula from one cell to another, it‘ll adjust the values in the formula based on where
it’s moved. But sometimes, we want those values to stay the same no matter whether they're
moved around or not — and we can do that by turning the formula into an absolute formula.
 To change the relative formula (=A5+C5) into an absolute formula, we'd precede the row and
column values by dollar signs, like this: (=$A$5+$C$5). (Learn more on Microsoft Office's
support page here.)

12. Use the VLOOKUP function to pull data from one area of a sheet to
another.

Have you ever had two sets of data on two different spreadsheets that you want to combine
into a single spreadsheet?

For example, you might have a list of people‘s names next to their email addresses in one
spreadsheet, and a list of those same people’s email addresses next to their company names in
the other — but you want the names, email addresses, and company names of those people to
appear in one place.

I have to combine data sets like this a lot — and when I do, the VLOOKUP is my go-to
formula.

Before you use the formula, though, be absolutely sure that you have at least one column that
appears identically in both places. Scour your data sets to make sure the column of data
you're using to combine your information is exactly the same, including no extra spaces.

The formula: =VLOOKUP(lookup value, table array, column number, Approximate

match (TRUE) or Exact match (FALSE))

The formula with variables from our example

below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this formula, there are several variables. The following is true when you want to combine
information in Sheet 1 and Sheet 2 onto Sheet 1.

 Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in
your first spreadsheet. In the example that follows, this means the first email address on the list, or
cell 2 (C2).
 Table Array: The table array is the range of columns on Sheet 2 you‘re going to pull your data from,
including the column of data identical to your lookup value (in our example, email addresses) in Sheet
1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.”
“A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our
lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information
that's only available in Sheet 2 that you want to translate to Sheet 1.
 Column Number: This tells Excel which column the new data you want to copy to Sheet 1 is located
in. In our example, this would be the column that “House” is located in. “House” is the second
column in our range of columns (table array), so our column number is 2. [Note: Your range can be
more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House
— and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to
change the “2” to a “3” so it pulls back the value in the third column:
=VLOOKUP(C2:Sheet2!A:C,3,false).]
 Approximate Match (TRUE) or Exact Match (FALSE): Use FALSE to ensure you pull in only
exact value matches. If you use TRUE, the function will pull in approximate matches.
 In the example below, Sheet 1 and Sheet 2 contain lists describing different information
about the same people, and the common thread between the two is their email addresses. Let's
say we want to combine both datasets so that all the house information from Sheet 2
translates over to Sheet 1.

So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the

house data into Sheet 1.

Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the
right of the column containing your identical data. This can lead to some limitations, which is
why some people prefer to use the INDEX and MATCH functions instead.

13. Use INDEX and MATCH formulas to pull data from horizontal columns.

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into
one central location. Here are the main differences:

 VLOOKUP is a much simpler formula. If you're working with large data sets that would require
thousands of lookups, using the INDEX and MATCH function will significantly decrease load time in
Excel.

 The INDEX and MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a
left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right
of the results column, then you'd have to rearrange those columns in order to do a VLOOKUP. This
can be tedious with large datasets and/or lead to errors.

So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column
values in Sheets 1 and 2 aren‘t the same, then to do a VLOOKUP, I would need to switch
around my columns. In this case, I’d choose to do an INDEX and MATCH instead.

Let‘s look at an example. Let’s say Sheet 1 contains a list of people‘s names and their
Hogwarts email addresses, and Sheet 2 contains a list of people’s email addresses and the
Patronus that each student has. (For the non-Harry Potter fans out there, every witch or
wizard has an animal guardian called a “Patronus” associated with him or her.) The
information that lives in both sheets is the column containing email addresses, but this email
 address column is in different column numbers on each sheet. I‘d use the INDEX and
MATCH formulas instead of VLOOKUP so I wouldn’t have to switch any columns around.

So what‘s the formula, then? The formula is actually the MATCH formula nested inside the
INDEX formula. You’ll see I differentiated the MATCH formula using a different color here.

The formula: =INDEX(table array, MATCH formula)

This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))

The formula with variables from our example

below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Here are the variables:

 Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to
Sheet 1. In our example, “A” means Column A, which contains the “Patronus” information for each
person.
 Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In
the example that follows, this means the “email” column on Sheet 1, which is Column C. So:
Sheet1!C:C.
 Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In
the example that follows, this refers to the “email” column on Sheet 2, which happens to also be
Column C. So: Sheet2!C:C.

Once you have your variables straight, type in the INDEX and MATCH formulas in the top-
most cell of the blank Patronus column on Sheet 1, where you want the combined
information to live.

14. Use the COUNTIF function to make Excel count words or numbers in any
range of cells.

Instead of manually counting how often a certain value or number appears, let Excel do the
work for you. With the COUNTIF function, Excel can count the number of times a word or
number appears in any range of cells.
 For example, let's say I want to count the number of times the word “Gryffindor” appears in
my data set.

The formula: =COUNTIF(range, criteria)

The formula with variables from our example below: =COUNTIF(D:D,“Gryffindor”)

In this formula, there are several variables:

 Range: The range that we want the formula to cover. In this case, since we're only focusing on one
column, we use “D:D” to indicate that the first and last column are both D. If I were looking at
columns C and D, I would use “C:D.”
 Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you
want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”

Simply typing in the COUNTIF formula in any cell and pressing “Enter” will show me how
many times the word “Gryffindor” appears in the dataset.

15. Combine cells using &.

Databases tend to split out data to make it as exact as possible. For example, instead of
having a column that shows a person‘s full name, a database might have the data as a first
name and then a last name in separate columns. Or, it may have a person’s location separated
by city, state, and zip code. In Excel, you can combine cells with different data into one cell
by using the “&” sign in your function.

The formula with variables from our example below: =A2&“ ”&B2

Let‘s go through the formula together using an example. Pretend we want to combine first
names and last names into full names in a single column. To do this, we’d first put our cursor
in the blank cell where we want the full name to appear. Next, we'd highlight one cell that
contains a first name, type in an “&” sign, and then highlight a cell with the corresponding
last name.
But you‘re not finished — if all you type in is =A2&B2, then there will not be a space
between the person’s first name and last name. To add that necessary space, use the
function =A2&“ ”&B2. The quotation marks around the space tell Excel to put a space in
between the first and last name.

To make this true for multiple rows, simply drag the corner of that first cell downward as
shown in the example.

16. Add checkboxes.

If you‘re using an Excel sheet to track customer data and want to oversee something that isn’t
quantifiable, you could insert checkboxes into a column.

For example, if you‘re using an Excel sheet to manage your sales prospects and want to track
whether you called them in the last quarter, you could have a "Called this quarter?" column
and check off the cells in it when you’ve called the respective client.

Here's how to do it.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click
DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle
highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and
then paste it.

17. Hyperlink a cell to a website.

If you‘re using your sheet to track social media or website metrics, it can be helpful to have a
reference column with the links each row is tracking. If you add a URL directly into Excel, it
should automatically be clickable. But, if you have to hyperlink words, such as a page title or
the headline of a post you’re tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up
allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or
click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting
the cell and clicking Insert > Hyperlink.

18. Add drop-down menus.

Sometimes, you‘ll be using your spreadsheet to track processes or other qualitative things.
Rather than writing words into your sheet repetitively, such as "Yes", "No", "Customer
Stage", "Sales Lead", or "Prospect", you can use dropdown menus to quickly mark
descriptive things about your contacts or whatever you’re tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top
navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then
click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you’ve probably noticed, Excel has a lot of features to make crunching numbers and
analyzing your data quick and easy. But if you ever spent some time formatting a sheet to
your liking, you know it can get a bit tedious.

Don’t waste time repeating the same formatting commands over and over again. Use the
format painter to easily copy the formatting from one area of the worksheet to another. To do
so, choose the cell you’d like to replicate, then select the format painter option (paintbrush
icon) from the top toolbar.

20. Create tables with data.

Converting your data into a table not only makes it visually appealing but also provides
improved data management and analysis capabilities.
 To get started, you’ll need to select the range of cells that you want to convert into a table.
Then, go to the Home tab in the Excel ribbon. In the Styles group, click on the Format as
Table button — it looks like a grid of cells. Then, choose a table style from the available
options, or customize a table if desired.

In the Create Table dialog box, make sure the range you selected is correct. If Excel did not
automatically detect the range correctly, you can adjust it manually. If your table has headers
(column names), ensure that the “My table has headers” option is checked. This allows Excel
to treat the first row as the header row.

Once everything is ready, click the OK button, and Excel will convert your selected data into
a table.

After your data is converted into a table, you'll notice some additional features and
functionalities become available:

 The table is automatically assigned a name, such as “Table1” or “Table2,” which you can modify if
needed.

 Filter drop-down arrows appear in the header row, allowing you to filter data within the table easily.

 The table is formatted with alternating row colors, making it visually appealing.

 Total rows are automatically added at the bottom of each column, allowing you to perform
calculations like sum, average, etc., for the data in that column.

21. Use tables to conduct a what-if analysis.

In addition to making your data more organized, tables can also help you conduct what-if
analyses. This allows you to test various combinations of input values and observe the
resulting outcomes.

A what-if analysis can be beneficial when it comes to decision making, planning, forecasting,
financial modeling, sensitivity analysis, resource planning, and more.

To get started, you’ll need to set up your worksheet with the necessary formulas and variables
you want to analyze. Then, determine the input values that you want to vary. Typically, you
will choose one or two input variables.
Select the cell where you want to display the results of your what-if analysis. Then, go to the
Data tab in the Excel ribbon and click on the What-If Analysis button. From the dropdown
menu, select Data Table.

In the Table Input dialog box, enter the input values that you want to test for each variable. If
you have one variable, enter the different input values in a column or row. If you have two
variables, enter the combinations in a table format.

Select the cells in the table area that correspond to the formula cell you want to analyze. This
is the cell that will display the results for each combination of input values.

Click OK to generate the data table. Excel will calculate the formula for each combination of
input values and display the results in the selected cells. The data table acts as a grid, showing
the various scenarios and their corresponding outcomes.

Once your table is created, you can use it to identify trends, patterns, or specific values of
interest. Play around with the input values and see how it may affect the final results.

22. Make formulas easier to comprehend with named ranges.

Instead of referring to a range of cells by its coordinates (e.g., A1:B10), you can assign a
name to it. This makes formulas more readable and easier to manage.

To get started, select the cell or range of cells that you want to name. Go to the Formulas tab
in the Excel ribbon and click on the Define Name button in the Defined Names group.
Alternatively, you can use the keyboard shortcut Alt + M + N + D.

In the New Name dialog box, enter a name for the selected cell or range in the Name field.
Make sure the name is descriptive and easy to remember. By default, Excel assigns the
selected cell or range's reference to the Refers to field in the dialog box. If needed, you can
modify the reference to include additional cells or adjust the range.

Click the OK button to save the named range. Once you've named a range, you can use it in
your formulas by simply typing the name instead of the cell reference. For example, if you
named cell A1 as “Revenue,” you could use =Revenue instead of =A1 in your formulas.

Using named ranges offers several benefits:

 Improved formula readability: Named ranges make formulas easier to understand and navigate,
especially in complex calculations or large datasets.
 Flexibility for range adjustments: If your dataset changes, you can easily modify the range
assigned to a named range without updating each formula that references it.
 Enhanced collaboration: Named ranges make it easier to collaborate with others, as they can
understand the purpose of a named range and use it in their own calculations.
 Simplified data analysis: When using named ranges, you can create more intuitive data analysis
by referring to named ranges in functions like SUM, AVERAGE, COUNTIF, etc.

To manage named ranges, you can go to the Formulas tab, click on the Name Manager button
in the Defined Names group. The Name Manager offers functionalities to modify, delete, or
review existing named ranges.
23. Group data to improve organization.

Grouping data in Excel provides a way to organize, analyze, and present information more
effectively, making it easier to identify patterns, trends, and insights within your data. For
instance, if you have a list of leads generated, you can group the data by month to create a
monthly performance report.

Grouping data especially makes it easier to navigate and work with large data sets. It helps in
organization and reduces clutter by collapsing the groups that are not immediately needed.

To group data in Excel, select the range of cells or columns that you want to group. Make
sure the data is sorted properly, if needed.

On the Data tab in the Excel ribbon, click on the Group button. It is usually found in the
Outline or Data Tools group.

You can specify the grouping levels by choosing options like Rows or Columns. For
example, if you want to group data by month, you can select Months. You can also set
additional options such as Summary rows below detail or Collapse the outline to the
summary levels. These options affect how the grouped data is displayed.

Once you have the options you want selected, click on the OK button, and Excel will group
the selected data based on your settings.

After your data is grouped, you will see a plus (+) or minus (-) button next to the grouped
rows or columns. Clicking on the plus button expands the group to show the individual
records, and clicking on the minus button collapses the group to hide the details.

24. Use Find & Select to streamline formatting.

Why format and clean up your spreadsheet manually when you can do it in just a few clicks?
Using the Find & Select tool can help you maintain accuracy and consistency in your
documents.

To get started, open the Excel worksheet that contains the data you want to search. Press the
Ctrl + F keys on your keyboard or go to the Home tab and click on the Find & Select drop-
down menu. Then, select Find from the menu. The Find and Replace dialog box will open.
In the Find field, enter the specific data you want to find. Optionally, you can narrow down
your search to specific cells, rows, columns, or formulas by choosing the appropriate options
in the dialog box.

Click on the Find next button to search for the first occurrence of the data. Excel will
highlight the cell containing the data.

To replace the found data with new information, click on the Replace button in the dialog
box. This will replace the highlighted occurrence with the data you enter in the Replace field.

To replace all occurrences of the data at once, click on the Replace All button. Once you have
finished finding and replacing, you can close the dialog box.

Note: Be cautious when using the Replace All feature, as it replaces all occurrences without
confirmation. It is always a good practice to review each replacement carefully before using
the Replace All option.

25. Protect your work.

Protecting your work in Excel is essential for data security, maintaining data integrity,
preserving intellectual property, and complying with legal or regulatory requirements. It
allows you to have control over who can access and modify your work, minimizing risks and
maintaining the quality and confidentiality of your data.

Here are a couple ways you can protect your work:

Protect a Worksheet

1. Open your Excel worksheet and navigate to the Review tab.

2. Click on the Manage Protection button in the Protection group.
3. A Manage Protection dialog box will appear. There, you can select whether or not you want
to protect the sheet. Set a password if desired and choose the options you want to apply, such
as preventing users from making changes to cells, formatting, inserting/deleting columns or
rows, etc.
Protecting a Workbook

1. Open your Excel workbook and navigate to the File tab.

2. Click on Info and select Protect Workbook from the options.
3. Choose Encrypt with Password and enter a password if desired.
4. Click OK to protect the workbook.

Taking these extra steps ensures your work is protected. Just make sure to keep your
passwords safe and secure.

26. Create custom number formats.

To display data in unique ways, use custom number formats. Doing this can help with data
presentation, data clarity, consistency, localization, and masking sensitive data.
To get started, select the cell or range of cells that you want to format. Right-click on the
selected cells and choose Number Format from the context menu. Then, find the Category list
and select Custom.

In the Type field, you can enter a custom number format code to define your desired format.
Here are some examples of custom number formats:

 To display numbers with a specific number of decimal places, use the 0 or # symbol to represent a
digit, and a zero or hashtag without a decimal point to represent optional digits. For
example, 0.00 will display two decimal places, 0.### will display up to three decimal places,
and ### will display no decimal places.
 To display a specific text or character alongside numbers, use the @ symbol. For example, $0 will
display a dollar sign before the number.
 To display percentages, use the % symbol. For example, 0% will display the number as a
percentage.
 To create custom date or time formats, use codes such as dd for day, mm for month, yy for two-
digit year, hh for hours, mm for minutes, and ss for seconds. For example, dd/mm/yyyy will
display the date in the format of day/month/year.

As you enter your custom number format in the Type field, you will see a Sample section that
shows a preview of how the format will be applied. Click OK to apply the custom number
format to the selected cells.

27. Customize the Excel ribbon.

Although the Excel ribbon already contains various tools that are used to execute common
functions and commands, you can customize it to fit your specific needs and preferences.
This can help streamline your workflow and make commonly used commands more easily
accessible. It also allows you to remove unnecessary elements that you don’t use, making it
easier to navigate and find the tools you need.

To make customizations, start by right clicking on an empty area of the ribbon and select
Customize the Ribbon. In the Excel Options window that appears, you'll see two sections.
The left section displays the tabs currently visible in the ribbon, while the right section
displays the tabs you can add.

To customize the ribbon, you have several options:

 To add a new tab, click on New Tab in the right section and give it a name.
 To add a group within an existing tab, select the tab in the left section, click New Group in
the right section, and name it.

 To add commands to a group, select the group in the right section, choose commands from
the left section, and click Add. You can also customize the order of the commands using the
Up and Down buttons.

You can also remove tabs, groups, or commands from the ribbon. Select the item you want to
remove in the left section and click Remove.

To change the order of tabs and groups, select the item in the left section and use the Up and
Down buttons to rearrange them.

Click OK in the Excel Options window to save your changes and apply the customized
ribbon.

To extend Excel’s functionality even further, you can customize the ribbon with additional
applications by clicking on the Add-ins button in the Home tab.
Note: Customizing the ribbon is specific to your Excel installation and won‘t affect other
users’ ribbons.

28. Improve visual presentation with text wrapping.

Even though spreadsheets aren’t always the most interesting things to look at, you can still
take the time to make them easier to read by wrapping text.

Doing this lets you display multiple lines of text within a single cell. It's particularly handy
when you need to include line breaks or break up paragraphs of information within a cell
without increasing the row height.

Select the cell(s) with the text you want to wrap. Navigate to the toolbar at the top of the
Excel window and locate the Wrap Text button (an icon with an angled arrow). It is typically
found in the Alignment section. Then, click on Wrap Text.

29. Add emojis.

Give your spreadsheets a little personal touch by adding in emojis.

To get started, click on the cell where you want to insert an emoji. Then, open the emoji
keyboard. This step may vary based on your operating system.

 Windows: Use the keyboard shortcut Win + . or Win + ; to open the emoji keyboard.
 macOS: Use the keyboard shortcut Ctrl + Cmd + Space to access the emoji keyboard.
Browse through the available emojis and click on the one you want to insert. The selected
emoji should now appear in the selected cell.

Emojis may appear small by default in Excel cells. If you want to make them larger to
improve visibility, you can adjust the cell size by dragging the row height and column width
accordingly.

You can also copy emojis from external sources on the web or other applications and paste
them directly into Excel cells.

Note: The ability to use emojis in Excel depends on the version of Excel and the device you
are using. Some older versions or platforms may not support emojis or display them
correctly. Therefore, it's important to ensure compatibility with the Excel version and
platform you are working with.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating,
formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel
shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

 PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

 How to Make a Chart or Graph in Excel [With Video Tutorial]
 Design Tips to Create Beautiful Excel Charts and Graphs
 Totally Free Microsoft Excel Templates That Make Marketing Easier
 How to Learn Excel Online: Free and Paid Resources for Excel Training

Use Excel to Automate Processes in Your Team

Even if you’re not an accountant, you can still use Excel to automate tasks and processes in
your team. With the tips and tricks we shared in this post, you’ll be sure to use Excel to its
fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for
comprehensiveness.
Top 30 Excel Formulas and Functions You
Should Know
By Shruti M
Last updated on Jan 31, 2024

Microsoft Excel is the go-to tool for working with data. There are probably a handful
of people who haven’t used Excel, given its immense popularity. Excel is a widely
used software application in industries today, built to generate reports and business
insights. Excel supports several in-built applications that make it easier to use.

One such feature that allows Excel to stand out is - Excel sheet formulas. Here, we
will look into the top 25 Excel formulas that one must know while working on Excel.
The topics that we will be covering in this article are as follows:

 What is Excel Formula?

 Excel Formulas and Functions

What is Excel Formula?

In Microsoft Excel, a formula is an expression that operates on values in a range of
cells. These formulas return a result, even when it is an error. Excel formulas enable
you to perform calculations such as addition, subtraction, multiplication, and division.
In addition to these, you can find out averages and calculate percentages in excel for
a range of cells, manipulate date and time values, and do a lot more.

Formulas in Excel: An Overview

 Choose a cell.
 To enter an equal sign, click the cell and type =.
  Enter the address of a cell in the selected cell or select a cell from the list.
 You need to enter an operator.
 Enter the address of the next cell in the selected cell.
 Press Enter.

There is another term that is very familiar to Excel formulas, and that is "function".
The two words, "formulas" and "functions" are sometimes interchangeable. They are
closely related, but yet different. A formula begins with an equal sign. Meanwhile,
functions are used to perform complex calculations that cannot be done manually.
Functions in excel have names that reflect their intended use.

The example below shows how we have used the multiplication formula manually
with the ‘*’ operator.

Sample Formula: "=A2*B2"

Fig: Microsoft Excel Formula

This example below shows how we have used the function - ‘PRODUCT’ to perform
multiplication. As you can see, we didn’t use the mathematical operator here.

Sample Formula: "=PRODUCT(A2,B2)"

Fig: Microsoft Excel Function

Excel formulas and functions help you perform your tasks efficiently, and it's time-
saving. Let's proceed and learn the different types of functions available in Excel and
use relevant formulas as and when required.
Excel Formulas and Functions
There are plenty of Excel formulas and functions depending on what kind of
operation you want to perform on the dataset. We will look into the formulas and
functions on mathematical operations, character-text functions, data and time, sumif-
countif, and few lookup functions.

Let’s now look at the top 25 Excel formulas you must know. In this article, we have
categorized 25 Excel formulas based on their operations. Let’s start with the first
Excel formula on our list.

1. SUM

The SUM() function, as the name suggests, gives the total of the selected range of
cell values. It performs the mathematical operation which is addition. Here’s an
example of it below:

Sum "=SUM(C2:C4)"

Fig: Sum function in Excel

As you can see above, to find the total amount of sales for every unit, we had to
simply type in the function “=SUM(C2:C4)”. This automatically adds up 300, 385, and
480. The result is stored in C5.

2. AVERAGE

The AVERAGE() function focuses on calculating the average of the selected range
of cell values. As seen from the below example, to find the avg of the total sales, you
have to simply type in:

AVERAGE =AVERAGE(C2, C3, C4)

 Fig: Average function in Excel

It automatically calculates the average, and you can store the result in your desired
location.

3. COUNT

The function COUNT() counts the total number of cells in a range that contains a
number. It does not include the cell, which is blank, and the ones that hold data in
any other format apart from numeric.

COUNT =COUNT(C1:C4)

Fig: Microsoft Excel Function - Count

As seen above, here, we are counting from C1 to C4, ideally four cells. But since the
COUNT function takes only the cells with numerical values into consideration, the
answer is 3 as the cell containing “Total Sales” is omitted here.

If you are required to count all the cells with numerical values, text, and any other
data format, you must use the function ‘COUNTA()’. However, COUNTA() does not
count any blank cells.
To count the number of blank cells present in a range of cells, COUNTBLANK() is
used.

4. SUBTOTAL

Moving ahead, let’s now understand how the subtotal function works. The
SUBTOTAL() function returns the subtotal in a database. Depending on what you
want, you can select either average, count, sum, min, max, min, and others. Let’s
have a look at two such examples.

Fig: Subtotal function in Excel

In the example above, we have performed the subtotal calculation on cells ranging
from A2 to A4. As you can see, the function used is

SUBTOTAL =SUBTOTAL(1, A2: A4)

In the subtotal list “1” refers to average. Hence, the above function will give the
average of A2: A4 and the answer to it is 11, which is stored in C5. Similarly,

“=SUBTOTAL(4, A2: A4)”

This selects the cell with the maximum value from A2 to A4, which is 12.
Incorporating “4” in the function provides the maximum result.

Fig: Count function in Excel

5. MODULUS

The MOD() function works on returning the remainder when a particular number is
divided by a divisor. Let’s now have a look at the examples below for better
understanding.

 In the first example, we have divided 10 by 3. The remainder is calculated using the
function

MODULUS =MOD(A2,3)

 The result is stored in B2. We can also directly type “=MOD(10,3)” as it will give the same
answer.

Fig: Modulus function in Excel

 Similarly, here, we have divided 12 by 4. The remainder is 0 is, which is stored in B3.

Fig: Modulus function in Excel

6. POWER

The function “Power()” returns the result of a number raised to a certain power. Let’s
have a look at the examples shown below:
 Fig: Power function in Excel

As you can see above, to find the power of 10 stored in A2 raised to 3, we have to
type:

Power =POWER (A2,3)

This is how power function works in Excel.

7. CEILING

Next, we have the ceiling function. The CEILING() function rounds a number up to its
nearest multiple of significance.

Fig: Ceiling function in Excel

The nearest highest multiple of 5 for 35.316 is 40.

8. FLOOR

Contrary to the Ceiling function, the floor function rounds a number down to the
nearest multiple of significance.
 Fig: Floor function in Excel

The nearest lowest multiple of 5 for 35.316 is 35.

9. CONCATENATE

This function merges or joins several text strings into one text string. Given below
are the different ways to perform this function.

 In this example, we have operated with the syntax:

CONCATENATE =CONCATENATE(A25, " ", B25)

Fig: Concatenate function in Excel

 In this example, we have operated with the syntax:

"=CONCATENATE(A27&" "&B27)"

Fig: Concatenate function in Excel

Those were the two ways to implement the concatenation operation in Excel.

Also Read: How to Use Concatenate in Excel?

10. LEN

The function LEN() returns the total number of characters in a string. So, it will count
the overall characters, including spaces and special characters. Given below is an
example of the Len function.

Fig: Len function in Excel

Let’s now move onto the next Excel function on our list of this article.

11. REPLACE

As the name suggests, the REPLACE() function works on replacing the part of a text
string with a different text string.

The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here,

start_num refers to the index position you want to start replacing the characters with.
Next, num_chars indicate the number of characters you want to replace.

Let’s have a look at the ways we can use this function.

 Here, we are replacing A101 with B101 by typing

REPLACE =REPLACE(A15,1,1,"B")
 Fig: Replace function in Excel

 Next, we are replacing A102 with A2102 by typing:

“=REPLACE(A16,1,1, "A2")”

Fig: Replace function in Excel

 Finally, we are replacing Adam with Saam by typing:

“=REPLACE(A17,1,2, "Sa")”

Fig: Replace function in Excel

Let’s now move to our next function.

12. SUBSTITUTE

The SUBSTITUTE() function replaces the existing text with a new text in a text
string.

The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.

Here, [instance_num] refers to the index position of the present texts more than
once.

Given below are a few examples of this function:

 Here, we are substituting “I like” with “He likes” by typing:

“=SUBSTITUTE(A20, "I like","He likes")”

 Fig: Substitute function in Excel

 Next, we are substituting the second 2010 that occurs in the original text in cell A21 with
2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”.

Fig: Substitute function in Excel

 Now, we are replacing both the 2010s in the original text with 2016 by typing
“=SUBSTITUTE(A22,2010,2016)”.

Fig: Substitute function in Excel

That was all about the substitute function, let’s now move on to our next function.

13. LEFT, RIGHT, MID

The LEFT() function gives the number of characters from the start of a text string.
Meanwhile, the MID() function returns the characters from the middle of a text string,
given a starting position and length. Finally, the right() function returns the number of
characters from the end of a text string.

Let’s understand these functions with a few examples.

 In the example below, we use the function left to obtain the leftmost word on the sentence
in cell A5.

Fig: Left function in Excel

Shown below is an example using the mid function.

Fig: Mid function in Excel

 Here, we have an example of the right function.

Fig: Right function in Excel

14. UPPER, LOWER, PROPER

The UPPER() function converts any text string to uppercase. In contrast, the
LOWER() function converts any text string to lowercase. The PROPER() function
converts any text string to proper case, i.e., the first letter in each word will be in
uppercase, and all the other will be in lowercase.

Let’s understand this better with the following examples:

 Here, we have converted the text in A6 to a full uppercase one in A7.

Fig: Upper function in Excel

 Now, we have converted the text in A6 to a full lowercase one, as seen in A7.

Fig: Lower function in Excel

 Finally, we have converted the improper text in A6 to a clean and proper format in A7.

Fig: Proper function in Excel

Now, let us hop on to exploring some date and time functions in Excel.

15. NOW()

The NOW() function in Excel gives the current system date and time.
 Fig: Now function in Excel

The result of the NOW() function will change based on your system date and time.

16. TODAY()

The TODAY() function in Excel provides the current system date.

Fig: Today function in Excel

The function DAY() is used to return the day of the month. It will be a number
between 1 to 31. 1 is the first day of the month, 31 is the last day of the month.

Fig: Day function in Excel

The MONTH() function returns the month, a number from 1 to 12, where 1 is January
and 12 is December.

Fig: Month function in Excel

The YEAR() function, as the name suggests, returns the year from a date value.

Fig: Year function in Excel

17. TIME()

The TIME() function converts hours, minutes, seconds given as numbers to an Excel
serial number, formatted with a time format.
 Fig: Time function in Excel

18. HOUR, MINUTE, SECOND

The HOUR() function generates the hour from a time value as a number from 0 to
23. Here, 0 means 12 AM and 23 is 11 PM.

Fig: Hour function in Excel

The function MINUTE(), returns the minute from a time value as a number from 0 to
59.

Fig: Minute function in Excel

The SECOND() function returns the second from a time value as a number from 0 to
59.

Fig: Second function in Excel

19. DATEDIF

The DATEDIF() function provides the difference between two dates in terms of
years, months, or days.

Below is an example of a DATEDIF function where we calculate the current age of a

person based on two given dates, the date of birth and today’s date.
 Fig: Datedif function in Excel

Now, let’s skin through a few critical advanced functions in Excel that are popularly
used to analyze data and create reports.

20. VLOOKUP

Next up in this article is the VLOOKUP() function. This stands for the vertical lookup
that is responsible for looking for a particular value in the leftmost column of a table.
It then returns a value in the same row from a column you specify.

Below are the arguments for the VLOOKUP function:

lookup_value - This is the value that you have to look for in the first column of a
table.

table - This indicates the table from which the value is retrieved.

col_index - The column in the table from the value is to be retrieved.

range_lookup - [optional] TRUE = approximate match (default). FALSE = exact

match.

We will use the below table to learn how the VLOOKUP function works.

If you wanted to find the department to which Stuart belongs, you could use the
VLOOKUP function as shown below:
 Fig: Vlookup function in Excel

Here, A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index
number with information about departments, and 0 is the range lookup.

If you hit enter, it will return “Marketing”, indicating that Stuart is from the marketing
department.

21. HLOOKUP

Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal

lookup. The function HLOOKUP looks for a value in the top row of a table or array of
benefits. It gives the value in the same column from a row you specify.

Below are the arguments for the HLOOKUP function:

lookup_value - This indicates the value to lookup.

table - This is the table from which you have to retrieve data.

row_index - This is the row number from which to retrieve data.

range_lookup - [optional] This is a boolean to indicate an exact match or

approximate match. The default value is TRUE, meaning an approximate match.

Given the below table, let’s see how you can find the city of Jenson using
HLOOKUP.
 Fig: Hlookup function in Excel

Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row
index number, 0 is for an approximate match.

Once you hit enter, it will return “New York”.

Our Data Analyst Master's Program will help you learn analytics tools and techniques to
become a Data Analyst expert! It's the pefect course for you to jumpstart your career. Enroll
now!

22. IF Formula

The IF() function checks a given condition and returns a particular value if it is TRUE.
It will return another value if the condition is FALSE.

In the below example, we want to check if the value in cell A2 is greater than 5. If it’s
greater than 5, the function will return “Yes 4 is greater”, else it will return “No”.

Fig: If function in Excel

In this case, it will return ‘No’ since 4 is not greater than 5.

‘IFERROR’ is another function that is popularly used. This function returns a value if
an expression evaluates to an error, or else it will return the value of the expression.

Suppose you want to divide 10 by 0. This is an invalid expression, as you can’t

divide a number by zero. It will result in an error.

The above function will return “Cannot divide”.

23. INDEX-MATCH

The INDEX-MATCH function is used to return a value in a column to the left. With
VLOOKUP, you're stuck returning an appraisal from a column to the right. Another
reason to use index-match instead of VLOOKUP is that VLOOKUP needs more
processing power from Excel. This is because it needs to evaluate the entire table
array which you've selected. With INDEX-MATCH, Excel only has to consider the
lookup column and the return column.

Using the below table, let’s see how you can find the city where Jenson resides.

Fig: Index-Match function in Excel

Now, let’s find the department of Zampa.

24. COUNTIF

The function COUNTIF() is used to count the total number of cells within a range that
meet the given condition.

Below is a coronavirus sample dataset with information regarding the coronavirus

cases and deaths in each country and region.

Let’s find the number of times Afghanistan is present in the table.

Fig: Countif function in Excel

The COUNTIFS function counts the number of cells specified by a given set of
conditions.

If you want to count the number of days in which the cases in India have been
greater than 100. Here is how you can use the COUNTIFS function.
25. SUMIF

The SUMIF() function adds the cells specified by a given condition or criteria.

Below is the coronavirus dataset using which we will find the total number of cases in
India till 3rd Jun 2020. (Our dataset has information from 31st Dec 2020 to 3rd Jun
2020).

Fig: Sumif function in Excel

The SUMIFS() function adds the cells specified by a given set of conditions or
criteria.

Let’s find the total cases in France on those days when the deaths have been less
than 100.

26. Goal Seek

Goal Seek is a function in-built in Advanced Excel Functions that allows you to get
the desired output by changing the assumptions. The process is dependent on the
trial and error method to achieve the desired result.

Let’s look at an example to understand it better.

Example

In this example, we aim to find what will be the rate of interest if the person wants to
pay

$5000 per month to settle the loan amount.

PMT function is used when you want to calculate the monthly payment you need to
pay to settle the loan amount.

Let’s go through this problem in steps to see how we can calculate the interest rate
that will settle a loan of $400,000 by $5,000 a month payment.

 PMT formula should now be entered in the cell that is the Payment cell adjacent.
Currently, there is no value in the rate of interest cell, Excel gives us the payment of
$3,333.33 because it assumes the rate of interest to be 0%. Ignore it.

 Go to Data > What - If Analysis > Goal Seek

  Set the monthly payment to -5,000. The deduction in amount signifies the negative
value.

Set rate of interest as the changing cell.

 Click OK. You will see the goal seek function automatically gives the interest rate that
is required to pay the loan amount.

Go to Home > Number and change the value to Percentage.

Your outcome will look like below:

27. What-If Analysis with Solver

What-If Analysis is the method of changing the values to try out different scenarios
for formulas in Advanced excel.

Several different sets of values can be used in one or multiple of these Advanced
excel formulas to explore the different results.

A solver is ideal for what-if analysis. It is an add-in program in Microsoft Excel and is
helpful on many levels. The feature can be used to identify an optimal value for a
formula in the cell known as the objective cell. Some constraints or limits are
however applicable on other formula cell values on a worksheet.

Solver works with decision variables which are a group of cells used in computing
the formulas in the objective and constraint cells. The solver adjusts the value of
decision variable cells to work on the limits on constraint cells. This process aids in
determining the desired result for the objective cell.

Also Read: The Best Guide to Build an Excel Dashboard

Activating Solver Add-in

 On the File tab, click Options.

 Go to Add-ins, select Solver Add-in, and click on the Go button.
 Check Solver Add-in and click OK.
  In the Data tab, in the Analyze group, you can see the Solver option is added.

How to Use Solver in Excel

In this example, we will try to find the solution for a simple optimization problem.

Problem: Suppose you are the business owner and you want your income to be
$8000.

Goal: Calculate the units to be sold and price per unit to achieve the target.

For example, we have created the following model:

 On the Data tab, in the Analysis group, click the Solver button.
 In the set objective, select the income cell and set its value to $8000.
 To Change the variable cell, select the C5, C6, and C10 cells.
  Click Solve.

Your data model will change according to the conditions.

28. If-Else

IF function is used to test the condition and return a value if the condition is indeed
true and a predetermined different value if it turns out to be false.

If-Else =IF(test, true result, false result)

29. If-Error

The Excel IFERROR function returns an alternative result when a formula generates
an error and an expected result when no error is detected.

If-Error =IFERROR (value, value_if_error)

For example, Excel returns a divide by zero error when a formula tries to divide a
number by 0.

By using the IFERROR function, you can add a message if the formula evaluates to
an error.

30. Index and Match

This is an Advanced Excel function. MATCH function is designed to return the

position of a value in a specified range, while the INDEX function returns a specific
value present in a uni-dimensional range.
The MATCH function returns the position of the ID you are looking for. The INDEX
function will return the value of the salary corresponding to the position.

31. Offset Function

The OFFSET function returns a reference to a range of cells that is a specified

number of rows and columns from a cell or range of cells.

Offset Function =OFFSET(reference, rows, cols, [height], [width])

Example:

Consider the following data:

To reference C4 starting at A1, reference is A1, rows is 3 and cols is 2:

SUM Function With OFFSET

In this example we have a monthly sales data of two years. The goal is to find the
sum of sales for a specific month.

The OFFSET function returns a 1x2 range, 8 rows below cell A2, and 1 column right
of cell A2. The SUM function then calculates the sum of this range.

FAQs
1. What are the basic formulas in Excel?

Basic formulas in Excel include arithmetic operations like addition, subtraction,

multiplication, and division—for example, SUM, AVERAGE, COUNT, and
PRODUCT.

2. What is MS Excel formulas and functions?

MS Excel formulas and functions are expressions used to perform calculations or

manipulate data in Excel. Formulas start with an equal sign (=) and can contain
functions, mathematical operations, cell references, and constants.

3. How to write a formula in Excel?

To write a formula in Excel, start with an equal sign (=), followed by the formula
expression. For example, to add two numbers in cells A1 and B1, write "=A1+B1" in
another cell.

4. What are basic Excel skills?

Basic Excel skills include:

 Entering data.
 Formatting cells.
 Using basic formulas and functions.
 Creating simple charts.
 Sorting and filtering data.
  Understanding cell references.

5. What is VLOOKUP in Excel?

VLOOKUP is a function in Excel used to search for a value in the first column of a
table range and return a related value from a specified column. It's commonly used
for data lookup and retrieval.

6. What is a formula in Excel?

An Excel formula is a mathematical expression that works with values in a specific

range of cells. These formulas yield a result, even if it's an error. They empower you
to carry out addition, subtraction, multiplication, and division calculations within
Excel.

7. What are the 5 important formulas in Excel?

Some important Excel formulas include:

1. SUM: The SUM formula adds the values in a range of cells.

2. AVERAGE: The AVERAGE formula averages the values in a range of cells.
3. COUNT: The COUNT formula calculates the quantity of cells that hold numeric
values within a designated range of cells.
4. IF: The IF formula assesses a condition and provides a specific output if the condition
is true, or an alternative output if the condition is false.
5. VLOOKUP: The VLOOKUP formula searches for a value in a table and returns the
corresponding value from another column in the table.
 87 Excel Tips and Tricks: From Beginners
to Pros
Published November 28, 2023

Written BySusan Harkins

Table of Contents

 How to use Excel dropdown lists

 How to use macros in Excel
 How to use Excel pivot tables
 How to manipulate data in Excel
 How to use styles and visuals in Excel
 How to use functions in Excel
 How to use numbers and calculations in Excel
 How to use conditional formatting in Excel
 More Excel tips, tricks and downloads

Discover 87 Excel tips and tricks that will take you from a beginner to
a pro. Improve your efficiency, productivity and skills with these
helpful Excel techniques.

Microsoft Excel was first released in 1985, and the spreadsheet program
has remained popular through the years. You can master Excel by reading
these tips and tricks on how to add a dropdown list in an Excel cell to find
duplicates, how to delete blank rows in Excel and more.

How to use Excel dropdown lists

How to add a drop-down list to an Excel cell
Drop-down lists can greatly facilitate data entry. Here’s a look at how to use
Excel’s data validation feature to create handy lists within your worksheets.

How to add a condition to a drop down list in Excel

Learn this quick and easy method for adding a condition to a data
validation list in Excel.

How to add color to a dropdown list in Excel

This tutorial shows the steps for adding color to a dropdown list in Microsoft
Excel.
How to create an Excel drop down list from another tab
Here’s a quick tip for creating a Microsoft Excel drop down list from another
tab.

How to use UNIQUE() to populate a dropdown in Microsoft Excel

Populating a dropdown with a dynamic list is easy thanks to Microsoft
Excel’s UNIQUE() dynamic array function.

How to use macros in Excel

How to use VBA procedures to generate a list of sheet names in an
Excel workbook
Use one or both of these simple Microsoft Excel macros to list all the
sheets in an Excel workbook.

How to add a timestamp to an Excel record

Like many Excel tasks, there’s an easy way and a better way to enter a
timestamp for your records. Learn about both in this article.

How to send a scheduled Microsoft Excel report email using Power

Automate
If the boss wants to review a Microsoft Excel report every day at the same
time, don’t worry: You can use a Power Automate flow to automatically
send that report on time.

How to convert a birth date to an age without an expression using

Microsoft Excel Power Query
In Microsoft Excel, you don’t need an expression to calculate someone’s
age — use Power Query to simplify the process.

How to use Excel pivot tables

How to filter a pivot table in Excel
Pivot tables generate great reports in Microsoft Excel, but adding a filter or
two can make them even more flexible. Here’s how.

How to add conditional formatting to a Microsoft Excel PivotTable

without expressions
Learn how to combine a pivot table with a slicer and conditional formatting
to provide on-the-fly highlights.

4 tips for refreshing Excel PivotTable objects

Up-to-date information can be critical; these 4 tips will add flexibility and
increase efficiency when refreshing pivot tables.
How to combine Excel VLOOKUP() and PivotTable for simple
solutions
Combining features often extends the flexibility and efficiency of your
solution. In this article, we used the VLOOKUP() function to add an
important detail to a tracking list.

How to display the top n records in an Excel PivotTable using built-in

options
To display specific values in a PivotTable in Microsoft Excel, use one of the
many built-in filters, such as Top 10.

How to convert a Microsoft Excel slicer into a series of filtering

buttons on a PivotChart
Slicers are a good way to filter data in a Microsoft Excel PivotTable or
PivotChart, but they take up a bit of room. By moving the buttons to the
chart, you free up room for more visuals.

How to manipulate data in Excel

How to conditionally return the last value in a column in Excel
Returning the last value based on a condition seems like a difficult problem
to solve unless you try one of these two easy-to-implement solutions.

A quick way to delete blank rows in Excel

Deleting blank rows in an Excel data range is easy with this technique, but
watch out for unintended consequences.

Six ways to remove blank rows from an Excel worksheet (free PDF)
Blank rows can find their way into your worksheets through various means
— but no matter how they get there, it’s a good idea to get rid of them. This
e-book walks through five manual techniques for deleting blank rows and
then winds up with a macro-based approach.

Copy an Excel sheet from one workbook to another

Susan Harkins shows you two quick ways to copy data from one Excel
workbook to another.

How to average unique values in Excel the easy way

If you need to average a list of values in Microsoft Excel that contain
duplicates without including the duplicates, don’t fret over a complex
expression when you can easily remove those duplicates from the
calculation.
How to parse data in Microsoft Excel
Simplify your data transformation by skipping complex parsing expressions
and instead using Flash Fill and Power Query in Microsoft Excel.

How to Extract Delimited Data Using Excel Power Query

Learn how to use Excel Power Query’s extract and split column features to
extract delimited strings into their components with this step-by-step
tutorial.

How to use styles and visuals in Excel

How to limit the columns in a filtered result set in Microsoft Excel
Microsoft Excel’s Advanced Filter feature displays records that match
specific criteria. You can also limit the columns returned by this feature.

How to easily print Excel sheets in black and white

Printing a colorized Excel spreadsheet in black and white is easy, whether
you do so manually or use a short VBA procedure. Here’s how.

How to create a fun people chart in Excel

Is your chart boring? Try Excel’s people chart to liven things up. Susan
Harkins shows you how.

How to force a consistent phone number format in Microsoft Excel

Combine an Excel custom format with data validation and remove the
pressure for input perfection from personnel by letting Excel do the work.

How to use Excel styles efficiently

Learn what styles are and how they can help you format your sheets more
effectively and efficiently.

How to create a burndown chart in Microsoft Excel

Try this easy-to-implement Microsoft Excel chart to keep you and your
team members on track.

2 ways to display negative numbers in red in Microsoft Excel

Learn how to use either of these methods in Microsoft Excel to display
negative values in red.

How to use a border to discern groups more easily in Microsoft Excel

If your data is grouped in Microsoft Excel, try displaying a border between
groups. Learn how to make your Excel tables easier to read.

How to create a static view of Excel data while collaborating

When collaborating in Excel, sometimes the collaboration can be
distracting. Here’s how to make a static image so you can do your own
work without interruption.

How to suppress 0 values in an Excel chart

There isn’t a one-size-fits-all solution for removing 0 values from an Excel
chart. Here’s a look at a few methods.

How to Adjust Text to Fit in Excel Cells With 3 Methods

Trying to make all of the text or words fit properly in your Excel
spreadsheet? Learn how to make text fit in Excel with our tutorial.

How to use Excel’s find feature to highlight or delete matching values

Excel’s Find feature offers more than just finding values if you know the
right steps.

How to use Find All to manipulate specific matching values in Excel

Filters are great tools, but you can’t remove specific items from the results.
When you need to do this, try Find All in Excel instead.

How to use functions in Excel

How to use EOMONTH() to return the last day of the month and more
in Excel
There’s more to EOMONTH() in Microsoft Excel than the last day of the
month. Learn how to put it to use in your spreadsheets.

How to turn complex formulas into easy-to-use custom functions

using LAMBDA() in Excel
In Excel spreadsheets, complex formulas are difficult to enter without
making mistakes. Microsoft Excel’s LAMBDA() function can make such
errors easier to find.

How to parse time values in Microsoft Excel

Using three Microsoft Excel functions, you can easily parse time
components from a full date value.

How to split a column using an IF() function in Excel

If you need to conditionally split values into multiple columns in Microsoft
Excel, consider using the IF() function. Here’s how.

How to combine Excel’s VLOOKUP() function with a combo box for

enhanced searching
A combo box’s autocomplete feature linked to a lookup function creates a
flexible search tool.
How to use the newish XLOOKUP() dynamic array function in Excel
Microsoft Excel’s dynamic array function XLOOKUP() might completely
replace VLOOKUP() and HLOOKUP().

How to use UNIQUE() to populate a dropdown in Microsoft Excel

Populating a dropdown with a dynamic list is easy thanks to Microsoft
Excel’s UNIQUE() dynamic array function.

How to use numbers and calculations in Excel

4 ways to multiply in Microsoft Excel
There’s more than one method for multiplying in Microsoft Excel, and one
of them doesn’t require an expression.

How to enter and display fractions in Excel

Entering fractions in Microsoft Excel isn’t exactly intuitive, but it is easy.
Learn how to enter and display fractions as either fractions or as decimal
values.

How to create a floating bar chart in Excel

Making a floating bar chart in Microsoft Excel is a great way to visually
represent distribution between entities. Susan Harkins will show you how.

How to calculate a conditional running total using a PivotTable in

Excel
At the sheet level, conditional running totals require focused expressions,
but an Excel PivotTable requires only a few field swaps. Susan Harkins
shows you how.

How to calculate conditional rank in Excel

A straight ranking result is easy using one of Microsoft Excel’s ranking
functions. Calculating a conditional rank is even easier if you let an Excel
PivotTable do all the work.

How to subtotal transactions by conditional date components in Excel

If you need a monthly or yearly balance for revenue transactions in
Microsoft Excel, you’re in luck because both are easy! Here’s how to find
the totals you need.

How to do more advanced averaging in Excel

Averaging values in an Excel sheet is easy. But perhaps you want to ignore
zeros or include multiple sheets. Knowing how each averaging function
works is the key to choosing the right one.
How to average with and without the highest and lowest values in
Excel
Averaging in Microsoft Excel is easy, until you start excluding specific
values. Here are three ways to average a data set when giving special
consideration to the highest and lowest values.

How to calculate bonuses and commissions in Excel

Everyone likes a bonus, but sometimes calculating one can be a bit
complicated — at first. Here’s how to calculate the amount in Microsoft
Excel that you or your staff will get paid.

How to find the minimum and maximum values within a specified set
of years in Excel
Use formulaic conditional rules in Microsoft Excel to highlight the smallest
and largest values within a period of years.

How to copy expressions without changing cell references in Excel

Microsoft Excel updates cell references when you copy an expression.
Here are a couple of workarounds for those rare occasions when you don’t
want to change the cell references.

How to handle VBA’s four most common errors in Microsoft 365 apps
Correcting Microsoft VBA code is easier if you understand what the basic
error messages mean.

How to highlight unique values in Excel

The easiest way to spot a unique value is to format it, and Microsoft Excel
offers two ways to do so.

3 ways to suppress zero in Excel

If you don’t want to display zeros, use one of these quick and easy
methods to suppress them in Microsoft Excel.

Use Excel to calculate the hours worked for any shift

With Microsoft Excel, you can create a worksheet that figures the hours
worked for any shift. Follow these step-by-step instructions.

Four ways to protect your Excel formulas (free PDF)

If a user inadvertently changes the formulas in your Excel workbooks, all
your hard work could go out the window. This ebook explains how to
protect and hide those formulas so they remain safe and intact.

How to determine the number of remaining workdays for Microsoft

Excel projects
Knowing how many days you’ve allotted to a project is important, but once
you’re into the project, knowing how many days remain might be more
important.

How to parse data in Microsoft Excel

Simplify your data transformation by skipping complex parsing expressions
and instead using Flash Fill and Power Query in Microsoft Excel.

How to Extract Delimited Data Using Excel Power Query

Learn how to use Excel Power Query’s extract and split column features to
extract delimited strings into their components with this step-by-step
tutorial.

How to use conditional formatting in Excel

How to use conditional formatting to highlight due dates in Excel
The ramifications of missing a due date can range from simply adjusting
the date to getting fired. Don’t take chances with deadlines when a simple
conditional format can remind you.

How to use Excel’s conditional formatting to make larger values more

readable
Reporting lots of large values in Excel is probably a good thing, but if you
want them to be readable, try this easy technique.

How to add a conditional format that highlights groups in Excel

Learn how to add a highlight to expose groups in your Microsoft Excel data
using a helper column and Excel’s conditional formatting feature.

How to use the selected value in a combo to determine conditional

formatting in Excel
Thanks to the linked cell property in Microsoft Excel, it’s super easy to use
the selected value as a condition in a macro or conditional formatting rule.

How to highlight the top n values in a Microsoft Excel sheet

This formulaic conditional formatting Excel rule will let viewers determine
how many top values to view on the fly.

How to avoid a conditional formatting rule in Excel that doesn’t work

as expected
Formulaic conditional formatting rules in Microsoft Excel can be tricky, so
learn how to avoid a common mistake.

More Excel tips, tricks and downloads

How to enter data quickly in Excel
Microsoft Excel users, follow these two easy methods to reduce data entry
time and errors when customized features aren’t available.

How to quickly import a .txt or .csv file into Microsoft Excel

Microsoft Excel has an import wizard, but if you structure the text correctly,
you can bypass the wizard altogether.

How to use hyperlinks to move quickly between sheets in Microsoft

Excel
The busier a Microsoft Excel workbook is, the more ways you need to get
around in it. Check out these shortcuts to help you move between sheets.

5 ways to rename a sheet in Microsoft Excel

Renaming an Excel sheet is a quick and easy task, but there’s more than
one way to do it. Susan Harkins will show you how.

How to extract the date and time from a serial date in Excel
If you have to work with a date stamp in Microsoft Excel that includes date
and time, you can use these simple expressions to extract both
components, making them easier to work with.

6 shortcuts for working with Table objects in Excel

Use these six shortcuts to select Table elements and insert columns and
rows quickly in Microsoft Excel, making your use of Tables that much
easier.

How to use Find All to manipulate specific matching values in Excel

Filters are great tools, but you can’t remove specific items from the results.
When you need to do this, try Find All in Excel instead.

6 ways to save time using Flash Fill in Microsoft Excel

Source data won’t always come in the form you need in Excel. When that
happens, consider using Flash Fill to save time and aggravation.

How to use sheet view for more flexible collaboration in Excel

Whether you’re tired of losing your spot when collaborating or you want to
customize the way you view your data, sheet views in Microsoft Excel are
for you.

How to use shortcuts to sort in Microsoft Excel

If you run sorts in Excel a lot, you might benefit from shortcuts or even a
macro. Here are some ways to make your sorting life easier.
How to use passwords to grant users access to different Microsoft
Excel workbook ranges
If multiple users work in the same Microsoft Excel file, you can limit their
access to only the ranges where they need to work.

How to transfer data from Word forms to an Excel worksheet

Avoid the hassle of manually importing Word form data into Excel. With the
help of an Excel wizard, you can quickly step through the process.

How to find duplicates in Excel

You’ll need more than one trick up your sleeve to find duplicates in
Microsoft Excel.

How to fix common printing problems in Microsoft Excel

Printing Microsoft Excel spreadsheets can be tricky, but you can avoid
most printing problems by following these tips.

How to highlight details for better insight with sparkline charts in

Excel
Sparklines are in-cell charts in Microsoft Excel that are easy to create and
extremely helpful, but they can be more insightful with just a few specific
settings.

How to turn ordinary sparklines into meaningful information with a

few simple formats
Sparklines are a great visual tool, but you can increase their impact with a
little simple formatting.

10 handy ways to get more from Excel (free PDF)

The tips, tricks and shortcuts in this ebook will help you get extra mileage
from Excel’s powerful features, generate accurate results and save time on
your worksheet tasks.

How to transfer data from Word forms to an Excel worksheet

Avoid the hassle of manually importing Word form data into Excel. With the
help of an Excel wizard, you can quickly step through the process.

How to use Excel’s what-if tools to analyze business scenarios (free

PDF)
Excel offers three what-if analysis tools that can sharpen your decision-
making and help you find the best route to accomplish your objectives. This
e-book introduces these tools — Goal Seek, Scenario Manager and Data
Tables — and demonstrates how you can put them to work. Sample files
are included in the download.
These new Excel features for working with text and lists will save you
time
Often known as the universal data munging tool, Excel is trying out new
options for processing messy text as well as entering it more quickly and
accurately in the first place.
Excel Workbook For Dummies Cheat Sheet
By: Greg Harvey
Updated: 11-30-2021
From The Book: Excel Workbook For Dummies

Perform Excel File menu commands with handy hot keys

You activate the Excel hot keys by pressing the Alt key before you type the various
sequences of mnemonic letters. The mnemonic letter for all the commands on the
Excel File menu in the Backstage View is F (for File). Therefore, all you have to
concentrate on learning in the following table is the second letter in the File menu hot
key sequence.

Unfortunately, not all these second letters are as easy to associate and remember as
Alt+F. For example, check out the Account option hot key sequence — Alt+FD —
where the second mnemonic letter (D) doesn’t occur anywhere in the option name!

Hot Excel Ribbon Function

Keys Command

Alt+FH File→Home Displays the Home panel, where you can

choose a template, or open a recent or
pinned workbook

Alt+FN File→New Displays the Available Templates panel in

the Backstage View box where you can open
a blank workbook or one from a template

Alt+FO File→Open Displays the Open dialog box in the regular

worksheet view where you can select a new
Excel workbook to open for editing or printing

Alt+FI File→Info Displays the Information panel in the

Backstage View where you can see a
preview of the current worksheet along with
statistics about the workbook as well as
protect the workbook, check the file for
compatibility issues, and manage different
versions created by the AutoRecover feature
Alt+FS File→Save Saves changes to a workbook. When
you first select this command for a new
workbook, Excel displays the Save As
dialog box

Alt+FA File→Save As Displays the Save As dialog box in the

regular worksheet view where you can
modify the filename, location where the
file is saved, and format that the file is
saved in

Alt+FA File→Save a For a OneDrive file, displays the Save a

Copy Copy screen in the Backstage view
where you choose the OneDrive folder
to save a copy of the file, the filename,
and the format in which the file is to be
saved

Alt+FP File→Print Displays the Print panel in the Backstage

View where you can change the print
settings before sending the current
worksheet, workbook, or cell selection to the
printer

Alt+FH File→Share Displays the Share panel in the Backstage

View where you can send the current
workbook as an e-mail attachment or fax it
using Internet Fax, attach it to an e-mail as a
PDF file, save it in a new file format, or save
it online to your company’s SharePoint site
or your own Windows Live SkyDrive

Alt+FE File→Export Displays the Export screen in the Backstage

view where you can change the workbook
file type or convert it to an Adobe PDF or
Microsoft XPS document

Alt+FU File→Publish Enables you to upload all or part of your

workbook to Microsoft Power BI (Business
Intelligence), a standalone program that
enables you to create rich visual reports and
dashboards for your Excel data

Alt+FC File→Close Closes the current workbook without

exiting Excel

Alt+FD File→Account Displays the Account screen in the

Backstage view where you can modify
your user information, select a new
background and theme for all Office
 programs, add connected storage
services, and get the product ID and
other information on your version of
Office

Alt+FK File→Feedback Displays the Feedback screen in the

Backstage view where you can send
Microsoft your comments about Excel
features you like and dislike as well as
make suggestions for new features and
other improvements

Alt+FI File→Options Displays the Excel Options dialog box in

the regular worksheet view where you
can change default program settings,
modify the buttons on the Quick Access
toolbar, and customize the Excel Ribbon

Alt+FX File→Exit Excel Quits the Excel program and closes all
open workbooks after prompting you to
save them

Common Excel hot keys to perform formula commands

All the hot key sequences for selecting the most common formula-related commands
in Excel begin with the sequence Alt+M because the M in forMulas was the only
mnemonic key still available (F was already assigned to the File menu commands).

Hot Keys Excel Ribbon Command Function

Alt+MF Formulas→Insert Function Opens the Insert Function

dialog box (same as
clicking the Insert Function
button on the Formula bar

Alt+MUS Formulas→AutoSum→Sum Selects the occupied

range above the cell
cursor and inserts SUM
formula to total the range

Alt+MUA Formulas→AutoSum→Average Selects the occupied

range above the cell
cursor and inserts
AVERAGE formula to
calculate the average of
total in the range
Alt+MUC Formulas→AutoSum→Count Selects the occupied
Numbers range above the cell
cursor and inserts COUNT
formula to count the
number of values in the
range

Alt+MI Formulas→Financial Opens a drop-down menu

listing all Financial
functions — click name to
insert function into current
cell

Alt+ME Formulas→Date & Time Opens a drop-down menu

listing all Date and Time
functions — click name to
insert function into current
cell

Alt+MN Formulas→Name Manager Opens Name Manager

dialog box showing all
range names in workbook
where you can add, edit,
and delete names

Alt+MMD Formulas→Define Name Opens New Name dialog

box where you can assign
a name to the cell
selection or define a new
constant

Alt+MS Formulas→Use in Formula Displays drop-down menu

with range names in
workbook that you can
insert into current formula
by clicking

Alt+MC Formulas→Create from Selection Opens Create Names from

Selection dialog box where
you indicate which rows
and columns to use in
naming cell selection

Alt+MH Formulas→Show Formulas Displays and then hides all

formulas in cells of the
worksheet

Alt+MXA Formulas→Options→Automatic Turns automatic

recalculation back on
 Alt+MXE Formulas→Options→Automatic Turns automatic
Except for Data Tables recalculation back on for
all parts of the worksheet
except for ranges with
Data Tables

Alt+MXM Formulas→Options→Manual Turns on manual

recalculation

Alt+MB Formulas→Calculate Now Recalculates formulas

throughout the entire
workbook when manual
recalculation is turned on

Alt+MJ Formulas→Calculate Sheet Recalculates formulas in

the current worksheet
when manual recalculation
is turned on

Excel view commands via hot keys

The mnemonic letter for all the view-related commands in Excel is W (the last letter
you see in vieW).

Hot Keys Excel Ribbon Function

Command

Alt+WL View→Normal Returns the worksheet to normal view from

Page Layout or Page Break Preview

Alt+WP View→Page Puts the worksheet into Page Layout View

Layout showing the page
breaks, margins, and rulers

Alt+WI View→Page Puts the worksheet into Page Break

Break Preview Preview showing page breaks that you can
adjust

Alt+WE View→Full Puts the worksheet in full-screen mode

Screen which hides the File Menu, Quick Access
toolbar, and Ribbon — press the Esc key to
restore previous viewing mode

Alt+WVG View→Gridlines Hides and redisplays the row and column

gridlines that form the cells in the
Worksheet area
 Alt+WG View→Zoom to Zooms the Worksheet area in or out to the
Selection magnification
percentage needed to display just the cell
selection

Alt+WJ View→100% Returns the Worksheet area to the default

100% magnification percentage

Alt+WN View→New Inserts a new window in the current

Window workbook

Alt+WA View→Arrange Opens the Arrange dialog box where you

All can select how workbook windows are
displayed on the screen

Alt+WF View→Freeze Opens the Freeze Panes drop-down menu

Panes where you select how to freeze rows and
columns in the Worksheet area: Freeze
Panes (to freeze all the rows above and
columns to the left of the cell cursor);
Freeze Top Row; or Freeze First Column

Alt+WS View→Split Splits the worksheet into four panes using

the top and left
edge of the cell cursor as the vertical and
horizontal dividing
lines — press hot keys again to remove all
panes

Alt+WH View→Hide Hides the current worksheet window or

workbook

Alt+WU View→Unhide Opens the Unhide dialog box where you

can select the window or workbook to
redisplay

Alt+WB View→View Side Tiles two open windows or workbooks one

by Side above the other form comparison — press
hot keys again to restore the original full
windows

Alt+WW View→Switch Opens the Switch Windows drop-down

Windows menu where you can select the open
window or workbook to make active

Excel hot keys for editing commands

The mnemonic letter for all the editing commands is H (for Home) because all of
these commands are conveniently located on the Home tab of the Ribbon.
However, even if you can remember to associate editing with the Home tab and
keep in mind that Alt+H is always the starting point, you’re still not home free
because the remaining letters in the hot key sequences are not as easy to remember
as you might like.

Fortunately, the most common editing commands (Cut, Copy, and Paste) still
respond to the old Ctrl+key sequences (Ctrl+X, Ctrl+C, and Ctrl+V, respectively),
which are a lot quicker than their Alt+H equivalents, provided that you already know
and regularly use them.

Hot Excel Ribbon Command Function

Keys

Alt+HVP Home→Paste→Paste Pastes the currently cut or copied

cell selection or graphic objects in
the worksheet

Alt+HX Home→Cut Cuts the cell selection or selected

graphic objects out of the
workbook and places them on the
Windows Clipboard

Alt+HC Home→Copy Copies the cell selection or

selected graphic objects to the
Windows Clipboard

Alt+HFP Home→Format Painter Activates the Format Painter

Alt+HFO Home→Clipboard Dialog Displays and hides the Clipboard

Box Launcher task pane

Alt+HII Home→Insert→Insert Opens Insert dialog box so you

Cells can indicate the direction in which
to shift existing cells to make room
for the ones being inserted

Alt+HIR Home→Insert→Insert Inserts blank rows equal to the

Sheet Rows number of rows in the cell
selection

Alt+HIC Home→Insert→Insert Inserts blank columns equal to the

Sheet Columns number of columns in the cell
selection

Alt+HIS Home→Insert→Insert Inserts a new worksheet in the

Sheet workbook
Alt+HDD Home→Delete→Delete Opens Delete dialog box so you
Cells can indicate the direction in which
to shift existing cells to replace the
ones being deleted

Alt+HDR Home→Delete→Sheet Deletes rows equal to the number

Rows of rows in the cell selection

Alt+HDC Home→Delete→Sheet Deletes columns equal to the

Columns number of columns in the cell
selection

Alt+HDS Home→Delete→Sheet Deletes the current worksheet after

warning you of data loss if the
sheet contains cell entries

Alt+HEA Home→Clear→Clear All Clears the contents, formatting,

and comments from the cell
selection

Alt+HEF Home→Clear→Clear Clears the formatting of the cell

Formats selection without removing the
contents and comments

Alt+HEC Home→Clear→Clear Clears the contents of the cell

Contents selection without removing the
formatting and comments

Alt+HEM Home→Clear→Clear Clears all comments in the cell

Comments selection without removing the
formatting and contents
What are macros and how do you use
macros for Excel?
Updated 19 March 2023

A macro is a scripting tool that can help you work more efficiently in programs such as
Microsoft Excel. Macros are basic instructions that empower users to automate tasks so they
can work on other projects or complex work. Although disabled by default in most programs
to prevent attacks from macro viruses, you can enable macros from within the software. In
this article, we discuss what a macro is and outline how to enable macros in Microsoft Excel
for individual sessions and on a permanent basis, in addition to answering some frequently
asked questions.

What are macros for Excel?

Macros for Excel are instructions you can customise to complete repetitive tasks
automatically. Implementing macros in different types of software can help save users time
when working on repetitive tasks, like filling in cells. Software developers can create macros
by using the programming language Visual Basic for Applications. Macros provide an
automated input sequence that allows users to imitate keystrokes, mouse actions and other
basic inputs. Generally, macros are a way to automate repetitive tasks that are the same.
Typically, these types of inputs are common in spreadsheet software applications like Excel.
Using macros with little understanding of what they do can be problematic because nefarious
users, such as hackers, can use macros as a route of entry to install viruses, malware or
adware onto computers or networks. It's due to this threat that most programs, including
Excel, disable macros by default.

How macros improve the user experience in Excel

When working with spreadsheet software applications, such as Excel, repetitive tasks are
quite common. This can include populating hundreds of cells with similar information and
editing multiple cells with the same data points. Macros fit into the Excel framework as a
way to automate most of these tasks, freeing up users from the repetition. Essentially, macros
are a recording of your steps in Excel, which makes them suitable for use in other tasks with
the click of a button.
For example, if you handle customer accounts at the end of every working day, this would
require you to download the latest data from your bank and format it to suit your filing
requirements. If you do the same routine every day, a macro is a great way to automate the
task so that you no longer do it manually. When considering the benefits of macros, know
that they can be a useful way to automate the importing and formatting of data.
What to do before creating your first macro in Excel
Before creating your own macros in Excel, it's necessary to enable the use of macros from
within the software. For Excel, this requires you to view the developer features. This is not
visible by default, which means it's first necessary to display it through the customise report
feature. Macros stay hidden by default because of the risk they present to users who don't
know much about them.
Hackers can penetrate systems by creating nefarious macros with embedded malware or
viruses. This is why it's a good idea to always create your own macros or use macros from a
trusted source. When creating your own macros, consider saving them in your workbook
using a macro-enabled format such as .XLSM. Remember that macro file names cannot
contain any spaces or else they aren't going to work.

Enabling macros in Excel for individual sessions

Enabling macros for a single session of Excel gives you the option to use macros without
changing the default settings of Excel. Once you complete your single session, it's best to
automatically disable macro to ensure security. To enable macros in Excel for a single
session, follow these steps.
1. Open 'Excel' and the 'macro file': Once you have Excel running, open the file that
contains the macro you intend to use. Once you open this file, Excel is going to
display a yellow box with a security warning to let you know that macros are
currently disabled.
2. Click on the 'info' tab: Click on the 'file' option located at the upper left portion of
the toolbar and select the 'info' option.
3. Enable macros: From the 'security warning' option, click on 'enable content' and
you're going to see a drop down menu. From here, click 'advanced options', select
'enable content for this session' and click 'OK' to confirm that you have enabled
macros for this session.
4. Make sure to disable macros for the next session: Although it typically occurs
automatically, it's worthwhile to make sure during your next Excel session that
macros remain disabled. In this case, you're going to see the same yellow box with the
security warning.

Enabling macros permanently in Excel

If you are looking to use macros on a permanent basis in Excel, you can speak with the
system administrator to discuss the risks. You are essentially keeping a door open which
could let dangerous viruses into your network, so permanently enabling macros might be a
serious risk. If you are sure you want to keep macros on permanently in Excel, follow these
steps.
1. Open your 'Excel file': While in Excel, open the macro file that you wish to use. It's
going to have a yellow security message box to let you know that macros are
currently disabled.
 2. Locate the Trust Center: The Trust Center is where you can alter and adjust the
security and privacy settings within Excel and other Microsoft Office applications.
You can locate it by clicking 'File', then 'Options' and 'Trust Center' from the
navigation panel on the upper left portion of the screen.
3. Find ‘Macro Settings': From within the Trust Center, you can adjust settings related
to the security of data and documents. Click 'Trust Center Settings' and select 'Macro
Settings'.
4. Enable all macros: From within macro settings, click on the 'Enable all macros'
button and confirm it by clicking 'OK'. A warning message is going to appear which
tells you to avoid enabling all macros due to the risks of dangerous code affecting
your computer or network.
5. Know how to disable macros in the future: If you want to disable macros after
permanently enabling them, you can return to the Trust Center and click on 'Disable
all macros with notifications.'

Changing macro settings in Excel

Enabling macros temporarily or permanently can help you work smarter within Excel. If you
want to get more value out of using macros, you can tweak macro settings from within Excel.
To do this, follow these steps.
1. Open 'Microsoft Excel': To begin, launch Microsoft Excel on your computer to start
the software.
2. Select the 'Options' tab: On the upper left of the screen, click the 'File' tab and scroll
down until you can see the 'Options' menu. Click on it and a new window is going to
open up.
3. Find the Trust Center: Scroll through the new window until you see the 'Trust
Center' panel on the left side and click on it.
4. Change macro settings: In the Trust Center, you have several options to choose from
when changing the settings, like disabling all macros without notification, disabling
all macros with notification and disable all macros except digitally signed macros.
Once you have selected one, click 'OK' to complete the process.

Frequently asked questions about Excel and macros

Below are some frequently asked questions about Excel and macros, together with their
respective answers:

What's Excel VBA?

VBA is an acronym for Visual Basic for Applications. This is a simple programming
language that you can use in Excel through its Visual Basic Editor (VBE). You can access
this through the 'Developer' tab, which you can add to the ribbon in the interface. You can
modify the VBA code within the Excel VBA editor to change the macro itself. Additionally,
you can run and record macros in Excel without learning how to use Excel VBA.
I can't find the 'Developer' ribbon. Where can I enable it from?

This tab isn't visible by default in most Microsoft Office software applications. There are two
ways of displaying it. The first is to go to 'File' and click on 'Options', which is on the bottom
left. Click on the 'Customise Ribbon' tab and check the box next to 'Developer' on the right-
hand list. Alternatively, you can right-click somewhere on the existing ribbon's tabs and
select 'Customise Ribbon' and check the box for 'Developer' in the right-hand menu.
Please note that none of the companies, institutions or organisations mentioned in this article
are affiliated with Indeed.
How to Create Macros in Excel: Step-by-
Step Tutorial (2024)
Get ready to have your mind blown!

Because in this tutorial, you learn how to create your own macros in Excel!

That’s right! And you don’t need to know VBA (Visual Basic for Applications)!

Instead, you will use the Excel macro recording feature to send your spreadsheet experience

into overdrive!

So, read on and try it out yourself using this practice Excel workbook.

Table of Contents

 What are Excel macros?

 How to record Excel macros
 How to run an Excel macro
 Run Excel macro from a shortcut key
 Saving macro-enabled workbooks

What are Excel macros?

A macro is a small program or set of actions that you can run repeatedly. Excel macros are
used to automate repetitive tasks to save a lot of time and hassle.

For example, open and take a look at the practice Excel workbook.

Businesses would often have lists like this one. These are potential customers they might
want to reach out to and market their products.
Notice how Columns C to H are just pieces of information extracted from Columns A & B.
(Learn how to extract strings from texts in this tutorial!)

To streamline the worksheet, you can hide Columns A & B. You can also hide the rest of the
columns on the right starting from Column I.

Let’s do this using Excel macros!

How to record Excel macros

1. Click on the View tab in the Excel ribbon

2. Next, click on the Macros button on the right side of the View ribbon

3. This will open the Macros drop-down.

Click Record Macro.

4. Enter a name for your macro, something like Hide_Columns.

Excel macros can be stored in the Personal Macro Workbook. This is saved in the
system files of Microsoft Excel and macros saved here can be used in other
workbooks.

For this Excel macro tutorial, you only need to save the macros in the current Excel
file.
4. Select Store macro in: This Workbook then click the OK button.

Excel is now recording your actions to create a macro.

5. Select Columns A & B and then right-click on the highlighted Column Bar to Hide them.
6. Then select Column I and press Ctrl + Shift + Right Arrow to include all remaining
columns on the right.

7. Right-click on the highlighted Column Bar then click on Hide.

Your worksheet should now look like this:

To end the macro recording:

8. On the View ribbon, click on Macros and select Stop Recording.

Good job!

You have created your first macro in Excel!

But wait, where is the recorded macro?

To view all of the available Excel macros :

1. Select View Macros.

2. This opens the Macro window. Saved macros will be listed here and you
can Run whichever one you need.

You can also click on Edit to view the VBA code window.

3. The VBA code editor opens.

Notice the Hide_Columns Sub procedure. You don’t have to write or edit VBA code for the
macro.
Excel automatically generated each code line based on the recorded keystrokes and mouse
clicks.

The Record Macro feature is powerful enough for general spreadsheet automation needs.

But if you want to customize your own VBA macro, you can learn more about Visual Basic
for Applications (VBA) here.

Using the Developer tab

Let’s record another macro to Unhide the hidden columns.

This time, you can record the macro from the Developer tab.

The Developer tab gives you access to a lot of useful Microsoft Excel features such as
the Visual Basic Editor. It also allows you to quickly insert form controls such as buttons
and checkboxes.

However, the Developer tab is not visible in the Excel ribbon by default.

To add it:

1. Right-click on the Excel ribbon.

Select Customize the Ribbon.

2. This opens the Customize Ribbon window.

On the right side, check the Developer tab checkbox.

3. You should now see the Developer tab.

To start recording the Unhide macro:

1. Click on the Record Macro button in the Developer tab.

2. Name this macro Unhide_Columns.

3. Click OK.

The recording has started.

4. Press Ctrl + A twice to select all cells.

5. Right-click anywhere on the Column Bar then click Unhide.

6. Click on the Stop Recording macro button to finish up.

Great work!

Now you have two recorded macros that can be executed.

How to run an Excel macro

To run your macros:

1. Click on the Macros button from the Developer tab.

2. In the Macro window, select the macro Hide_Columns and click on Run.
The macro executes the actions recorded earlier and hides the unnecessary columns.

You can also run macros from the View ribbon.

Run Excel macro from the View tab

This time, run the Unhide_Columns to show all the columns.

1. On the View ribbon, click the Macros button and select View Macros.

2. Select the Unhide_Columns macro and Run it.

This unhides all the columns in the worksheet.

As you can see, the Macro window allows you to quickly run all the available macros.

But you can execute them even faster by using buttons and shortcuts

Run Excel macro from a button

For this next example, you will assign macros to buttons which will be located on top of the
table.

1. Insert 2 rows above the table headers. Select Row 1 then press Ctrl + Shift + Plus
Sign(+) twice.
2. To create a button, click on Insert > Illustrations > Shapes.

Then select the Rectangle.

3. Draw a rectangle and format it as you’d like. Label it “HIDE”.

This will be your HIDE button. Place it between columns A & B so it will be hidden with the
columns when the macro runs.

4. To assign a macro, right-click the shape and select Assign Macro.

5. In the Assign Macro window, select Hide_Columns and click OK.

The Hide button now works!

Now, do the same for the Unhide_Columns macro.

6. Create another rectangle button and label it “UNHIDE”.

7. Repeat Steps 4 & 5 but this time, assign the Unhide_Columns macro.

Alright!

Now you can quickly run your macros using the HIDE and UNHIDE buttons.

Run Excel macro from a shortcut key

It is sometimes better to run macros using a keyboard shortcut.

For this next example, you want to quickly highlight people on the list that expressed interest
in the business.

To create a macro for this:

1. Select any cell within the table.

2. On the Developer tab, toggle ON the Use Relative References button.

3. Start recording with the Record Macro button on the Developer tab.

Or, you can also click the Record Macro button on the Status Bar.

4. Name the macro Mark_Interested.

Then assign a shortcut key. For example, Ctrl + Q.

Click OK. The recording has now started.

4. Highlight the row of the Active Cell using the keyboard shortcut Shift + Space Bar.

When selecting cells or expanding selections while recording a macro, it is best to use
keyboard shortcuts.

This is so that Excel can record the selections as relative references.

For example, if you select Row 4 by clicking on the Row Bar, Excel will record this
as an absolute reference. This means it will always select Row 4 regardless of the
currently Active Cell.
When you use the Shift + Space Bar shortcut instead, it tells Excel to select the row
of the current Active Cell.
5. Apply the formatting:

 Fill using the color Green

 Change font color to White

6. End the macro recording from the Status Bar

All done!

Try to use the shortcut Ctrl + Q to quickly apply formatting to entire rows.

Saving macro-enabled workbooks

If you save the practice workbook, this window will pop up:
This is because the practice workbook is currently saved with the .xlsx file extension which
does not support macro features.

To save properly, change it to the .xlsm file extension for macro-enable workbooks.

Keep this in mind when saving your work.

Congratulations!

You are now familiar with Excel macros.

Try to record your own macros and start saving time on your work!