What is Microsoft Excel?

Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical
data. Microsoft Excel provides multiple features to perform various operations like calculations,
pivot tables, graph tools, macro programming, etc. It is compatible with multiple OS like
Windows, macOS, Android and iOS.

A Excel spreadsheet can be understood as a collection of columns and rows that form a table.
Alphabetical letters are usually assigned to columns, and numbers are usually assigned to rows.
The point where a column and a row meet is called a cell. The address of a cell is given by the
letter representing the column and the number representing a row.

Why Should I Learn Microsoft Excel?

We all deal with numbers in one way or the other. We all have daily expenses which we pay for
from the monthly income that we earn. For one to spend wisely, they will need to know their
income vs. expenditure. Microsoft Excel comes in handy when we want to record, analyze and
store such numeric data. Let’s illustrate this using the following image.

Where can I get Microsoft Excel?

There are number of ways in which you can get Microsoft Excel. You can buy it from a hardware
computer shop that also sells software. Microsoft Excel is part of the Microsoft Office suite of
programs. Alternatively, you can download it from the Microsoft website but you will have to
buy the license key.
How to Open Microsoft Excel?

Running Excel is not different from running any other Windows program. If you are running
Windows with a GUI like (Windows XP, Vista, and 7) follow the following steps.
● Click on start menu
● Point to all programs
● Point to Microsoft Excel
● Click on Microsoft Excel
Alternatively, you can also open it from the start menu if it has been added there. You can also
open it from the desktop shortcut if you have created one.
For this tutorial, we will be working with Windows 8.1 and Microsoft Excel 2013. Follow the
following steps to run Excel on Windows 8.1
● Click on start menu
● Search for Excel even before you even typing, all programs starting with what you have
typed will be listed.
● Click on Microsoft Excel
The following image shows you how to do this

Understanding the Ribbon

The ribbon provides shortcuts to commands in Excel. A command is an action that the user
performs. An example of a command is creating a new document, printing a documenting, etc.
The image below shows the ribbon used in Excel 2013.
Ribbon components explained
Ribbon start button – it is used to access commands i.e. creating new documents, saving existing
work, printing, accessing the options for customizing Excel, etc.

Ribbon tabs – the tabs are used to group similar commands together. The home tab is used for
basic commands such as formatting the data to make it more presentable, sorting and finding
specific data within the spreadsheet.

Ribbon bar – the bars are used to group similar commands together. As an example, the
Alignment ribbon bar is used to group all the commands that are used to align data together.

Understanding the worksheet (Rows and Columns, Sheets, Workbooks)

A worksheet is a collection of rows and columns. When a row and a column meet, they form a
cell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columns
are usually labelled with letters while rows are usually numbers.

A workbook is a collection of worksheets. By default, a workbook has three cells in Excel. You
can delete or add more sheets to suit your requirements. By default, the sheets are named Sheet1,
Sheet2 and so on and so forth. You can rename the sheet names to more meaningful names i.e.
Daily Expenses, Monthly Budget, etc.
Customization Microsoft Excel Environment

Personally I like the black colour, so my excel theme looks blackish. Your favourite colour could
be blue, and you too can make your theme colour look blue-like. If you are not a programmer,
you may not want to include ribbon tabs i.e. developer. All this is made possible via
customizations. In this sub-section, we are going to look at;
● Customization the ribbon
● Setting the colour theme
● Settings for formulas
● Proofing settings
● Save settings
Customization of ribbon

The above image shows the default ribbon in Excel 2013. Let’s start with customization the
ribbon, suppose you do not wish to see some of the tabs on the ribbon, or you would like to add
some tabs that are missing such as the developer tab. You can use the options window to achieve
this.

Click on the ribbon start button

Select options from the drop down menu. You should be able to see an Excel Options dialog
window
Select the customize ribbon option from the left-hand side panel as shown below
On your right-hand side, remove the check marks from the tabs that you do not wish to see on
the ribbon. For this example, we have removed Page Layout, Review, and View tab.
Click on the “OK” button when you are done.
Your ribbon will look as follows

Adding custom tabs to the ribbon

You can also add your own tab, give it a custom name and assign commands to it. Let’s add a tab
to the ribbon with the text Guru99

Right click on the ribbon and select Customize the Ribbon. The dialogue window shown above
will appear
Click on new tab button as illustrated in the animated image below
Select the newly created tab
Click on Rename button
Give it a name of Guru99
Select the New Group (Custom) under Guru99 tab as shown in the image below
Click on Rename button and give it a name of My Commands
Let’s now add commands to my ribbon bar
The commands are listed on the middle panel
Select All chart types command and click on Add button
Click on OK
Your ribbon will look as follows
Setting the colour theme
To set the color-theme for your Excel sheet you have to go to Excel ribbon, and click on à File
àOption command. It will open a window where you have to follow the following steps.

1. The general tab on the left-hand panel will be selected by default.

2. Look for colour scheme under General options for working with Excel
3. Click on the colour scheme drop-down list and select the desired colour
4. Click on OK button

Settings for formulas

This option allows you to define how Excel behaves when you are working with formulas.
You can use it to set options i.e. autocomplete when entering formulas, change the cell
referencing style and use numbers for both columns and rows and other options.
If you want to activate an option, click on its check box. If you want to deactivate an option,
remove the mark from the checkbox. You can this option from the Options dialogue window
under formulas tab from the left-hand side panel

Proofing settings

This option manipulates the entered text entered into excel. It allows setting options such as the
dictionary language that should be used when checking for wrong spellings, suggestions from the
dictionary, etc. You can this option from the options dialogue window under the proofing tab
from the left-hand side panel

Save settings
This option allows you to define the default file format when saving files, enable auto recovery
in case your computer goes off before you could save your work, etc. You can use this option
from the Options dialogue window under save tab from the left-hand side panel

Important Excel shortcuts

Ctrl + P used to open the print dialogue window
Ctrl + N creates a new workbook
Ctrl + S saves the current workbook
Ctrl + C copy contents of current select
Ctrl + V paste data from the clipboard
SHIFT + F3 displays the function insert dialog window
SHIFT + F11 Creates a new worksheet
F2 Check formula and cell range covered

About OneDrive

Whenever you're opening or saving a workbook, you'll have the option of using your OneDrive,
which is the online file storage service included with your Microsoft account. To enable this
option, you'll need to sign in to Office. To learn more, visit our lesson on Understanding
OneDrive.
To create a new blank workbook:

​ Select the File tab. Backstage view will appear.

 ​ Select New, then click Blank workbook.

​ A new blank workbook will appear.

To open an existing workbook:

In addition to creating new workbooks, you'll often need to open a workbook that was previously
saved. To learn more about saving workbooks, visit our lesson on Saving and Sharing
Workbooks.
​ Navigate to Backstage view, then click Open.

​ Select Computer, then click Browse. You can also choose OneDrive to
open files stored on your OneDrive.
 ​ The Open dialog box will appear. Locate and select your workbook,
then click Open.

If you've opened the desired workbook recently, you can browse your Recent Workbooks rather
than search for the file.
Saving and sharing Workbook
Whenever you create a new workbook in Excel, you'll need to know how to save it in order to
access and edit it later. As with previous versions of Excel, you can save files locally to your
computer. You can also save a workbook to the cloud using OneDrive, as well as export and
share workbooks with others directly from Excel.

About OneDrive

Whenever you're opening or saving a workbook, you'll have the option of using your OneDrive,
which is the online file storage service included with your Microsoft account. To enable this
option, you'll need to sign in to Office. To learn more, visit our lesson on Understanding
OneDrive.
Save and Save As

Excel offers two ways to save a file: Save and Save As. These options work in similar ways,
with a few important differences:

​ Save: When you create or edit a workbook, you'll use the Save command
to save your changes. You'll use this command most of the time. When
you save a file, you'll only need to choose a file name and location the
first time. Then you can just click the Save command to save it with the
same name and location.
​
​ Save As: You'll use this command to create a copy of a workbook while
keeping the original. When you use Save As, you'll need to choose a
different name and/or location for the copied version.
To save a workbook:

It's important to save your workbook whenever you start a new project or make changes to an
existing one. Saving early and often can prevent your work from being lost. You'll also need to
pay close attention to where you save the workbook so it will be easy to find later.

​ Locate and select the Save command on the Quick Access Toolbar.

​ If you're saving the file for the first time, the Save As pane will appear in
Backstage view.
​ You'll then need to choose where to save the file and give it a file name.
To save the workbook to your computer, select Computer, then click
Browse. You can also click OneDrive to save the file to your OneDrive.
 ​ The Save As dialog box will appear. Select the location where you want
to save the workbook.
​ Enter a file name for the workbook, then click Save.

​ The workbook will be saved. You can click the Save command again to
save your changes as you modify the workbook.

You can also access the Save command by pressing Ctrl+S on your keyboard.

Using Save As to make a copy

If you want to save a different version of a workbook while keeping the original, you can create
a copy. For example, if you have a file named Sales Data, you could save it as Sales Data 2 so
you'll be able to edit the new file and still refer back to the original version.

To do this, click the Save As command in Backstage view. Just like when saving a file for the
first time, you'll need to choose where to save the file and give it a new file name.
To change the default save location:

If you don't want to use OneDrive, you may be frustrated that OneDrive is selected as the
default location when saving. If you find it inconvenient to select Computer each time, you can
change the default save location so Computer is selected by default.

​ Click the File tab to access Backstage view.

​ Click Options.

​ The Excel Options dialog box will appear. Select Save, check the box
next to Save to Computer by default, then click OK. The default save
 location will be changed.

AutoRecover

Excel automatically saves your workbooks to a temporary folder while you are working on them.
If you forget to save your changes or if Excel crashes, you can restore the file using
AutoRecover.

To use AutoRecover:
 ​ Open Excel. If autosaved versions of a file are found, the Document
Recovery pane will appear.
​ Click to open an available file. The workbook will be recovered.

By default, Excel autosaves every 10 minutes. If you are editing a workbook for less than 10
minutes, Excel may not create an autosaved version.

If you don't see the file you need, you can browse all autosaved files from Backstage view. Just
select the File tab, click Manage Workbook, then choose Recover Unsaved Workbooks.
Exporting workbooks

By default, Excel workbooks are saved in the .xlsx file type. However, there may be times when
you need to use another file type, like a PDF or Excel 97-2003 workbook. It's easy to export
your workbook from Excel to a variety of file types.
To export a workbook as a PDF file:

Exporting your workbook as an Adobe Acrobat document, commonly known as a PDF file,
can be especially useful if you're sharing a workbook with someone who does not have Excel. A
PDF will make it possible for recipients to view but not edit the content of your workbook.

​ Click the File tab to access Backstage view.

​ Click Export, then select Create PDF/XPS.
 ​ The Save As dialog box will appear. Select the location where you want
to export the workbook, enter a file name, then click Publish.

By default, Excel will only export the active worksheet. If you have multiple worksheets and
want to save all of them in the same PDF file, click Options in the Save As dialog box. The
Options dialog box will appear. Select Entire workbook, then click OK.
Whenever you export a workbook as a PDF, you'll also need to consider how your workbook
data will appear on each page of the PDF, just like printing a workbook. Visit our Page Layout
and Printing lesson to learn more about what to consider before exporting a workbook as a PDF.

To export a workbook to other file types:

You may also find it helpful to export your workbook to other file types, like an Excel 97-2003
workbook if you need to share with people using an older version of Excel, or a .CSV file if you
need a plain-text version of your workbook.

​ Click the File tab to access Backstage view.

​ Click Export, then select Change File Type.
​ Select a common file type, then click Save As.
 ​ The Save As dialog box will appear. Select the location where you want
to export the workbook, enter a file name, then click Save.

You can also use the Save as type: drop-down menu in the Save As dialog box to save
workbooks in a variety of file types.
Sharing workbooks

Excel makes it easy to share and collaborate on workbooks using OneDrive. In the past, if you
wanted to share a file with someone, you could send it as an email attachment. While convenient,
this system also creates multiple versions of the same file, which can be difficult to organize.

When you share a workbook from Excel, you're actually giving others access to the exact same
file. This lets you and the people you share with edit the same workbook without having to
keep track of multiple versions.
In order to share a workbook, it must first be saved to your OneDrive.

To share a workbook:

​ Click the File tab to access Backstage view, then click Share.

​ Excel will return to Normal view and open the Share panel on the right
side of the window. From here, you can invite people to share your
document, see a list of who has access to the document, and set whether
they can edit or only view the document.
Cell Basics

Whenever you work with Excel, you'll enter information—or content—into cells. Cells are the
basic building blocks of a worksheet. You'll need to learn the basics of cells and cell content to
calculate, analyze, and organize data in Excel.

Understanding cells

Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the
intersection of a row and a column. In other words, it's where a row and column meet.

Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2, 3).
Each cell has its own name—or cell address—based on its column and row. In the example
below, the selected cell intersects column C and row 5, so the cell address is C5.

Note that the cell address also appears in the Name box in the top-left corner, and that a cell's
column and row headings are highlighted when the cell is selected.

You can also select multiple cells at the same time. A group of cells is known as a cell range.
Rather than a single cell address, you will refer to a cell range using the cell addresses of the first
and last cells in the cell range, separated by a colon. For example, a cell range that included cells
A1, A2, A3, A4, and A5 would be written as A1:A5. Take a look at the different cell ranges
below:

​ Cell range A1:A8

​ Cell range A1:F1

 ​ Cell range A1:F8

If the columns in your spreadsheet are labeled with numbers instead of letters, you'll need to
change the default reference style for Excel. Review our Extra on What are Reference Styles?
to learn how.

To select a cell:

To input or edit cell content, you'll first need to select the cell.

​ Click a cell to select it. In our example, we'll select cell D9.
​ A border will appear around the selected cell, and the column heading
and row heading will be highlighted. The cell will remain selected until
 you click another cell in the worksheet.

You can also select cells using the arrow keys on your keyboard.

To select a cell range:

Sometimes you may want to select a larger group of cells, or a cell range.

​ Click and drag the mouse until all of the adjoining cells you want to
select are highlighted. In our example, we'll select the cell range
B5:C18.
 ​ Release the mouse to select the desired cell range. The cells will remain
selected until you click another cell in the worksheet.

Cell content

Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain
different types of content, including text, formatting, formulas, and functions.
​ Text: Cells can contain text, such as letters, numbers, and dates.

​ Formatting attributes: Cells can contain formatting attributes that

change the way letters, numbers, and dates are displayed. For example,
percentages can appear as 0.15 or 15%. You can even change a cell's text
or background color.

​ Formulas and functions: Cells can contain formulas and functions that
calculate cell values. In our example, SUM(B2:B8) adds the value of
 each cell in the cell range B2:B8 and displays the total in cell B9.

To insert content:

​ Click a cell to select it. In our example, we'll select cell F9.

​ Type something into the selected cell, then press Enter on your keyboard.
The content will appear in the cell and the formula bar. You can also
 input and edit cell content in the formula bar.

To delete (or clear) cell content:

​ Select the cell(s) with content you want to delete. In our example, we'll
select the cell range A10:H10.
 ​ Select the Clear command on the Home tab, then click Clear Contents.

​ The cell contents will be deleted.

You can also use the Delete key on your keyboard to delete content from multiple cells at once.
The Backspace key will only delete content from one cell at a time.
To delete cells:

There is an important difference between deleting the content of a cell and deleting the cell
itself. If you delete the entire cell, the cells below it will shift to fill in the gaps and replace the
deleted cells.

​ Select the cell(s) you want to delete. In our example, we'll select
A10:H10.

​ Select the Delete command from the Home tab on the Ribbon.
 ​ The cells below will shift up and fill in the gaps.

To copy and paste cell content:

Excel allows you to copy content that is already entered into your spreadsheet and paste this
content to other cells, which can save you time and effort.
​ Select the cell(s) you want to copy. In our example, we'll select F9.

​ Click the Copy command on the Home tab, or press Ctrl+C on your
keyboard.

​ Select the cell(s) where you want to paste the content. In our example,
we'll select F12:F17. The copied cell(s) will have a dashed box around
 them.

​ Click the Paste command on the Home tab, or press Ctrl+V on your
keyboard.
 ​ The content will be pasted into the selected cells.

To access additional paste options:

You can also access additional paste options, which are especially convenient when working
with cells that contain formulas or formatting. Just click the drop-down arrow on the Paste
command to see these options.
Instead of choosing commands from the Ribbon, you can access commands quickly by
right-clicking. Simply select the cell(s) you want to format, then right-click the mouse. A
drop-down menu will appear, where you'll find several commands that are also located on the
Ribbon.
To cut and paste cell content:

Unlike copying and pasting, which duplicates cell content, cutting allows you to move content
between cells.

​ Select the cell(s) you want to cut. In our example, we'll select G5:G6.
​ Right-click the mouse and select the Cut command. You can also use the
command on the Home tab, or press Ctrl+X on your keyboard.

​ Select the cells where you want to paste the content. In our example,
we'll select F10:F11. The cut cells will now have a dashed box around
them.
​ Right-click the mouse and select the Paste command. You can also use
the command on the Home tab, or press Ctrl+V on your keyboard.
 ​ The cut content will be removed from the original cells and pasted into
the selected cells.

To drag and drop cells:

Instead of cutting, copying, and pasting, you can drag and drop cells to move their contents.

​ Select the cell(s) you want to move. In our example, we'll select H4:H12.
​ Hover the mouse over the border of the selected cell(s) until the mouse
changes to a pointer with four arrows.
​ Click and drag the cells to the desired location. In our example, we'll
move them to G4:G12.
 ​ Release the mouse. The cells will be dropped in the selected location.

To use the fill handle:

If you're copying cell content to adjacent cells in the same row or column, the fill handle is a
good alternative to the copy and paste commands.
​ Select the cell(s) containing the content you want to use, then hover the
mouse over the lower-right corner of the cell so the fill handle appears.
​ Click and drag the fill handle until all of the cells you want to fill are
selected. In our example, we'll select G13:G17.
 ​ Release the mouse to fill the selected cells.

To continue a series with the fill handle:

The fill handle can also be used to continue a series. Whenever the content of a row or column
follows a sequential order, like numbers (1, 2, 3) or days (Monday, Tuesday, Wednesday), the
fill handle can guess what should come next in the series. In most cases, you will need to select
multiple cells before using the fill handle to help Excel determine the series order. Let's take a
look at an example:

​ Select the cell range that contains the series you want to continue. In our
example, we'll select E4:G4.
​ Click and drag the fill handle to continue the series.

​ Release the mouse. If Excel understood the series, it will be continued in

the selected cells. In our example, Excel added Part 4, Part 5, and Part 6
to H4:J4.
You can also double-click the fill handle instead of clicking and dragging. This can be useful
with larger spreadsheets, where clicking and dragging may be awkward.

Modifying column rows and cells

By default, every row and column of a new workbook is set to the same height and width. Excel
allows you to modify column width and row height in different ways, including wrapping text
and merging cells.

To modify column width:

In our example below, column C is too narrow to display all of the content in these cells. We can
make all of this content visible by changing the width of column C.

​ Position the mouse over the column line in the column heading so the
cursor becomes a double arrow.
 ​ Click and drag the mouse to increase or decrease the column width.

​ Release the mouse. The column width will be changed.

With numerical data, the cell will display pound signs (#######) if the column is too narrow.
Simply increase the column width to make the data visible.

To AutoFit column width:

The AutoFit feature will allow you to set a column's width to fit its content automatically.
​ Position the mouse over the column line in the column heading so the
cursor becomes a double arrow.

​ Double-click the mouse. The column width will be changed

automatically to fit the content.
You can also AutoFit the width for several columns at the same time. Simply select the columns
you want to AutoFit, then select the AutoFit Column Width command from the Format
drop-down menu on the Home tab. This method can also be used for row height.

To modify row height:

​ Position the cursor over the row line so the cursor becomes a double
arrow.

​ Click and drag the mouse to increase or decrease the row height.

​ Release the mouse. The height of the selected row will be changed.

To modify all rows or columns:

Instead of resizing rows and columns individually, you can modify the height and width of every
row and column at the same time. This method allows you to set a uniform size for every row
and column in your worksheet. In our example, we will set a uniform row height.

​ Locate and click the Select All button just below the name box to select
every cell in the worksheet.
 ​ Position the mouse over a row line so the cursor becomes a double
arrow.
​ Click and drag the mouse to increase or decrease the row height, then
release the mouse when you are satisfied. The row height will be changed
for the entire worksheet.

Inserting, deleting, moving, and hiding

After you've been working with a workbook for a while, you may find that you want to insert
new columns or rows, delete certain rows or columns, move them to a different location in the
worksheet, or even hide them.

To insert rows:

​ Select the row heading below where you want the new row to appear. In
this example, we want to insert a row between rows 4 and 5, so we'll
select row 5.

​ Click the Insert command on the Home tab.

​ The new row will appear above the selected row.

When inserting new rows, columns, or cells, you will see a paintbrush icon next to the inserted
cells. This button allows you to choose how Excel formats these cells. By default, Excel formats
inserted rows with the same formatting as the cells in the row above. To access additional
options, hover your mouse over the icon, then click the drop-down arrow.

To insert columns:

​ Select the column heading to the right of where you want the new
column to appear. For example, if you want to insert a column between
columns D and E, select column E.

​ Click the Insert command on the Home tab.

​ The new column will appear to the left of the selected column.

When inserting rows and columns, make sure to select the entire row or column by clicking the
heading. If you select only a cell in the row or column, the Insert command will only insert a
new cell.

To delete a row or column:

It's easy to delete a row or column that you no longer need. In our example we'll delete a row, but
you can delete a column the same way.
 ​ Select the row you want to delete. In our example, we'll select row 9.

​ Click the Delete command on the Home tab.

​ The selected row will be deleted, and those around it will shift. In our
example, row 10 has moved up, so it's now row 9.

It's important to understand the difference between deleting a row or column and simply
clearing its contents. If you want to remove the content from a row or column without causing
others to shift, right-click a heading, then select Clear Contents from the drop-down menu.

To move a row or column:

Sometimes you may want to move a column or row to rearrange the content of your worksheet.
In our example we'll move a column, but you can move a row in the same way.

​ Select the desired column heading for the column you want to move.
 ​ Click the Cut command on the Home tab, or press Ctrl+X on your
keyboard.

​ Select the column heading to the right of where you want to move the
column. For example, if you want to move a column between columns E
and F, select column F.

​ Click the Insert command on the Home tab, then select Insert Cut Cells
from the drop-down menu.

​ The column will be moved to the selected location, and the columns
around it will shift.

You can also access the Cut and Insert commands by right-clicking the mouse and selecting the
desired commands from the drop-down menu.

To hide and unhide a row or column:

At times, you may want to compare certain rows or columns without changing the organization
of your worksheet. To do this, Excel allows you to hide rows and columns as needed. In our
example we'll hide a few columns, but you can hide rows in the same way.
​ Select the columns you want to hide, right-click the mouse, then select
Hide from the formatting menu. In our example, we'll hide columns C,
D, and E.

​ The columns will be hidden. The green column line indicates the
location of the hidden columns.

​ To unhide the columns, select the columns on both sides of the hidden
columns. In our example, we'll select columns B and F. Then right-click
the mouse and select Unhide from the formatting menu.
 ​ The hidden columns will reappear.

Wrapping text and merging cells

Whenever you have too much cell content to be displayed in a single cell, you may decide to
wrap the text or merge the cell rather than resize a column. Wrapping the text will
automatically modify a cell's row height, allowing cell contents to be displayed on multiple
lines. Merging allows you to combine a cell with adjacent empty cells to create one large cell.

To wrap text in cells:

​ Select the cells you want to wrap. In this example, we'll select the cells in
column C.
​ Click the Wrap Text command on the Home tab.

​ The text in the selected cells will be wrapped.

Click the Wrap Text command again to unwrap the text.

To merge cells using the Merge & Center command:

​ Select the cell range you want to merge. In our example, we'll select
A1:F1.
​ Click the Merge & Center command on the Home tab. In our example,
we'll select the cell range A1:F1.

​ The selected cells will be merged, and the text will be centered.

To access additional merge options:

If you click the drop-down arrow next to the Merge & Center command on the Home tab, the
Merge drop-down menu will appear.
From here, you can choose to:

​ Merge & Center: This merges the selected cells into one cell and
centers the text.
​ Merge Across: This merges the selected cells into larger cells while
keeping each row separate.
​ Merge Cells: This merges the selected cells into one cell but does not
center the text.
​ Unmerge Cells: This unmerges selected cells.

Be careful when using this feature. If you merge multiple cells that all contain data, Excel will
keep only the contents of the upper-left cell and discard everything else.

Centering across selection

Merging can be useful for organizing your data, but it can also create problems later on. For
example, it can be difficult to move, copy, and paste content from merged cells. A good
alternative to merging is to Center Across Selection, which creates a similar effect without
actually combining cells.

To use Center Across Selection:

​ Select the desired cell range. In our example, we'll select A1:F1. Note: If
you already merged these cells, you should unmerge them before
continuing to step 2.
​ Click the small arrow in the lower-right corner of the Alignment group
on the Home tab.

​ A dialog box will appear. Locate and select the Horizontal drop-down
menu, select Center Across Selection, then click OK.

​ The content will be centered across the selected cell range. As you can
see, this creates the same visual result as merging and centering, but it
 preserves each cell within A1:F1.

Formatting Cell
All cell content uses the same formatting by default, which can make it difficult to read a
workbook with a lot of information. Basic formatting can customize the look and feel of your
workbook, allowing you to draw attention to specific sections and making your content easier to
view and understand.

To change the font size:

​ Select the cell(s) you want to modify.

​ On the Home tab, click the drop-down arrow next to the Font Size
command, then select the desired font size. In our example, we will
 choose 24 to make the text larger.

​ The text will change to the selected font size.

You can also use the Increase Font Size and Decrease Font Size commands or enter a custom
font size using your keyboard.

To change the font:

By default, the font of each new workbook is set to Calibri. However, Excel provides many other
fonts you can use to customize your cell text. In the example below, we'll format our title cell to
help distinguish it from the rest of the worksheet.

​ Select the cell(s) you want to modify.

​ On the Home tab, click the drop-down arrow next to the Font
command, then select the desired font. In our example, we'll choose
Century Gothic.
 ​ The text will change to the selected font.

When creating a workbook in the workplace, you'll want to select a font that is easy to read.
Along with Calibri, standard reading fonts include Cambria, Times New Roman, and Arial.

To change the font color:

​ Select the cell(s) you want to modify.

​ On the Home tab, click the drop-down arrow next to the Font Color
command, then select the desired font color. In our example, we'll
 choose Green.

​ The text will change to the selected font color.

Select More Colors at the bottom of the menu to access additional color options. We've changed
the font color to a bright pink.
To use the Bold, Italic, and Underline commands:

​ Select the cell(s) you want to modify.

 ​ Click the Bold (B), Italic (I), or Underline (U) command on the Home
tab. In our example, we'll make the selected cells bold.

​ The selected style will be applied to the text.

You can also press Ctrl+B on your keyboard to make selected text bold, Ctrl+I to apply italics,
and Ctrl+U to apply an underline.

Cell borders and fill colors

Cell borders and fill colors allow you to create clear and defined boundaries for different
sections of your worksheet. Below, we'll add cell borders and fill color to our header cells to
help distinguish them from the rest of the worksheet.

To add a fill color:

​ Select the cell(s) you want to modify.

​ On the Home tab, click the drop-down arrow next to the Fill Color
command, then select the fill color you want to use. In our example, we'll
choose a dark gray.

​ The selected fill color will appear in the selected cells. We've also
changed the font color to white to make it more readable with this dark
fill color.
To add a border:

​ Select the cell(s) you want to modify.

​ On the Home tab, click the drop-down arrow next to the Borders
command, then select the border style you want to use. In our example,
we'll choose to display All Borders.

​ The selected border style will appear.

You can draw borders and change the line style and color of borders with the Draw Borders
tools at the bottom of the Borders drop-down menu.

Cell styles

Instead of formatting cells manually, you can use Excel's predesigned cell styles. Cell styles are
a quick way to include professional formatting for different parts of your workbook, like titles
and headers.

To apply a cell style:

In our example, we'll apply a new cell style to our existing title and header cells.

​ Select the cell(s) you want to modify.

 ​ Click the Cell Styles command on the Home tab, then choose the desired
style from the drop-down menu.

​ The selected cell style will appear.

Applying a cell style will replace any existing cell formatting except for text alignment. You
may not want to use cell styles if you've already added a lot of formatting to your workbook.
Text alignment

By default, any text entered into your worksheet will be aligned to the bottom-left of a cell, while
any numbers will be aligned to the bottom-right. Changing the alignment of your cell content
allows you to choose how the content is displayed in any cell, which can make your cell content
easier to read.

Click the arrows in the slideshow below to learn more about the different text alignment options.

To change horizontal text alignment:

In our example below, we'll modify the alignment of our title cell to create a more polished look
and further distinguish it from the rest of the worksheet.

​ Select the cell(s) you want to modify.

​ Select one of the three horizontal alignment commands on the Home

tab. In our example, we'll choose Center Align.
 ​ The text will realign.

To change vertical text alignment:

​ Select the cell(s) you want to modify.

​ Select one of the three vertical alignment commands on the Home tab.
In our example, we'll choose Middle Align.
 ​ The text will realign.

You can apply both vertical and horizontal alignment settings to any cell.

The Format Painter

If you want to copy formatting from one cell to another, you can use the Format Painter
command on the Home tab. When you click the Format Painter, it will copy all of the formatting
from the selected cell. You can then click and drag over any cells where you want to paste the
formatting.

Understanding number formats

Whenever you're working with a spreadsheet, it's a good idea to use appropriate number
formats for your data. Number formats tell your spreadsheet exactly what type of data you're
using, like percentages (%), currency ($), times, dates, and so on.
Why use number formats?

Number formats don't just make your spreadsheet easier to read—they also make it easier to use.
When you apply a number format, you're telling your spreadsheet exactly what types of values
are stored in a cell. For example, the date format tells the spreadsheet that you're entering
specific calendar dates. This allows the spreadsheet to better understand your data, which can
help ensure that your data remains consistent and that your formulas are calculated correctly.

If you don't need to use a specific number format, the spreadsheet will usually apply the general
number format by default. However, the general format may apply some small formatting
changes to your data.

Applying number formats

Just like other types of formatting, such as changing the font color, you'll apply number formats
by selecting cells and choosing the desired formatting option. There are two main ways to choose
a number format:

​ Go to the Home tab, click the Number Format drop-down menu in the
Number group, and select the desired format.
 ​ You can also click one of the quick number-formatting commands below
the drop-down menu.

You can also select the desired cells and press Ctrl+1 on your keyboard to access more
number-formatting options.

In this example, we've applied the Currency number format, which adds currency symbols ($)
and displays two decimal places for any numerical values.
If you select any cells with number formatting, you can see the actual value of the cell in the
formula bar. The spreadsheet will use this value for formulas and other calculations.

Using number formats correctly

There's more to number formatting than selecting cells and applying a format. Spreadsheets can
actually apply a lot of number formatting automatically based on the way you enter data. This
means you'll need to enter data in a way the program can understand, and then ensure that those
cells are using the proper number format. For example, the image below shows how to use
number formats correctly for dates, percentages, and times:
Now that you know more about how number formats work, we'll look at a few different number
formats in action.

Percentage formats

One of the most helpful number formats is the percentage (%) format. It displays values as
percentages, such as 20% or 55%. This is especially helpful when calculating things like the
cost of sales tax or a tip. When you type a percent sign (%) after a number, the percentage
number format will be be applied to that cell automatically.

As you may remember from math class, a percentage can also be written as a decimal. So 15% is
the same thing as 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55, and so on. You can review this
lesson from our Math tutorials to learn more about converting percentages to decimals.

There are many times when percentage formatting will be useful. For example, in the images
below, notice how the sales tax rate is formatted differently for each spreadsheet (5, 5%, and
0.05):
As you can see, the calculation in the spreadsheet on the left didn't work correctly. Without the
percentage number format, our spreadsheet thinks we want to multiply $22.50 by 5, not 5%. And
while the spreadsheet on the right still works without percentage formatting, the spreadsheet in
the middle is easier to read.

Date formats

Whenever you're working with dates, you'll want to use a date format to tell the spreadsheet that
you're referring to specific calendar dates, such as July 15, 2014. Date formats also allow you to
work with a powerful set of date functions that use time and date information to calculate an
answer.

Spreadsheets don't understand information the same way a person would. For instance, if you
type October into a cell, the spreadsheet won't know you're entering a date so it will treat it like
any other text. Instead, when you enter a date, you'll need to use a specific format your
spreadsheet understands, such as month/day/year (or day/month/year depending on which
country you're in). In the example below, we'll type 10/12/2014 for October 12, 2014. Our
spreadsheet will then automatically apply the date number format for the cell.
Now that we have our date correctly formatted, we can do many different things with this data.
For example, we could use the fill handle to continue the dates through the column, so a different
day appears in each cell:
If the date formatting isn't applied automatically, it means the spreadsheet did not understand the
data you entered. In the example below, we've typed March 15th. The spreadsheet did not
understand that we were referring to a date, so this cell is still using the general number format.

On the other hand, if we type March 15 (without the "th"), the spreadsheet will recognize it as a
date. Because it doesn't include a year, the spreadsheet will automatically add the current year so
the date will have all of the necessary information. We could also type the date several other
ways, such as 3/15, 3/15/2014, or March 15 2014, and the spreadsheet would still recognize it as
a date.
Try entering the dates below into a spreadsheet and see if the date format is applied
automatically:

​ 10/12
​ October
​ October 12
​ October 2016
​ 10/12/2016
​ October 12, 2016
​ 2016
​ October 12th

Other date formatting options

To access other date formatting options, select the Number Format drop-down menu and
choose More Number Formats. These are options to display the date differently, like including
the day of the week or omitting the year.
The Format Cells dialog box will appear. From here, you can choose the desired date formatting
option.
As you can see in the formula bar, a custom date format doesn't change the actual date in our
cell—it just changes the way it's displayed.

Number formatting tips

Here are a few tips for getting the best results with number formatting:

​ Apply number formatting to an entire column: If you're planning to

use one column for a certain type of data, like dates or percentages, you
may find it easiest to select the entire column by clicking the column
letter and applying the desired number formatting. This way, any data you
 add to this column in the future will already have the correct number
format. Note that the header row usually won't be affected by number
formatting.

​ Double-check your values after applying number formatting: If you

apply number formatting to existing data, you may have unexpected
results. For example, applying percentage (%) formatting to a cell with a
value of 5 will give you 500%, not 5%. In this case, you'd need to retype
 the values correctly in each cell.

​ If you reference a cell with number formatting in a formula, the

spreadsheet may automatically apply the same number formatting to the
new cell. For example, if you use a value with currency formatting in a
formula, the calculated value will also use the currency number format.

​ If you want your data to appear exactly as entered, you'll need to use the
text number format. This format is especially good for numbers you don't
want to perform calculations with, such as phone numbers, zip codes, or
numbers that begin with 0, like 02415. For best results, you may want to
apply the text number format before entering data into these cells.

Increase and Decrease Decimal

The Increase Decimal and Decrease Decimal commands allow you to control how many
decimal places are displayed in a cell. These commands don't change the value of the cell;
instead, they display the value to a set number of decimal places.
Decreasing the decimal will display the value rounded to that decimal place, but the actual value
in the cell will still be displayed in the formula bar.

The Increase/Decrease Decimal commands don't work with some number formats, like Date
and Fraction.

Working with Multiple Worksheets

Every workbook contains at least one worksheet by default. When working with a large amount
of data, you can create multiple worksheets to help organize your workbook and make it easier
to find content. You can also group worksheets to quickly add information to multiple
worksheets at the same time.
To insert a new worksheet:

​ Locate and select the New sheet button near the bottom-right corner of
the Excel window.

​ A new blank worksheet will appear.

By default, any new workbook you create in Excel will contain one worksheet, called Sheet1. To
change the default number of worksheets, navigate to Backstage view, click Options, then
choose the desired number of worksheets to include in each new workbook.

To copy a worksheet:

If you need to duplicate the content of one worksheet to another, Excel allows you to copy an
existing worksheet.
​ Right-click the worksheet you want to copy, then select Move or Copy
from the worksheet menu.

​ The Move or Copy dialog box will appear. Choose where the sheet will
appear in the Before sheet: field. In our example, we'll choose (move to
end) to place the worksheet to the right of the existing worksheet.
​ Check the box next to Create a copy, then click OK.

​ The worksheet will be copied. It will have the same title as the original
worksheet, as well as a version number. In our example, we copied the
 November worksheet, so our new worksheet is named November (2).
All content from the November worksheet has also been copied to the
new worksheet.

You can also copy a worksheet to an entirely different workbook. You can select any workbook
that is currently open from the To book: drop-down menu.

To rename a worksheet:

​ Right-click the worksheet you want to rename, then select Rename from
the worksheet menu.

​ Type the desired name for the worksheet.

 ​ Click anywhere outside the worksheet tab, or press Enter on your
keyboard. The worksheet will be renamed.

To move a worksheet:

​ Click and drag the worksheet you want to move until a small black
arrow appears above the desired location.

​ Release the mouse. The worksheet will be moved.

To change the worksheet tab color:

​ Right-click the desired worksheet tab, and hover the mouse over Tab
Color. The Color menu will appear.
 ​ Select the desired color.

​ The worksheet tab color will be changed.

The worksheet tab color is considerably less noticeable when the worksheet is selected. Select
another worksheet to see how the color will appear when the worksheet is not selected.
To delete a worksheet:

​ Right-click the worksheet you want to delete, then select Delete from the
worksheet menu.
 ​ The worksheet will be deleted from your workbook.

If you want to prevent specific worksheets from being edited or deleted, you can protect them
by right-clicking the desired worksheet and selecting Protect Sheet from the worksheet menu.

Switching between worksheets

If you want to view a different worksheet, you can simply click the tab to switch to that
worksheet. However, with larger workbooks this can sometimes become tedious, as it may
require scrolling through all of the tabs to find the one you want. Instead, you can simply
right-click the scroll arrows in the lower-left corner, as shown below.

A dialog box will appear with a list of all of the sheets in your workbook. You can then
double-click the sheet you want to jump to.
 Grouping and ungrouping worksheets

You can work with each worksheet individually, or you can work with multiple worksheets at
the same time. Worksheets can be combined together into a group. Any changes made to one
worksheet in a group will be made to every worksheet in the group.

To group worksheets:

​ Select the first worksheet you want to include in the worksheet group.
 ​ Press and hold the Ctrl key on your keyboard. Select the next worksheet
you want in the group.

​ Continue to select worksheets until all of the worksheets you want to

group are selected, then release the Ctrl key. The worksheets are now
grouped.

While worksheets are grouped, you can navigate to any worksheet within the group. Any
changes made to one worksheet will appear on every worksheet in the group. However, if you
select a worksheet that is not in the group, all of your worksheets will become ungrouped.

To ungroup worksheets:

Right-click a worksheet in the group, then select Ungroup Sheets from the worksheet menu.
 ​ The worksheets will be ungrouped. Alternatively, you can simply click
any worksheet not included in the group to ungroup all worksheets.

Using Find & Replace

When working with a lot of data in Excel, it can be difficult and time consuming to locate
specific information. You can easily search your workbook using the Find feature, which also
allows you to modify content using the Replace feature.

To find content:

In our example, we'll use the Find command to locate a specific department in this list.
​ From the Home tab, click the Find and Select command, then select
Find from the drop-down menu.

​ The Find and Replace dialog box will appear. Enter the content you
want to find. In our example, we'll type the department's name.
​ Click Find Next. If the content is found, the cell containing that content
will be selected.
 ​ Click Find Next to find further instances or Find All to see every
instance of the search term.

​ When you are finished, click Close to exit the Find and Replace dialog
box.

You can also access the Find command by pressing Ctrl+F on your keyboard.
Click Options to see advanced search criteria in the Find and Replace dialog box.

To replace cell content:

At times, you may discover that you've repeatedly made a mistake throughout your workbook
(such as misspelling someone's name) or that you need to exchange a particular word or phrase
for another. You can use Excel's Find and Replace feature to make quick revisions. In our
example, we'll use Find and Replace to correct a list of department names.
​ From the Home tab, click the Find and Select command, then select
Replace from the drop-down menu.

​ The Find and Replace dialog box will appear. Type the text you want to
find in the Find what: field.
​ Type the text you want to replace it with in the Replace with: field, then
click Find Next.

​ If the content is found, the cell containing that content will be selected.
​ Review the text to make sure you want to replace it.
​ If you want to replace it, select one of the replace options. Choosing
Replace will replace individual instances, while Replace All will replace
every instance of the text throughout the workbook. In our example, we'll
 choose this option to save time.

​ A dialog box will appear, confirming the number of replacements made.

Click OK to continue.

​ The selected cell content will be replaced.

 ​ When you are finished, click Close to exit the Find and Replace dialog
box.

Generally, it's best to avoid using Replace All because it doesn't give you the option of skipping
anything you don't want to change. You should only use this option if you're absolutely sure it
won't replace anything you didn't intend it to.

Page Layout and Printing

There may be times when you want to print a workbook to view and share your data offline.
Once you've chosen your page layout settings, it's easy to preview and print a workbook from
Excel using the Print pane.

To access the Print pane:

​ Select the File tab. Backstage view will appear.

 ​ Select Print. The Print pane will appear.

Click the buttons in the interactive below to learn more about using the Print pane.
To print a workbook:
​ Navigate to the Print pane, then select the desired printer.
​ Enter the number of copies you want to print.
​ Select any additional settings if needed (see above interactive).
 ​ Click Print.

Choosing a print area

Before you print an Excel workbook, it's important to decide exactly what information you want
to print. For example, if you have multiple worksheets in your workbook, you will need to
decide if you want to print the entire workbook or only active worksheets. There may also be
times when you want to print only a selection of content from your workbook.

To print active sheets:

Worksheets are considered active when selected.

​ Select the worksheet you want to print. To print multiple worksheets,

click the first worksheet, hold the Ctrl key on your keyboard, then click
any other worksheets you want to select.

​ Navigate to the Print pane.

 ​ Select Print Active Sheets from the Print Range drop-down menu.

​ Click the Print button.

To print the entire workbook:

​ Navigate to the Print pane.

​ Select Print Entire Workbook from the Print Range drop-down menu.
 ​ Click the Print button.

To print a selection:

In our example, we'll print the records for the top 40 salespeople on the Central worksheet.

​ Select the cells you want to print.

​ Navigate to the Print pane.

​ Select Print Selection from the Print Range drop-down menu.
​ A preview of your selection will appear in the Preview pane.
 ​ Click the Print button to print the selection.

If you prefer, you can also set the print area in advance so you'll be able to visualize which cells
will be printed as you work in Excel. Simply select the cells you want to print, click the Page
Layout tab, select the Print Area command, then choose Set Print Area. Keep in mind that if
you ever need to print the entire workbook, you'll need to clear the print area.

Adjusting content

On occasion, you may need to make small adjustments from the Print pane to fit your
workbook content neatly onto a printed page. The Print pane includes several tools to help fit and
scale your content, such as scaling and page margins.

To change page orientation:

Excel offers two page orientation options: landscape and portrait. Landscape orients the page
horizontally, while portrait orients the page vertically. In our example, we'll set the page
orientation to landscape.
​ Navigate to the Print pane.
​ Select the desired orientation from the Page Orientation drop-down
menu. In our example, we'll select Landscape Orientation.
 ​ The new page orientation will be displayed in the Preview pane.

To fit content before printing:

If some of your content is being cut off by the printer, you can use scaling to fit your workbook
to the page automatically.
​ Navigate to the Print pane. In our example, we can see in the Preview
pane that our content will be cut off when printed.
​ Select the desired option from the Scaling drop-down menu. In our
example, we'll select Fit All Columns on One Page.
 ​ The worksheet will be condensed to fit onto a single page.

Keep in mind that worksheets will become more difficult to read as they are scaled down, so
you may not want to use this option when printing a worksheet with a lot of information. In our
example, we'll change the scaling setting back to No Scaling.

To include Print Titles:

If your worksheet uses title headings, it's important to include these headings on each page of
your printed worksheet. It would be difficult to read a printed workbook if the title headings
appeared only on the first page. The Print Titles command allows you to select specific rows
and columns to appear on each page.
​ Click the Page Layout tab on the Ribbon, then select the Print Titles
command.

​ The Page Setup dialog box will appear. From here, you can choose rows
or columns to repeat on each page. In our example, we'll repeat a row
first.
​ Click the Collapse Dialog button next to the Rows to repeat at top:
field.

​ The cursor will become a small selection arrow, and the Page Setup
dialog box will be collapsed. Select the row(s) you want to repeat at the
 top of each printed page. In our example, we'll select row 1.

​ Row 1 will be added to the Rows to repeat at top: field. Click the
Collapse Dialog button again.

​ The Page Setup dialog box will expand. To repeat a column as well, use
the same process shown in steps 4 and 5. In our example, we've selected
to repeat row 1 and column A.
​ When you're satisfied with your selections, click OK.
 ​ In our example, row 1 appears at the top of every page, and column A
appears at the left of every page.

To adjust page breaks:

​ Click the Page Break Preview command to change to Page Break view.

​ Vertical and horizontal blue dotted lines denote the page breaks. Click
and drag one of these lines to adjust that page break.
​ In our example, we've set the horizontal page break between rows 21 and
22.
 ​ In our example, all the pages now show the same number of rows due to
the change in the page break.

To modify margins in the Preview pane:

A margin is the space between your content and the edge of the page. Sometimes you may need
to adjust the margins to make your data fit more comfortably. You can modify page margins
from the Print pane.

​ Navigate to the Print pane.

​ Select the desired margin size from the Page Margins drop-down menu.
In our example, we'll select Narrow Margins.
 ​ The new page margins will be displayed in the Preview pane.

You can adjust the margins manually by clicking the Show Margins button in the lower-right
corner, then dragging the margin markers in the Preview pane.
View Tab
The commands for hiding and showing worksheets are on the Home Tab under (Cells Group,
Format > Unhide).
The commands for hiding and showing workbooks (and windows) can be found on this tab.

Sheet View
Lets you create different views of the same worksheet when you are collaborating with others.
Only enabled in Excel online or on the Desktop when your workbook has been saved in
OneDrive.

Switch Sheet View - A drop-down list of all the different sheet views in this workbook for easy
switching.
Keep - Saves the current view as a new sheet view.
Exit - Closes the current sheet view and returns back to the default view.
New - Lets you define a new sheet view. New views are given the name "Temporary View" until
they have been saved.
Options - This will only be enabled when. Displays the "Sheet View Options" dialog box which
allows you to rename, duplicate and delete as well as quickly switching between different views.

Workbook Views
Normal - Displays the worksheet in the normal view. There is also a shortcut to this view in the
bottom right corner of the status bar.
Page Break Preview - Used for adjusting page breaks. There is also a shortcut to this view in the
bottom right corner of the status bar.
Page Layout - Displays the worksheet as it will appear on a printed page. Displays a very useful
horizontal and vertical ruler plus you can add/change headers and footers, check margins,
row/column headings and scaling options. Not to be confused with Print Preview. There is also a
shortcut to this view in the bottom right corner of the status bar.
Custom Views - Displays the "Custom Views" dialog box.

Show

Ruler - This will only be enabled when in Page Layout view. Toggles the display of the
horizontal and vertical rulers. This is only available when you are in Page Layout view. You can
change the units displayed from the (Excel Options, Advanced tab)(Display, Ruler Units). By
default the ruler displays the default units that are specified in the control panel, regional
settings. These units can be either inches, centimeters or millimeters.
Gridlines - Toggles the display of gridlines on the active worksheet. Provides a shortcut to (Excel
Options, Advanced tab)(Display options for this worksheet, show gridlines).
Formula Bar - Toggles the display of the formula bar. Provides a shortcut to (Excel
Options)(Advanced tab, Show formula bar).
Headings - Toggles the display of the heading rows. Provides a shortcut to (Excel
Options)(Advanced tab, Show row and column headers).

Zoom
Zoom - Displays the "Zoom" dialog box.
100% - Zoom the workbook to 100% of its normal size.
Zoom to Selection - Zoom the worksheet to display just the currently selected cells.

Window

New Window - Creates a new window of your current workbook.

Arrange All - Tile all open windows side by side on the screen.
Freeze Panes - Drop-Down. The drop-down contains the commands: Freeze Panes, Freeze Top
Row and Freeze First Column.
Split - Splits the window into multiple resizeable panes which allow you to have multiple views
of the same workbook.
Hide - Hides the current workbook or window.
Unhide - Displays the "Unhide" dialog box allowing you to unhide a workbook or window.
View Side by Side - View two workbooks side by side to allow you to compare their contents.
Synchronous Scrolling - Synchronize the scrolling of two windows so they scroll together. The
View Side by Side option must be switched on for this command to be enabled.
Reset Window Position - Reset the window position of the two windows being compared so that
they share the screen equally. The View Side by Side option must be switched on for this
command to be enabled.
Switch Windows - Drop-Down. The drop-down contains a list of all the workbooks/windows
that are currently open.
Macros

Macros - Button with Drop-Down. The button is a shortcut to View Macros which displays the
"Macro" dialog box displaying all the available macros. The drop-down contains the commands:
View Macros, Record Macro and Use Relative References.

Intro to Formulas

One of the most powerful features in Excel is the ability to calculate numerical information
using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this
lesson, we'll show you how to use cell references to create simple formulas.

Mathematical operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for
subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret
(^) for exponents.
All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is
equal to, the formula and the value it calculates.

Understanding cell references

While you can create simple formulas in Excel using numbers (for example, =2+2 or =5*5),
most of the time you will use cell addresses to create a formula. This is known as making a cell
reference. Using cell references will ensure that your formulas are always accurate because you
can change the value of referenced cells without having to rewrite the formula.

In the formula below, cell A3 adds the values of cells A1 and A2 by making cell references:

When you press Enter, the formula calculates and displays the answer in cell A3:
If the values in the referenced cells change, the formula automatically recalculates:

By combining a mathematical operator with cell references, you can create a variety of simple
formulas in Excel. Formulas can also include a combination of cell references and numbers, as in
the examples below:

To create a formula:

In our example below, we'll use a simple formula and cell references to calculate a budget.
​ Select the cell that will contain the formula. In our example, we'll select
cell D12.

​ Type the equals sign (=). Notice how it appears in both the cell and the
formula bar.

​ Type the cell address of the cell you want to reference first in the
formula: cell D10 in our example. A blue border will appear around the
 referenced cell.

​ Type the mathematical operator you want to use. In our example, we'll
type the addition sign (+).
​ Type the cell address of the cell you want to reference second in the
formula: cell D11 in our example. A red border will appear around the
referenced cell.
 ​ Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell. If you select the cell again, notice that
the cell displays the result, while the formula bar displays the formula.

If the result of a formula is too large to be displayed in a cell, it may appear as pound signs
(#######) instead of a value. This means the column is not wide enough to display the cell
content. Simply increase the column width to show the cell content.

Modifying values with cell references

The true advantage of cell references is that they allow you to update data in your worksheet
without having to rewrite formulas. In the example below, we've modified the value of cell D10
from $1,200 to $1,800. The formula in D12 will automatically recalculate and display the new
value in cell D12.
Excel will not always tell you if your formula contains an error, so it's up to you to check all of
your formulas. To learn how to do this, you can read the Double-Check Your Formulas lesson
from our Excel Formulas tutorial.

To create a formula using the point-and-click method:

Instead of typing cell addresses manually, you can point and click the cells you want to include
in your formula. This method can save a lot of time and effort when creating formulas. In our
example below, we'll create a formula to calculate the cost of ordering several boxes of plastic
silverware.

​ Select the cell that will contain the formula. In our example, we'll select
cell D4.
​ Type the equals sign (=).
​ Select the cell you want to reference first in the formula: cell B4 in our
example. The cell address will appear in the formula.

​ Type the mathematical operator you want to use. In our example, we'll
type the multiplication sign (*).
​ Select the cell you want to reference second in the formula: cell C4 in our
example. The cell address will appear in the formula.
 ​ Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell.

Copying formulas with the fill handle

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time
and effort if you need to perform the same calculation multiple times in a worksheet. The fill
handle is the small square at the bottom-right corner of the selected cell(s).
 ​ Select the cell containing the formula you want to copy. Click and drag
the fill handle over the cells you want to fill.

​ After you release the mouse, the formula will be copied to the selected
cells.

To edit a formula:

Sometimes you may want to modify an existing formula. In the example below, we've entered an
incorrect cell address in our formula, so we'll need to correct it.
​ Select the cell containing the formula you want to edit. In our example,
we'll select cell D12.

​ Click the formula bar to edit the formula. You can also double-click the
cell to view and edit the formula directly within the cell.

​ A border will appear around any referenced cells. In our example, we'll
change the first part of the formula to reference cell D10 instead of cell
 D9.

​ When you're finished, press Enter on your keyboard or select the Enter
command in the formula bar.
 ​ The formula will be updated, and the new value will be displayed in the
cell.

If you change your mind, you can press the Esc key on your keyboard or click the Cancel
command in the formula bar to avoid accidentally making changes to your formula.

To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave
accent). The grave accent key is usually located in the top-left corner of the keyboard. You can
press Ctrl+` again to switch back to the normal view.
Cell referencing

There are two types of cell references: relative and absolute. Relative and absolute references
behave differently when copied and filled to other cells. Relative references change when a
formula is copied to another cell. Absolute references, on the other hand, remain constant no
matter where they are copied.

Relative references

By default, all cell references are relative references. When copied across multiple cells, they
change based on the relative position of rows and columns. For example, if you copy the formula
=A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are
especially convenient whenever you need to repeat the same calculation across multiple rows or
columns.

To create and copy a formula using relative references:

In the following example, we want to create a formula that will multiply each item's price by the
quantity. Rather than create a new formula for each row, we can create a single formula in cell
D2 and then copy it to the other rows. We'll use relative references so the formula correctly
calculates the total for each item.
​ Select the cell that will contain the formula. In our example, we'll select
cell D2.
​ Enter the formula to calculate the desired value. In our example, we'll
type =B2*C2.

​ Press Enter on your keyboard. The formula will be calculated, and the
result will be displayed in the cell.
​ Locate the fill handle in the lower-right corner of the desired cell. In our
example, we'll locate the fill handle for cell D2.
​ Click, hold, and drag the fill handle over the cells you wish to fill. In our
example, we'll select cells D3:D12.
 ​ Release the mouse. The formula will be copied to the selected cells with
relative references and the values will be calculated in each cell.

You can double-click the filled cells to check their formulas for accuracy. The relative cell
references should be different for each cell, depending on its row.
Absolute references

There may be times when you do not want a cell reference to change when filling cells. Unlike
relative references, absolute references do not change when copied or filled. You can use an
absolute reference to keep a row and/or column constant.

An absolute reference is designated in a formula by the addition of a dollar sign ($) before the
column and row. If it precedes the column or row (but not both), it's known as a mixed
reference.
You will use the relative (A2) and absolute ($A$2) formats in most formulas. Mixed references
are used less frequently.

When writing a formula in Microsoft Excel, you can press the F4 key on your keyboard to
switch between relative, absolute, and mixed cell references

To create and copy a formula using absolute references:

In our example, we'll use the 7.5% sales tax rate in cell E1 to calculate the sales tax for all items
in column D. We'll need to use the absolute cell reference $E$1 in our formula. Because each
formula is using the same tax rate, we want that reference to remain constant when the formula is
copied and filled to other cells in column D.

​ Select the cell that will contain the formula. In our example, we'll select
cell D3.
​ Enter the formula to calculate the desired value. In our example, we'll
type =(B3*C3)*$E$1.

​ Press Enter on your keyboard. The formula will calculate, and the result
will display in the cell.
​ Locate the fill handle in the lower-right corner of the desired cell. In our
example, we'll locate the fill handle for cell D3.
​ Click, hold, and drag the fill handle over the cells you wish to fill, cells
D4:D13 in our example.
 ​ Release the mouse. The formula will be copied to the selected cells with
an absolute reference, and the values will be calculated in each cell.

You can double-click the filled cells to check their formulas for accuracy. The absolute reference
should be the same for each cell, while the other references are relative to the cell's row.
Be sure to include the dollar sign ($) whenever you're making an absolute reference across
multiple cells. The dollar signs were omitted in the example below. This caused the spreadsheet
to interpret it as a relative reference, producing an incorrect result when copied to other cells.
Using cell references with multiple worksheets

Most spreadsheet programs allow you to refer to any cell on any worksheet, which can be
especially helpful if you want to reference a specific value from one worksheet to another. To do
this, you'll simply need to begin the cell reference with the worksheet name followed by an
exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its cell
reference would be Sheet1!A1.

Note that if a worksheet name contains a space, you will need to include single quotation
marks (' ') around the name. For example, if you wanted to reference cell A1 on a worksheet
named July Budget, its cell reference would be 'July Budget'!A1.

To reference cells across worksheets:

In our example below, we'll refer to a cell with a calculated value between two worksheets. This
will allow us to use the exact same value on two different worksheets without rewriting the
formula or copying data between worksheets.
​ Locate the cell you wish to reference, and note its worksheet. In our
example, we want to reference cell E14 on the Menu Order worksheet.

​ Navigate to the desired worksheet. In our example, we'll select the

Catering Invoice worksheet.

​ The selected worksheet will appear.

​ Locate and select the cell where you want the value to appear. In our
example, we'll select cell B2.

​ Type the equals sign (=), the sheet name followed by an exclamation
point (!), and the cell address. In our example, we'll type ='Menu
 Order'!E14.

​ Press Enter on your keyboard. The value of the referenced cell will
appear. If the value of cell E14 changes on the Menu Order worksheet, it
 will be updated automatically on the Catering Invoice worksheet.

If you rename your worksheet at a later point, the cell reference will be updated automatically to
reflect the new worksheet name.