Microsoft Office Excel 2016 pdf

Page 1 of 150
Microsoft Office
Excel 2016 for Windows
INTRODUCTION TO MS-EXCEL

Page 2 of 150
Learning Objectives
• The Ribbon, Customizing the Ribbon
• The Quick Access Toolbar, Tell Me and Smart Lookup
• The Smart Lookup Tool, The File Tab
• Columns and Rows, Entering Text
• Long Words and Numbers, Completing a Series
• Selecting Multiple Cells, Moving Text and Numbers
• Copying Data
• Insert a Row or Column, Delete a Row or Column
• Changing Column Width and Row Height, Formatting Numbers
• Formatting Text and Numbers, Changing the Font, Borders
• GraphicsClip Art, Images from a FilePrinting, Saving
Intro to Excel spreadsheets
What is a spreadsheet?
A spreadsheet is the computerized equivalent of a general ledger. It has taken the place of the pencil,
paper, and calculator. Spreadsheet programs were first developed for accountants but have now been
adopted by anyone wanting to prepare a budget, forecast sales data, create profit and loss statements,
compare financial alternatives and any other mathematical applications requiring calculations.
The electronic spreadsheet is laid out similar to the paper ledger sheet in that it is divided into columns
and rows. Any task that can be done on paper can be performed on an electronic spreadsheet faster and
more accurately.
Page 3 of 150
The problem with manual sheets is that if any error is found within the data, all answers must be erased
and recalculated manually. With the computerized spreadsheet, formulas can be written that are
automatically updated whenever the data are changed.
What can a spreadsheet do?
In contrast to a word processor, which manipulates text, a spreadsheet manipulates numerical data and
text. Using a spreadsheet, one can create budgets, analyze data, produce financial plans, and perform
various other simple and complex numerical applications.
By having formulas that automatically recalculate, either built by you, the user, or the built-in math
functions, you can play with the numbers to see how the result is affected. Using this “what-if?” analysis,
you can see what affect changing a data value or calculation can have on your monitoring program.
Spreadsheets can also be used for graphing data points, reporting data analyses, and organizing and
storing data.
Starting Excel
You are encouraged to start using MS Excel as you read through the following materials to familiarize
yourself with the topics and procedures.
1. Click the Start button on the Windows taskbar.
a. The Start menu opens
2. Point to Programs
a. The Programs menu opens
3. Click Microsoft Excel
a. Excel opens a new workbook
Note: an icon for MS Excel may be located either on the desktop or on the Office toolbar.
Page 4 of 150
The Excel Screen
The screen in Excel looks different than those used in other types of applications.
Columns
The large window, labeled “Microsoft Excel” may take up the entire screen. This is referred to as the
Application Window. The top line is called the Title Bar and has three buttons (Minimize, Restore, and
Close) to the right. These buttons are used to size the window and close it. This title bar is standard in
all Windows programs.
The second line is called the Menu Bar. Notice that one character of each selection is highlighted or
underlined. This menu bar is also standard in all Windows programs.
The next two lines contain buttons with text or images and are referred to as the Standard and
Formatting Toolbars. If you have a mouse, these toolbars allow you to enhance your worksheet without
accessing the menu. Keep in mind that these may not be in the exact same place as on the illustration
above. All toolbars can be customized to display any buttons you desire.
Page 5 of 150
The next line is the Formula Bar and displays the current cell address (see below) and contents. As you
move from cell to cell, Excel will keep track of the current cell address for you. The Formula Bar can also
be used to edit the text (contents) or formulas contained in the cell.
Columns and Cells and Row
The horizontal bar across the top of the worksheet area is filled with letters, beginning with A. Each letter
represents a column while the vertical bar on the left side of the worksheet filled with numbers refers
to rows.
The intersection between a column and a row is referred to as a cell. A cell is similar to a box that can be
used to store pieces of information. Each piece of information could be a word or group of words, a
number or a mathematical formula.
Each cell has its own address. This address is used in formulas for referencing different parts of the
worksheet. The address of a cell is defined by the letter of the column in which it is located and the
number of the row. For example, the address of a cell in column B, row 5 would be referred to as B5.
The column is always listed first followed by the row without any spaces between the two.
The outlined cell (the one with the dark borders) within the worksheet is referred to as the active cell.
Each cell may contain text, numbers, or dates. You can enter up to 32,000 characters in each cell
(Equivalent to a 44 page report!).
These cell addresses are useful when entering formulas. Instead of typing actual values in your
equations, you simply type the cell address where the value is stored. Then, if you need to go back and
change one of the values the spreadsheet automatically updates the result of the formula based on the
new data.
For example, instead of typing 67*5.4 you could enter C5*D5. The number 67 is stored in cell C5 and the
number 5.4 is stored in cell D5. If these numbers change next month or next year, the formula remains
correct because it references the cells – not the actual values. With the second formula, you can change
the numbers stored in cells C5 and D5 as often as required and see the result recalculated immediately.
The next section of the screen lists the columns and rows within the current worksheet. As mentioned,
columns are lettered and rows are numbered. The first 26 columns are lettered A through Z. Excel then
Page 6 of 150
begins lettering the 27th column with AA and so on. In a single Excel worksheet there are 256 columns
(lettered A-IV) and 65,536 rows (numbered 1-65,536), totaling 16,777,216 individual cells.
Sheets and Workbooks
Towards the bottom of the worksheet is a set of small Tabs that identify each sheet in the workbook
(file). If there are multiple sheets, you can use the tabs to easily identify what data is stored on each
sheet. For example, the top sheet could be “Expenses” and the second sheet could be called “Income”.
When you begin a new workbook, the tabs default to being labeled Sheet1, Sheet2, etc.
At the bottom of the screen is another bar called the Status Bar. This bar is used to display various
information about the system and current workbook.
The left- hand corner of this line lists the Mode Indicator, which tells you what mode you are currently
working in.
The Zoom button (located on the toolbar at the top of the screen) allows you to change the size of the
viewing area. This does not affect the actual printing of the file. Click on the down arrow located to the
right side of the current zoom factor. Scroll through the available zoom choices. When you select a zoom
factor, Excel will zoom in or out of the worksheet area – as specified in the Zoom. You can also access
the View Æ Zoom menu. In addition, you can hide everything except the worksheet and the menu (which
will increase your working area) by accessing the View Æ Full Screen menu.
The Ribbon
The Ribbon is designed to help you quickly find the commands that you need to complete a task.
Commands are organized in logical groups, which are collected together under Tabs. Each Tab relates to
a type of activity, such as formatting or laying out a page. To reduce clutter, some Tabs are shown only
when needed. For example, the Picture Tools tab is shown only when a picture is selected.
File Menu
Here you will find the basic commands such as open, save, print, etc.
Page 7 of 150
Quick Access Toolbar
The place to keep the items that you not only need to access quickly, but want to be immediately
available regardless of which of the Ribbon’s tabs you’re working on. If you put so many items on the
Quick Access Toolbar that it becomes too big to fit on the title bar, you can move it onto its own line.
Tell Me
This is a text field where you can enter words and phrases about what you want to do next and quickly
get to features you want to use or actions you want to perform. You can also use Tell Me to find help
about what you’re looking for, or to use Smart Lookup to research or define the term you entered.
Formula Bar
A place where you can enter or view formulas or text.
Expand Formula Bar Button
This button allows you to expand the formula bar. This is helpful when you have either a long formula
or large piece of text in a cell.
Worksheet Navigation Tabs
By default, every workbook starts with 1 sheet.
Insert Worksheet Button
Click the Insert New Worksheet button to insert a new worksheet in your workbook.
Horizontal/Vertical Scroll
Allows you to scroll vertically/horizontally in the worksheet.
Normal View
This is the “normal view” for working on a spreadsheet in Excel.
Page Layout View
Page 8 of 150
View the document as it will appear on the printed page.
Page Break Preview
View a preview of where pages will break when the document is printed.
Zoom Level
Allows you to quickly zoom in or zoom out of the worksheet.
Navigating in the Excel Environment
Below is a table that will assist you with navigating/moving around in the Excel environment.
Key Description
ARROW KEYS Move one cell up, down, left, or right in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.
BACKSPACE Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.
In cell editing mode, it deletes the character to the left of the
insertion point.
DELETE Removes the cell contents (data and formulas) from selected
cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the
insertion point.
END Moves to the cell in the lower-right corner of the window when
SCROLL LOCK is turned on.
Page 9 of 150
Also selects the last command on the menu when a menu or
submenu is visible.
CTRL+END moves to the last cell on a worksheet, in the lowest
used row of the rightmost used column. If the cursor is in the
formula bar, CTRL+END moves the cursor to the end of the text.
CTRL+SHIFT+END extends the selection of cells to the last used
cell on the worksheet (lower-right corner). If the cursor is in the
formula bar, CTRL+SHIFT+END selects all text in the formula bar
ENTER Completes a cell entry from the cell or the Formula Bar, and
selects the cell below (by default).
ESC Cancels an entry in the cell or Formula Bar. Closes an open
menu or submenu, dialog box, or message window.
HOME Moves to the beginning of a row in a worksheet.
CTRL+HOME moves to the beginning of a worksheet.
PAGE DOWN Moves one screen down in a worksheet.
PAGE UP Moves one screen up in a worksheet.
SPACEBAR In a dialog box, performs the action for the selected button, or
selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
Page 10 of 150
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
TAB Moves one cell to the right in a worksheet.
Highlighting/Selecting Areas Using the Mouse
Select cells:
Moves a cell’s contents:
Activate the Autofill feature:
To Select a Column: Click on the column letter
To Select a Row: Click on the row number
To Select the Entire Worksheet: Click above row 1 and to the left of column A or hit CTRL A on the
keyboard
Entering Text
Any items that are not to be used in calculations are considered, in Excel’s terminology, labels. This
includes numerical information, such as phone numbers and zip codes. Labels usually include the title,
column and row headings.
To Enter Text/Labels:
1) Click in a cell
2) Type text
3) Press Enter
NOTE: By default, pressing the Enter key will move you to the cell below the active cell.
Page 11 of 150
The label actually “lives” in the cell you typed it into. If you type long text it might appear to be in multiple
columns. It is important to understand this concept when trying to apply formatting to a cell. Using the
formula bar will confirm where the label actually “lives.”
Autofills
Frequently, it is necessary to enter lists of information. For example, column headings are often the
months of the year or the days of the week. To simplify entering repetitive or sequential lists of
information, Excel has a tool called Autofill. This tool allows preprogrammed lists, as well as custom lists,
to be easily added to a spreadsheet.
Entering Values
Numerical pieces of information that will be used for calculations are called values. They are entered the
same way as labels. It is important NOT to type values with characters such as “,” or “$”.
To Enter Values:
1) Navigate to a cell
2) Type a value
3) Press Enter
Creating Formulas
Formulas perform calculations or other actions on the data in your worksheet. A formula starts with an
equal sign (=). It is possible to create formulas in Excel using the actual values, such as “4000*.4” but it
is more beneficial to refer to the cell address in the formula, for example “D1*.4”. One of the benefits
of using a spreadsheet program is the ability to create a formula in one cell and copy it to other cells.
Most spreadsheet formulas use a concept called relative referencing.
This is the explanation of relative referencing from Excel’s help file:
“A relative cell reference in a formula, such as A1, is based on the relative position of the cell that
contains the formula and the cell the reference refers to. If the position of the cell that contains the
formula changes, the reference is changed. If you copy the formula across rows or down columns, the
Page 12 of 150
reference automatically adjusts. By default, new formulas use relative references. For example, if you
copy a relative reference in cell B2 to cell B3, it automatically adjusts.”
It is also important to know the operators Excel uses for formulas:
Operator (Key) Function
= Begins all Excel functions and formulas
+ Addition
– Subtraction
* Multiplication
/ Division
Operator (Key) Function
= Begins all Excel functions and formulas
+ Addition
– Subtraction
* Multiplication
/ Division
To Create a Formula:
1) Click in a cell
2) Press the = key
3) Type the formula
4) Press Enter
Copying Formulas
Page 13 of 150
Like many things in Excel, there is more than one way to copy formulas. Feel free to choose what works
best for you.
To Copy Formulas Using Autofill:
1) Click in the cell that contains the formula
2) Position the mouse on the Autofill handle (a thin black cross will appear)
3) Click and drag to copy the formula
To Copy Formulas Using Copy and Paste:
1) Click in the cell that contains a formula
2) Select Copy on the Home Ribbon in the Editing group
3) Highlight the cell where you would like to paste the formula
4) Select Paste on the Home Ribbon in the Editing group
ALTERNATE METHODS
Keyboard: Ribbon: Mouse:
Press CTRL + C copy Right-click and choose
Keyboard: Ribbon: Mouse:
Press CTRL + C Right-click and choose
Copy
Page 14 of 150
Autosum Function
The most common formula in Excel is SUM, or the addition of multiple values. In this example, we could
create a formula that reads =C6+D6+E6+F6+G6+H6. That’s a lot of typing! Instead, we can use the SUM
function and specify a range of cells.
Functions are more complex formulas that are invoked by typing their name. In this example, we will
use the SUM function. Excel has over 200 functions that can be used. Because SUM is the most common
function, it is the only one with its own toolbar button.
When working with functions, the cells used in the formula are referred to as the range. A range is a
group of cells that are specified by naming the first cell in the group and the last cell. For example, A1:D1
is a range that includes cells A1, B1, C1 and D1.
To Create the Total Column’s Values Using Autosum:
1) Click in the cell where you would like the Total to be located
2) Press the Autosum button on the Home Ribbon
Page 15 of 150
The Autosum function automatically looks for cells that have values in them. It will read values until it
finds the first blank cell. Autosum will always look for values in the cells above it first, then to the left.
This means that you need to be aware of what cells will be in the formula. Autosum will select the range
of cells to use in the formula by highlighting the range.
3) Press Enter
Saving a Worksheet
When working in Excel it is necessary to save your files. It is also very important that while working, your
file is saved frequently. When naming a file, you are restricted to 255 characters. Avoid most
punctuation; spaces are acceptable.
To Save the File:ss
1) Click on the File tab
2) Click Save
3) Choose the destination
4) Type a file name
5) Click Save
Editing Cells
Excel provides a major enhancement over earlier spreadsheet products in its ability to edit cells easily.
There are various methods for cell editing, including double-clicking in the cell, using the F2 key, and
typing in the formula bar.
To Edit a Cell in the Worksheet:
1) Position yourself in the cell you would like to edit
2) Press the F2 key on the keyboard or double-click in the cell
Page 16 of 150
3) Use the backspace or delete keys to edit the cell
4) Press Enter when you have finished editing the cell
5) Click in the cell you would like to edit
6) Click in the formula bar and make any necessary changes
7) Press Enter when you have finished editing the cell
Undo
Excel and other Windows applications have a convenient method of correcting mistakes known as Undo.
In many applications, including Excel, you can undo an almost limitless number of commands. The Undo
button has a small down-pointing arrow next to it. When pressed, it will display a list of actions that can
be undone. Redo works in the same way, allowing you to repeat actions.
Excel will undo actions in reverse chronological order, meaning that the most recent command is
reversed first, then the one prior to that, and so on. You cannot reverse an earlier action using Undo
without first undoing the actions that were performed after it.
NOTE: The list of commands to undo is reset after the file is saved. You cannot use Undo to fix an error
after the file is saved.
To Undo a Command:
Click Undo
Clearing Cells
As we begin to look at formatting, it is important to understand what makes up the contents of a cell.
There are three distinct items that can be in a cell:
• Contents
• Formats
• Comments
Page 17 of 150
These allow items to be formatted properly, even if the values change. However, when trying to delete
or clear a cell, it can be a bit tricky. Excel stores formats and contents separately, simply deleting the
contents does not delete the format.
To Clear a Cell Format:
1) Click in the cell that contains formatting
Click the drop-down arrow next to the Clear button on the Home tab in the Editing group
Click Clear Formats
Formatting Values
Applying formats to any cell(s) can be done either using the Font, Alignment and Number groups or using
the dialog box which will include all the formatting options.
To Apply the Currency Format:
1) Highlight the cell(s)
2) Click on the Currency Style button on the Home tab in the Number group
3) If necessary, click on the Increase or Decrease Decimal button on the Number group
To Apply the Comma Format:
Page 18 of 150
1) Highlight cells
2) Click on the Comma Style button on the Number group
3) If necessary, click on the Increase or Decrease Decimal button on the Number group
Formatting Labels
A Label, or text formatting is applied virtually the same way it is done in word processing programs.
To Format the Title Labels:
1) Highlight the cell(s)
2) Select a font from the Font group
3) Select a point size from the Font group
Using the Dialog Box:
1) Highlight the cells
2) Click on the arrow in the corner of one of the formatting groups (Font, Alignment, Number) to
open the Format Cells dialog box and click on one of the tabs
Page 19 of 150
Format Painter
Frequently, you will need to take a format that is applied to one cell and apply it to other cells. A quick
way to do this is by using the Format Painter.
To Apply a Format to Cells:
1) Highlight cell(s)
2) Format the cell(s) to the desired format
3) Select the formatted cell(s)
4) Click the Format Painter from the Clipboard group of the Home tab
5) Highlight the cells you wish to format
Tips and Tricks: If you would like the Format Painter to remain active, double-click the Format Painter.
It will remain active until you press the Esc key.
Cantering Text Across Columns
When it comes to titles, it may be preferable to have the information cantered across the document,
rather than in only one cell. Excel uses the feature Merge Cells to accomplish this.
Page 20 of 150
To Centre the Title Across Columns:
1) Highlight cell(s)
2) Click the Merge and Centre button on the Alignment group
NOTE: Each cell must be done individually. Excel will delete the contents of all but the top most cell if
multiple cells are selected.
This option basically takes all the cells in the highlighted range and merges them into one large cell. For
example, the range A1:F1 became cell A1 after the Merge Cells button was selected. There is no cell B1,
C1, etc. any longer.
Creating a Basic Chart
1) Highlight the data to be charted
2) Click on the Insert tab
3) Click on a Chart Type in the Charts group
4) Click on a Chart Style
To Move your Chart:
Click and drag the chart to a new location on the worksheet.
Page 21 of 150
When the chart is selected you will notice a new tab “Chart Tools” on the Ribbon. If you do not see the
Chart Tools, click on the chart to select it. Under Chart Tools you will find 2 tabs:
• Design
• Format`
Excel Functions
As we have previously seen, the power of Excel lies in its ability to perform calculations. The real strength
of this is shown in Functions. Functions are more complex formulas that are executed by using the name
of a function and stating whatever parameters the function requires.
To Enter the SUM Function:
1) Click in a cell
2) Click on the AutoSum button in the Editing group
3) Highlight the range of cells that are to be added (The colon means “through”)
4) Press ENTER
To Insert the Average Function into the Worksheet:
1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Average
Page 22 of 150
4) Highlight the range of cells be calculated
5) Press ENTER
To Insert the MAX Function into the Worksheet:
1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Max
4) Highlight the range of cells be calculated
5) Press ENTER
To Insert the MIN Function into the Worksheet:
1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Min
4) Highlight the range of cells be calculated
5) Press ENTER
To Insert the COUNT NUMBERS Function into the Worksheet:
1) Click in a cell
2) Click on the drop-down arrow next to the AutoSum button
3) Click on Count Numbers
4) Highlight the range of cells be calculated
5) Press ENTER
Page 23 of 150
Printing a Worksheet
To Print, Preview and Modify Page Setup
1) Click on the File tab
2) Click on Print
The spreadsheet shows as it will be printed. You can proceed to print the document from here, or you
can change things to make the printed output look different.
Page Setup
You can change options under Settings or you can click on Page Setup.
Clicking on Page Setup will open a dialog box with four tabs:
• Page
• Margins
• Header/Footer
• Sheet
Page 24 of 150
Page:
1) Change the Orientation
2) Adjust the Scaling
3) Change the Paper Size
Margins:
1) Change the margins
2) Center on the page either horizontally, vertically or select both
Header/Footer:
1) To select from one of the already created headers/footers, click on the drop-down arrow for
Header and also for Footer and choose from the list
2) To create a custom header and/or footer, click on Custom Header and Custom Footer
This area is made of three sections – left, centre and right. Any information added in these sections will
appear in that area (left, centre or right) in the header or footer. You will also see a row of buttons in
this dialog box. Following are their functions:
Page 25 of 150
Click in a section to position your cursor
3) Enter text/fields
4) Click OK when finished
Sheet Tab:
1. Repeat Rows and Columns under Print Titles
2. Check off what to print under Print
3. Change the Page Order
File Tab in Microsoft Excel
What is File tab and its uses?
File tab contains the basic required options such as New, Open, Save, Save as, Print, Share, Export, and
Close options. Other than the aforementioned options, we can find account and Excel options tab, too.
Page 26 of 150
a) Info: – With this option, we can get the information about the particular Excel file. Created date, last
modified date, Author name, Properties, versions etc.
b) New: – We use this option to open the new Excel file. We can open new file using shortcut key,
Ctrl+N or by Clicking on File tab > New > Blank workbook. If Excel file is not opened, then Press Window+R
and type Excel, New Excel file will open.
Page 27 of 150
c) Open: – We use this option to open the existing file (shortcut Ctrl+O). “Open” option appears and
you can choose to open the file.
Alternatively, File tab > Open > choose the file
d) Save: – We use this option to save the current file.
Shortcut: – Ctrl+S
Path: – Click on File tab >Save
e) Save as: – We use this option to make another copy or save the file at another place. F12 is the
shortcut key to save as the file or we can save the file following these steps: – Click on File tab > Save as
and then choose the location.
Page 28 of 150
g) Share: – We use this option to share the file with multiple users and send it over email. To share the
file we can follow the steps: – Click on File tab >Share.
h) Export: – We use this option to export the file in PDF or XPS document and we can change the file
type as well. To Export the file, we can follow the steps: – Click on File tab >Export. And then we can
export it as per our requirement.
Page 29 of 150
i) Close: – We use this option to close the file. Ctrl+W is the shortcut key to close the workbook or we
can follow the steps: – Click on File tab >Close, active file will be closed. When we close the file, we get
the confirmation message to save the file or not or cancel the command.
j) Account: – We use this option to sign in to our office account and we can change office theme, too.
We can follow the steps: – Click on File tab >Active, Active window will appear.
k) Options: – It was in the tool menu of the previous versions of 2007 MS. We use this option to add
extra and advanced features, like Developer tab, Power pivot, Analysis toolpak and many more. Also, we
can change default settings, like font size, font style, number of sheets etc. In Excel options, we have 10
categories:-
1) General
2) Formulas
Page 30 of 150
3) Proofing
4) Save
5) Language
6) Advanced
7) Customize Ribbon
8) Quick Access toolbar
9) Add Ins
10) Trust Center
Page 31 of 150
Home Tab in Microsoft Excel
What is Home tab and its uses?
Home tab contains the most frequently used options such as cut-copy-paste, font formatting, alignment,
Number, Conditional formatting, etc. All the options are used to format the data.
Under the Home tab, we have 7 groups: –
a) Clipboard: – This group contains frequently used commands: Cut, Copy, Paste and Format painter.
Clipboard option allows us to collect text and graphic items and paste it.
Page 32 of 150
b) Font: – We use this option to change the font style and font-size. We can make it bold, italic and
underline. Also, this group contains border styles, fill color, font color.
c) Alignment: – We use this option to change the alignment of cell’s text to the right, left and middle.
Also, we can subject the text to top, bottom, and middle alignment. In this group, we have Wrap text
option to adjust and make the text visible within a cell, and we can also merge 2 or more cells, using
merge option.
d) Number: – We use this option to change the number formatting into General, Percentage, Currency,
Date, Time, Fraction etc. We can increase and decrease the decimal and convert the number into
accounting number.
e) Styles: – In this option, we have Conditional Formatting, Format as Table and Cell Styles. Conditional
formatting is used to highlight the cell or range on the basis of conditions. Format as table is having
Page 33 of 150
readymade table format and Cell styles feature different types of built-in styles that are a combination
of Font style, Font colour and Fill colour.
f) Cells: – We use this option to insert or delete cells, rows, columns and sheets. Also, we have format
option to adjust the height, width of cells or range. Using this option, we can hide or unhide the range,
protect the workbook, rename the sheet name, fill the tab colour, move or copy to sheets, lock the cells.
g) Editing: – This option has Auto Sum feature to return the total of numbers and move the text to
right, left, up and down, Clear the format, content, comments and hyperlink; sort the data and find and
select option.
Insert Tab in Microsoft Excel
What is Insert tab and its uses?
We use Insert tab to insert the picture, charts, filter, hyperlink etc. We use this option to insert the
objects in Excel. To open the insert tab, press shortcut keys Alt+N.
Page 34 of 150
Under the Insert tab, we have 10 groups:-
a) Tables: – We use this option to insert the dynamic table, Pivot table and recommended table. Pivot
table is used to create the summary of report with the built-in calculation, and we have option to make
our own calculation. Tables make it easy to sort, filter and format the data within a sheet. This option is
also having recommended table that means on the basis of data, we can just insert the table as per the
Excel’s recommendation.
Page 35 of 150
b) Illustration: – We use this option to insert the Pictures, Online Pictures, Shapes, SmartArt and
Screenshot. It means if we want to insert any image, we can use Illustration feature.
c) Apps: – We use this option to insert an app into the document and, in order to enhance the
functionality, we can use web option.
d) Charts: -Charts is very important and useful function in Excel. In excel, we have different and good
numbers of readymade chart options. We have 8 types of different charts in Excel:- Column, Bar, Radar,
Line, Area, Combo, Pie and Bubbles chart. We can insert Pivot chart as well as Recommended chart, and
if we don’t know which chart we should insert for the data, we can use this option to fulfil the
requirement.
e) Reports: -We use this option to create a better report on the basis of the decisions we take for
business. It makes the report more interactive and decipherable.
Page 36 of 150
f) Sparkline’s: -Sparkline is a very tricky and useful option added by Microsoft Excel. On the basis of a
range, it can visualize the trends in a single cell as charts. We have 3 different types of cell charts: – Line,
Column and Win/Loss chart.
g) Filters: -We use this option to filter data visually and filter dates interactively. We have 2 options:
Slicers and Timeline. We use Slicer to make the fast and easier to filter tables, Pivot tables, Pivot Charts
and cube functions. Timeline makes it faster and easier to select time periods in order to filter Pivot
Tables, Pivot Charts and Cube function.
h) Links: –
• We use this option to create the link in the document for the quick access to webpage and files.
• We can also use it to access different locations in the document.
i) Text: -We use this option to insert the Text box, Header and Footer, Word art, Signature and objects.
We insert Text box to write something in the image format. We use Header and Footer options to place
the content on the top and bottom of the page. Word art makes the text stylish. Insert the Add Signature
Lines that specify the individual who is supposed to sign it. And object option works for embedded
objects, like documents or other files we have inserted into the document.
Page 37 of 150
j) Symbols: – We use this option to insert the symbols and equation. Equation is used to insert the
common mathematical equations to your document and also we can add equation by using the
mathematical symbols. We use Symbols to insert the symbols which are not on the keyboard and, to
create the equation, we use the symbols from here.
DATE AND TIME FUNCTIONS
LEARNING OBJECTIVES
 Dates in Exce
 Times in Excelss
 Useful Date/Time Shortcuts
 Custom Formatting
 Simple Date/Time Math
 Date Worksheet Functions
 Time Functions
 Datedif Function
Dates in Excel
If you’ve ever lost the date formatting on a cell, you have seen it turn into a strange number. For
example, according to Excel these cells are the same number:
Page 38 of 150
The serial number 41, 564 tells us how many days it has been since January 1st, 1900*. The date serial
numbers are sequential, one day at a time:
*Note: The default date system for the Macintosh begins with January 1, 1904. This may cause some
confusion if you try to use the same file in both a Mac and a PC. The setting can be changed in the Excel
Options on the File menu.
Times in Excel
One day has 24 hours, so in Microsoft Excel, 1 is equivalent to 24 hours, 0.5 is equivalent to 12 hours. If
we take our October 17th date, and add in a time of 12:00pm, it translates into 41564.5, the 0.5
representing the half‐ way point of the day.
1=24 hours, 0.5=12 hours, 0.25=6 hours…
If you leave the date off a time, Excel will default to 1/0/1900 as the ‘understood’ date. You can ignore
it, but realize that is what happens if you change a time format into a date/time format. All three of
these cells contain 12:00 PM, they are just displayed with different formats:
Page 39 of 150
Custom Formatting
Days, Months, and Years
You can format a cell with a preset list of options in the
Format Cells Window.
1. Click the More button in the Number group
2. Right‐click on a cell and choose Format cells
3. Select the cell and press Ctrl‐1 to open this window
Excel has a pretty extensive list of date and time formats, but it is possible to custom build a date format,
using simple abbreviations.
Hours, minutes, and seconds
If you use “m” immediately after the “h” or “hh” code or immediately before the “ss” code, Excel
displays minutes instead of the month.
Day
Mont
h Year
D 3 m 2
yy 04
dd 03 mm 02
ddd Tue mmm Feb
yyyy 2004
dddd Tuesday mmmm February
Page 40 of 150
Times for 1:02:05
appropriate designator at the end. Access will accept any of the following: AM/PM; am/pm; A/P; a/p;
AMPM.
Times for 17:02:05
Hours 24 hour clock 12 hour clock
H 17 h:m 17:2 h:m am/pm 5:2 PM
hh 17 h:mm 17:02 h:mm am/pm 5:02 PM
h AM/PM 5 PM hh:mm 17:02 hh:mm am/pm05:02 PM
If you need to go smaller, adding .00 after the second format (ss.00) will give the fraction of a second.
Shortcut Result Note
Ctrl‐; Current Date Control Semicolon
Ctrl‐: Current Time Control Colon
Page 41 of 150
Totaling Time
The time formats in Excel stay within the defaults of 24 hours, 60 minutes, 60 seconds. If we do math
we may want it to display the times beyond these boundaries. For this, we use the brackets [ ] around
the abbreviation.
Beyond the
Boundaries
Simple Date/Time Math
Because dates and times are stored as numbers you can do simple math with them
Times are a fraction of a day. As mentioned earlier, 0.5=12 hours. If we multiply a time by 24 we should
get the numeric value.
Time Time * 24
8:45 AM 6:00 PM
Why doesn’t it look right? You have to format the result as a true number field.
Time
Time * 24 (formatted as a
number)
36
Hours 75 minutes
75
seconds
h 12 m
(assumes
month) s 15
hh 12 mm
(assumes
month) ss 15
[h] 36 [m] 75 [s] 75
Page 42 of 150
8:45 AM 8.75
Remember if you are going beyond the 24 hour clock, you will need to set up a custom format.
Date Worksheet Functions
Adapted from Excel Help
DATE ‐ Returns the sequential serial number that represents a particular date
Syntax: DATE(year, month, day)
Year The value of the year argument can include one to four digits.
Month A positive or negative integer representing the month of the year from 1 to 12. If
month is greater than 12, month adds that number of months to the first month in
the year specified
Day A positive or negative integer representing the day of the month from 1 to 31.
DATEVAULE ‐ Returns the serial number of a date
Converts a date that is stored as text to a serial number that Excel recognizes as a date. To view a
date serial number as a date, you must apply a date format to the cell.
Syntax: DATEVALUE(date_text)
date_text represents a date between 01/01/1900 and 12/31/9999 saved as text
Equation Result Notes
=DATEVALUE(3/15/2009) #VALUE! Date not in Text format
=DATEVALUE(“10/15/1905”) 2115
Page 43 of 150
=DATEVALUE(“3/20/2011”) 40622
=DATEVALUE(A1) #VALUE!
A
1 = 12/5/1976
=DATEVALUE(A1) 28099
A
1 = “12/5/1976”
DAY ‐ Returns the numeric value of the day in a valid date
Syntax: =DAY(serial number)
Serial Number is the date of the day you are trying to find.
Equation Result Notes
=DAY(3/15/2009) 0
Serial Number not a Date (Excel sees
3÷15÷2009)
=DAY(“10/15/1905”) 15
=DAY(40622) 20 Equivalent to 3/20/2011
=DAY(A1) 5 A1 = 12/5/1976
DAYS360 ‐ returns the number of days between two dates based on a 360‐day year
360 day year assumes twelve 30‐day months, this is often used with accounting calculations. If start date
occurs after end date, the DAYS360 function returns a negative number.
Syntax: DAYS360(start date, end date, [method])
Page 44 of 150
Start Date and End Date are valid dates which represent the starting and ending dates. Method Optional
logical value that specifies whether to use the U.S. (NASD) or European method in the calculation. False
or omitted will give us the U.S. method, True will use the European method.
Remarks: If start_date is the last day of the month, both methods set the start_date to the 30th of
the month, but if the end_date is the last day of the month the US method will change the date to
the 1st of the next month, the European will change the date to the 30th.
EDATE ‐ Returns serial number of the date that is a number of months away from a date
Syntax: EDATE (start date, months)
Start Date is a valid date that represents the starting date
Months is the number of months before or after start date. A positive value for months yields
a future date; a negative value yields a past date
Equation Result Notes
=EDATE(A1, 1) 39899 03/27/2009 A1 = 02/27/2009
=EDATE(A1, 10) 40174 12/27/2009 A1 = 02/27/2009
=EDATE(A
1, 100) 42913 06/27/2017 A1 = 02/27/2009
=EDATE(A
1, ‐1) 39840 01/27/2009 A1 = 02/27/2009
Page 45 of 150
EOMONTH ‐ Returns serial number for the last day of the
month EOMonth => End of month
Syntax: EOMONTH(start date, months)
Months number of months before or after start_date. A positive value for months yields a future date;
a negative value yields a past date.
MONTH‐ Returns the numeric value of the month in a valid date
Syntax: =MONTH (serial number)
Serial Number is the date of the month you are trying to find.
Syntax: NETWORKDAYS(start_date, end_date, holidays)
Start Date and End Date are valid dates.
Holidays is an optional range of one or more dates to exclude from the working calendar. The list can
be either a range of cells that contains the dates or an array constant of the serial numbers that
represent the dates
Equation Result Notes
=NETWORKDAYS(A1, A2) 5 A1 = 02/27/2009, A2 = 03/05/2009
=NETWORKDAYS(B1, B2) 1 B1 = 01/30/2009, B2 = 02/01/2009
=NETWORKDAYS(C1, C2) 12 C1 = 07/01/2009, C2 = 07/15/2009
Page 46 of 150
=NETWORKDAYS(C1, C2, C3) 11
C1 = 07/01/2009, C2 = 07/15/2009,
C3 = 07/04/1999
=NETWORKDAYS(D1, D2, D3:D4) 259
D1 = 11/15/2009, D2 = 11/15/2010, D3
=
01/01/2010, D4 = 07/04/2010
WEEKDAY ‐ Returns the day of the week corresponding to a date
Syntax: =WEEKDAY (serial number, return_type)
Serial Number is the date of the day you are trying to find.
Return Type is a number that determines the type of return value. 1 or omitted sees the week
as 1‐ Sunday, 7‐Saturday. 2 sees the week as 1‐Monday, 7‐Sunday. 3 sees the week as
0‐Monday, 6‐ Sunday.
Equation Result Notes
=WEEKDAY(“12/15/1976”) 4 Wednesday, December 15, 1976
=WEEKDAY(40622) 1 Sunday, March 20, 2011
=WEEKDAY(A1) 7 A1 = Saturday, October 28, 1905
Page 47 of 150
WEEKNUM ‐ Returns the day of the week corresponding to a date
Syntax: =WEEKNUM (serial number, return_type)
Serial Number is the date of the day you are trying to find.
Return Type is a number that determines the type of return value. 1 ‐ Week begins on Sunday.
Weekdays are numbered 1 through 7; 2 ‐ Week begins on Monday. Weekdays are
numbered 1 through 7.
Remarks: The WEEKNUM function considers the week containing January 1 to be the first week of the
year.
Equation Result Notes
=WEEKNUM (“12/15/1976”) 51 Wednesday, December 15, 1976
=sWEEKNUM (40622) 13 Sunday, March 20, 2011
=WEEKNUM(A1) 43 A1 = Saturday, October 28, 1905
WORKDAY ‐ Returns a date that is a number of working days before or after a date
Syntax: WORKDAY (start_date, days, holidays)
Start Date is a valid date that represents the starting date.
Days is the number of non‐weekend and non‐holiday days before or after start_date. A
positive value yields a future date; a negative value, a past date.
Page 48 of 150
Holidays is an optional range of one or more dates to exclude from the working calendar. The
list can be either a range of cells that contains the dates or an array constant of the serial
numbers that represent the dates
YEAR‐ Returns the numeric value of the year in a valid date
Syntax: =YEAR (serial number)
Serial Number is the date of the year you are trying to find.
Equation Result Notes
=YEAR(“10/15/1905”) 5
=YEAR(40622) 2011
Equivalent to
3/20/2011
=YEAR(A1) 1976 A1 = 12/5/1976
YEARFRAC ‐ Returns fraction of the year of the number of whole days between two
dates Syntax: YEARFRAC (start_date, end date, basis)
Start Date and End Date are valid dates which represent the starting and ending dates
Basis is the type of day count basis to use
0‐US/NASD 30/360; 1‐Actual/Actual; 2‐Actual/360; 3‐Actual/365; 4‐European 30/360
Page 49 of 150
Equation Result Notes
=YEARFRAC (A1, A2) 1 A1 = 1/1/2009, A2 = 1/1/2010
=YEARFRAC (A1, A3) 1.013888889 A1 = 1/1/2009, A3 = 1/1/2010
=YEARFRAC (A1, A4) 0.455555556 A1 = 1/1/2009, A4 = 6/15/2009
=YEARFRAC (A1, A5) 0.538888889 A1 = 1/1/2009, A5 = 7/15/2009
=YEARFRAC (A1, A6) 13.53888889 A1 = 1/1/2009, A6 = 7/15/2022
HOUR ‐ Returns the hour of a time value
Syntax: HOUR (serial number)
Serial Number the time that contains the hour you want to find.
Remarks: Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as
decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other
formulas or functions (for example, TIMEVALUE (“6:45 PM”)).
Equation Result Notes
=HOUR(A1) 20 A1 = 8:28 PM
=HOUR(B1) 8 B1 = 8:28 AM
=HOUR(C1) 15 C1 = 15:43:12
=HOUR(D1) 17 D1 = 1/2/2003 17:52
MINUTE‐ Returns the hour of a time value
Page 50 of 150
Syntax: MINUTE (serial number)
Serial Number the time that contains the hour you want to find.
Remarks: Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as
decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other
formulas or functions (for example, TIMEVALUE (“6:45 PM”)).
Equation Result Notes
=MINUTE(A1) 28 A1 = 8:28 PM
=MINUTE(C1) 43 C1 = 15:43:12
=MINUTE(D1) 52 D1 = 1/2/2003 17:52
NOW ‐ the serial number of the current date and time
Syntax: NOW()
SECOND ‐ Returns the Seconds of a time value
Syntax: SECOND (serial number)
Serial Number the time that contains the hour you want to find.
Remarks: Times may be entered as text strings within quotation marks (for example, “6:45 PM”), as
decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other
formulas or functions (for example, TIMEVALUE(“6:45 PM”)).
Page 51 of 150
Hour is a number from 0 (zero) to 32767 representing the hour. Any value greater than 23
will be divided by 24 and the remainder will be treated as the hour value
Minute is a number from 0 to 32767 representing the minute. Any value greater than 59
will be converted to hours and minutes.
Second is a number from 0 to 32767 representing the second. Any value greater than 59
will be converted to hours, minutes, and seconds.
TIMEVAULE ‐ Returns the serial number of a time
Converts a time that is stored as text to a serial number that Excel recognizes as a time. To view a
time serial number as a time, you must apply a time format to the cell.
Syntax: TIMEVALUE (time text)
time text represents a time between 01/01/1900 and 12/31/9999 saved as text
Equation Result Notes
=TIME(15, 3, 15) 3:03:20 PM
=TIME(0, 0, 2000) 12:33:20 AM 2000 seconds = 33 min, 20 sec
=TIME(C1, C2, C3) 6:12:09 AM
Page 52 of 150
Datedif Function
There is a “hidden” function that can find
the difference between two dates and
return different increments. When you
subtract two dates you get the number of
days between them. The Datedif
function is not in the help files or list of
formats, but is probably one of the most
powerful date related worksheet
functions.
Syntax: DATEDIF (Begin Date, End Date,
“Interval”)
Math and Functions
Learning Objectives
 Fill Handle
 Mathematical Operations
 Building an Equation
 Order of Operations
 Formatting Dates
 Formula View
 Absolute/Relative
 Functions.
 Naming Cells
Interval Description
D Number of Days
M Number of Months
Y Number of Years
YM Number of months, not counting years
YD Number of days, not counting years
MD
Number of days, not counting years and
months
Page 53 of 150
Fill Handle Options
When you use the Fill Handle, you will notice a symbol appear in the right hand bottom corner of your
newly filled cells. This icon ( ) represents your AutoFill Options. If you put your mouse over the icon
you will see a drop down arrow that will give you a list of your fill options.
The four basic Fill Options are:
 Copy Cells – Repeat the cells along the selection
 Fill Series – Follow pattern along the selection
 Fill Format Only – Repeat the format of the cells along the selection
 Fill without Formatting – Follow the pattern along the selection, but not the format
 Flash Fill – Fills based on a pattern you establish in the same column
If you use the Fill Handle on cells with dates you will notice even more options:
 Fill Days
 Fill Weekdays
 Fill Months
 Fill Years
Building an Equation
Page 54 of 150
You can directly type in values, but that data stays constant. If you want to have the answers to your
equations update as you change your data, you should use the cell addresses. You will see the cell
addresses change colors so you can tell which ones are used in your equation.
Type in the exact cell address
Cells are labeled by their row and column headings. Rows are numbered and go horizontally across
(rows of chairs) and columns are lettered and go vertically top to bottom (columns of a building). When
we refer to the address of a cell, we use the column letter then the row number such as A1.
– Click in the cell where the answer will appear
– Press the Equal sign (=)
– Type in the cell address you want to use in your equation
– Accept the answer or press the next math operator (+, ‐, *, /, ^)
A B C
1 1 2 =a1+b1
Use the keyboard to point to the cell address
When you first enter a character into a cell, you will be in “ENTER” mode. This includes when you type
an equal sign. But if you press an arrow key on the keyboard after the equal sign, Excel does not move
Page 55 of 150
out of the cell. Instead you will be put into a “POINT” mode, and Excel will color coordinate the cell you
are selecting.
– Click in the cell where the answer will appear
– Press the Equal sign (=)
– Press the arrow keys until you are on the cell you want to use in your equation
– Accept the answer or press the next math operator (+, ‐, *, /, ^)
Use the mouse to point to the cell address
The mouse and arrow keys are both “pointers”. If you press the equal sign and then use the mouse to click
on another cell, Excel will put you into a “POINT” mode, and place the address of the cell you clicked on
in your equation.
– Click in the cell where the answer will appear
– Press the Equal sign (=)
– Use the mouse to click on the cell you want to use in your equation
– Accept the answer or press the next math operator (+, ‐, *, /, ^)
Order of Operations
Microsoft Excel respects the Order of Operations.
(1) Parenthesis
(2) Exponents (raised to a power)
(3) Multiplication and Division
(4) Addition and Subtraction
Page 56 of 150
Parentheses Exponents Multiply Divide Add Subtract
Please Excuse My Dear Aunt Sally
This means with an equation such as
= 5 + 3 * 2
Excel will do the multiplication before it does the addition. would be 11.
If you wanted the addition to happen first, you have to use parentheses:
= ( 5 + 3 ) * 2
giving us a result of 16.
In math, we use the brackets, such as {[(5+3) *(4‐2)] / 2}
In Excel we ONLY use parentheses
=((5+3)*(4‐2))/2) result 8.
Remember to let Excel know you want it to calculate something; you have to start with an equal sign
If you get stuck inside an equation, or confused by a mistake, press Esc to cancel and try again.
Formatting Dates
Dates and Times are technically numbers because they can be used in equations. Excel is able to do this
because it has a “serial” number equivalent. Both columns of this table at the right of this page show the same
value, but the dates in the second column have lost the date format. For a date, 0 = 1/1/1900. The numeric
values in the second column represent how many days there are between 1/1/1900 and the date listed.
If your date loses its format you can modify it through the Number group or through the Format Cells Window.
If you don’t like the choices offered, you can custom build your date from the Custom section of the Number
page in the Format Cells Window. Use M for months, D for days and Y for years. Excel is not case sensitive, upper
or lowercase letters will work for these formats.
11/16/1978 28,810.00
5/2/1982 30,073.00
11/4/1986 31,720.00
Page 57 of 150
2/23/1993 34,023.00
11/13/1995 35,016.00
2/2/2002 37,289.00
7/24/2008 39,653.00
12/25/2010 40,537.00
6/8/2013 41,433.00
11/20/2018 43,424.00
Our cells display the formula results. If we want to see the equation, we have to look at the formula bar
or edit the cell. Sometimes it helps to see all the equations on a worksheet. The button for this option is on
the Formula tab, in the Format Auditing group. The keyboard shortcut is Control‐Tilde, ctrl ~ (the wavy line
above Tab, below Esc on your
You can also turn this on through the Excel Options. From the File Menu, choose Options. On the
Advanced tab, under the Display Options for this Worksheet group, check the box next to Show
formulas in cell instead of their calculated results.
Calculated Results (normal view)
Page 58 of 150
Formula View
The formula view will stretch out the columns and all the numbers appear to have lost their format,
including the dates and some alignments. Be careful adjusting the column widths, if you shrink them
in the formula view, it will proportionally shrink in the calculated results view.
You can continue working in this view and print out the sheet with all the formulas showing. If you’re
going to print this view, I recommend turning on the print headings from the Page Layout tab.
Friday, February 1, 2002
dddd, mmmm d, yyyy
Day Month Year
d 1 m 2 y 02
dd 01 mm 02 yy 02
ddd Fri mmm Feb yyy 2002
Page 59 of 150
dddd Friday mmmm February yyyy 2002
You can also custom build times using H for hours, M for minutes and S for seconds.
Formula View
Our cells display the formula results. If we want to see the equation, we have to look at the formula bar
or edit the cell. Sometimes it helps to see all the equations on a worksheet. The button for this option
is on the Formula tab, in the Format Auditing group. The keyboard shortcut is Control‐Tilde, ctrl ~ (the
wavy line above Tab, below Esc on your keyboard).
You can also turn this on through the Excel Options. From the File Menu, choose Options. On the
Advanced tab, under the Display Options for this Worksheet group, check the box next to Show
formulas in cell instead of their calculated results.
Calculated Results (normal view)
Page 60 of 150
The formula view will stretch out the columns and all the numbers appear to have lost their format,
including the dates and some alignments. Be careful adjusting the column widths, if you shrink them in
the formula view, it will proportionally shrink in the calculated results view.
You can continue working in this view and print out the sheet with all the formulas showing. If you’re
going to print this view, I recommend turning on the print headings from the Page Layout tab.
Absolute/Relative
When you create an equation in Excel using cell addresses, Excel sets up the equation to have a relative
reference. When you are using the Fill Handle or the Copy and Paste features the equation result is relative
If this equation is copied into cell C2, or the Fill Handle is used to drag the equation down to C2, Excel
will give you this result:
Since the equation was moved down, between rows, only the row number changes. If instead we moved the
equation across, the row numbers will remain the same, but the column numbers will change:
The addresses in the equation are relative to where the answer is positioned. The equation in cell C1 of the
table above states ‘take the value of the cell that is two to the left from this cell and add it to the value of the
cell that is one to the left from this cell’. When we fill or copy the cell over or down, the basic equation stays
the same.
If you do not want a number to move relatively you can make it absolute by using dollar signs ($) in the
equation. The F4 button on the keyboard will place the dollar sign characters in for you while you are in Enter,
Edit, or Point mode. (Think F4 ‐> FORCE)
=$A$1 ‐ Locks the reference into Cell A1
A B C D
1 5 6 =A1+B1
2 12 4 =A2+B2
3
Page 61 of 150
=$A1 ‐ Locks the reference into Column A, but will allow the row number to change =A$1 ‐ Locks
the reference into Row 1, but will allow the row number to change
A B C D E F G H I
1 5 6 =$A$1+B1 SubTotals: 123 456 789
2 12 4 =$A$1+B2 TaxRate: 6.5%
3 Taxes: =F1*$F$2 =G1*$F$2 =H1*$F$2
Functions
Microsoft Excel has several built in functions. To insert a function, click the Insert Function
button on the Formula Bar, or the Insert Function option from the Formulas Tab.
Page 62 of 150
From here you can request a function to perform a particular task and Excel will make suggestions for you.
If you Search for a function: Excel will return a Recommended category, offering all the functions it thinks
might help in your search.
By default, the first category is a list of Most Recently Used functions. To see all the functions built into
Excel, you can choose All from the Select a category: list.
The bottom of this window displays a description of the selected function. Each choice will show an example
arrangement of the function, the arguments, and a description of what that function should do. If you need
more information, click on the Help on this function option in the bottom left corner. If you have found the
function you would like to use, select it and click OK.
An easier way to access the list of Most Recently Used functions is to press the equal
sign on the keyboard, as if you were going to type an equation. The name box, that
displays which cell you are in, changes to the last function that was used. When you
click on the arrow next to the listed function (in this case SUM), you will see a list of
list of Most Recently Used functions.
becomes
If the function you desire is not on the Most Recently Used list, chose the More Functions… option at the
bottom of the list and you will get the above Insert Function dialog box.
Either selection method will open the Function Arguments window.
Page 63 of 150
The function name is listed in the top left corner and the description is across the bottom. There are
blanks in the middle of the screen for the arguments of the statement. You can type in the cell addresses,
move the window out of the way to try to select the addresses or let Excel help you move the window
by using the Collapse or “go out and get it button” ( ). This button will collapse your Function
Arguments window so you can select the data you wish to use as an argument in this function. Once you
have chosen your desired data either press Enter or click on the Expand button ( ) to return to the
full window.
In the sample above, you can see we can Sum more than one number/set of numbers. As soon as you
click into Number 2 a Number 3 will appear. The description tells us this will allow up to 255 arguments
(number ranges) to sum.
Across the bottom of this window we can see a Formula Result =. This will show us the running total as
we add in each part of the equation. Notice there is also an =number at the end of each argument line.
This will give you a piece‐by‐piece result for each argument. This is especially helpful when using the
logic functions, such as If.
Page 64 of 150
Once a name is defined, you can use it in your equations. =B12*$B$1 means the same as
=B12*FixedValue
When you use the fill handle or the Copy/Paste feature, the B12 will change to be relative to the new
location, but FixedValue will remain throughout, always pointing to cell B1.
This same method can be used for a range of cells. Select the desired range, click within the name box,
erase the current cell address, type the name of the range, press Enter.
*NOTE* There are some limitations in naming. You cannot use many special characters such as the hyphen
(‐), and the name must be all one word, no spaces. In the example above we used capitalization to show
multiple words, you can also use the underscore character (_ ).
Defined Names group on the Formula Tab:
Define a Name – create a new name
Use in Formula – choose from a list of existing names, this can be used
to begin a new formula or to add a name to a formula you are building.
Create from Selection – make a new name based on a group of selected cells
Use the Name Manager to modify a Defined Name.
Page 65 of 150
New: Create a new Name
Edit…: Rename, add a comment, change cell
reference
Delete: Select the name in the list and click
Delete to remove it
Filter: Filter the list of names to help you find
the one you need.
To Redefine: Select the name in the list, erase the Refers to blank and choose your new range of cells.
Click the Check to accept.
F3 is the keyboard shortcut to let you pick a name to use in your equation.
Creating Equations with Cell addresses (pg 3)
– Clear cell C1, type =a1+b1 O
Not case sensitive
 Notice the color coding
 Accept data, should get a 3
 Change A1 to 5, accept to see answer in C1 change
-USING KEYBOARD -USING MOUSE
 Go to C2   Go to C3
 press = sign  press = sign
 press arrow two times  click on cell A3
 press + sign  press the + sign
Page 66 of 150
 press arrow once  click on cell B3
 enter to accept  click on check to accept
Basic math symbols (pg 3)
– Add +, Subtract ‐, Multiply *, Divide / O
All found on the Num Pad
– Build these equations
 In Cell C4 add A4 and B4: =A4+B4
 In Cell C5 subtract A5 and B5: =A5‐B5
 In Cell C6 multiply A6 and B6: =A6*B6
 In Cell C7 divide A7 and B7: =A7/B7
Remember the Order of Operations (pg 4)
– Parenthesis
– Exponents/Powers
– Multiplication/Division
– Addition/Subtractions
– Build these equations
O In Cell C8 type =2+3*5
O In Cell C9 type =(2+3)*5
Page 67 of 150
Date not Math?
– Put today’s date in cell C10
O i.e. 10/5
O Note Excel sees it as a date not as an equation (10 divided by 5), Why? Because there is
no Equal sign! 10/5 is October 5th, =10/5 is an equation with a result of 2
Totals Preview
– Select Column A
– Look in the status bar in the bottom right side of the window. When you select more
than one cell, Excel will automatically calculate the Average, Count, and Sum of your
selection.
Formatting
– Format column A with dollar signs
O Select Column A, from the right‐click or Home tab, choose the $ format
Page 68 of 150
– Format column B to be centered
O Select Column B, from the right‐click or Home tab, choose Align Center
– Edit Cell C1 (double‐click in cell)
O When you accept, Excel recalculates answer and the format changes
– Format Column C with dollar signs
Note format in cell C10, no longer a date
Reformat Date
– Click on Cell C10 (we don’t want the whole column)
– Choose Short date from the list of number formats, in the Number group on the Home
Tab
– Choose “More Numbers” to view different date formats
– Choose the 14‐Mar format to return to the original date formatting
To custom build a data format see pg 4
Formula View (pg 5)
– Toggle cell data with ctrl‐` (Ctrl‐~… Wavy line above
tab, below Esc)
– We can see all the formulas but NO formatting
Page 69 of 150
– Toggle back to answer view
Create Data Table
– Click on the Plus sign next
to Sheet 2 to create Sheet
3
– Zoom to 150%
– Type in the table shown here
Format Data Table
– Bold Row 4
– Center Column C
– AutoFit Column C
– Dollar Format the Prices, B5 through B7
– In Cell D4 type: SubTotal
O Should be bold already
Calculate SubTotals
Page 70 of 150
Enter First SubTotal
o In Cell D5 Type: = B5*C5
$121,401.00
– Each total is going to be the price of the item, times the Qty sold of the item. If we had 3,000 records,
we would not want to type the equation 3,000 times
O Our equation is a pattern, “the cell two away, times the cell next to me”
O Anywhere you copy or fill the equation, the pattern will follow
– Use Fill handle to pull down this “pattern”
– Switch to Formula view (Ctrl ~) to see results and switch back to normal view
calculate Taxes
– In Cell D2 type: Tax Rate
– In Cell E2 type: 10%
– In Cell E4 type: Taxes
Page 71 of 150
– In Cell E5 type: =D5*E2
o Our pattern is Subtotal times Tax Rate
Use fill handle to pull down equation
o Don’t Panic, these answers are supposed to look weird
Absolute vs Relative (pg 6)
What Happened?
Pattern “Cell next to me * Cell three above me”
As we move DOWN the pattern continues but it’s wrong for this equation
Erase the answers
In Cell E5 type the same equation: =D5*E2
Before you accept, press the F4 button on the keyboard to lock cell address E2
=D5*$E$2
you can type in the $ signs, but F4 is often faster
Think FORCE for the F4 button
D5 is relative (always cell next to me)
$E$2 is absolute (always cell E2)
Use fill handle to pull down the equation
Calculate Totals
– In Cell F4 type: Total
Page 72 of 150
– In Cell F5 type: = D5+E5
– Use fill handle to pull down the equation
Calculate Grand Total
In Cell E8 Type: Big City Store Grand Total O From the
Home tab, Align Right
If it won’t let you, make sure you have accepted your entry (enter or )
Click in Cell F8
Use AutoSum button Sigma ‐ ∑ in the Editing group on far right
of the Home tab
O =SUM(F5:F7)
O In English, this reads “Calculate the Sum of F5 through F7”
Page 73 of 150
Note: When you click the AutoSum button Excel looks for numbers above the current cell. If it can’t
find a number, it will look to the left. The AutoSum button has a drop down menu to do quick
calculations for Sum, Average, Max, Min, and Count.
Confirm your answer
Select cells F5:F8
Look at the status bar in the bottom right of the window to see the Average, Count, and
Sum O Sum should be $740,183.40
Change the fill color for cells F5:F8 to yellow
These are now our “Yellow Numbers”
Set up for Functions
– In Cell C9 type: Grand Total
– In Cell C10 type: Total Avg
– In Cell C11 type: # of Items
– In Cell C12 type: Largest Sale
– In Cell C13 type: Smallest Sale
– Right align all titles in cells C9:C13
– click in cell D10
Page 74 of 150
O We can tell we are in cell D10 because of the Name Box
– Press the Equal Sign =
O The Name Box has changed to a list of Most Recently Used Functions
O Since the last function used on this computer is SUM, it’s listed first
–
–
–
–
–
– Choose SUM
O This will open the Function Arguments window
 Clear the contents in the Number1 box
Page 75 of 150
 Move window so you can see the yellow numbers
 To move: click in the title bar or any grey blank space and drag
 Once you can see the yellow numbers, click in the first one, cell F5
 Drag to the last yellow number, F7
 Selection should be F5:F7
– Sum should be $740,183.40
– Clear Cell D10
– In Cell D10 Type: =
– Open the List of Recent Functions from the Name Box
– Click on the last option, More Functions…
O If you click on a function name,
Excel gives a brief description.
O Click on the Help on this
function link for a Help
article about this function,
usually with examples.
Page 76 of 150
Erase the “Type a brief description of what you want to do and then click Go” from the Search box.
Type in ADD and click the GO button.
 Select Sum from the list and click OK
 This opens the same Function Arguments window
 Choose your Yellow Numbers and Click OK
 Sum should be $740,183.40
 Clear Cell D10
Editing a function (pg 8)
Press the Equal Sign
Choose SUM from function list
Click OK
Answer should be WRONG
Why? Because we don’t have the yellow numbers
Click on the Edit Function box in front of the formula bar
Page 77 of 150
This opens the Function Arguments window
Choose the Yellow Numbers and Click OK
Other Basic Functions
– Click in Cell D11
– Press Equal Sign =
– Click on List of Functions
– Choose Average (aka Arithmetic Mean)
– Choose Yellow Numbers and Click OK
– Using Yellow numbers for each
O In cell D12 calculate the Count
O In cell D13 calculate the Max
O In cell D14 calculate the Min
Final Result
Page 78 of 150
Reset for next lesson
Clear the calculated taxes, Cells E5:E7
Notice how it carries through to all the answers
Clear the Grand Total in Cell F8
Clear the five functions you created in Cells D10:D14
Press Ctrl‐Home to return to the top of the worksheet
Naming a Cell (pg 9)
 Click in cell E2 (10%)
 Click in Name box
 Erase E2 in Name box
 Type TaxRate
 No Spaces, No Hyphens, capitalization doesn’t matter
Page 79 of 150
 Press Enter and TaxRate should still be in the box
 Click anywhere else, see cell address in name box
 Click on E2 (10%), see “TaxRate” in Name Box
 Click anywhere else
 Click arrow next to Name box and choose TaxRate
 Should jump to cell E2
 Go to Sheet 1, Click on Name box menu, Choose TaxRate
 Should jump to Worksheet 3, Cell E2
 We have made a “bookmark” in our workbook
Using named cell in equations
In Cell E5 type =D5*
Click on cell E2
 Instead of E2 we see TaxRate
 TaxRate is always TaxRate, no ($) locks needed
Page 80 of 150
Accept and fill down for E6 and E7
Naming Ranges
– Select Yellow numbers
– Type Total in the Name box and press Enter
Using named cell in functions
– Click in cell F8
– Use AutoSum button Sigma ‐ ∑ O
=Sum(Total)
– In Cell D10 type: =
– Choose Sum from the List of Recently Used Functions
– Select your Yellow numbers, you should get Total
– Click OK
Typing in Functions
Clear cell D10
Page 81 of 150
In Cell D10 Type: =SU
See list of functions that start with an SU
Once you see the word SUM Select it
Double‐click or press Tab (to GRAB!)
Continue to type: TO
See list of all functions and Names that start with TO
Double‐click Total or tab
Press Enter to accept
Try the same for the other four functions. Type an equal sign, start typing the name of the
function. Once you see it, double‐click, or tab to grab the selection. Then type the first few
letters of total. Double‐click or tab to grab. Enter to accept.
Cell D11 the Average
Cell D12 the Count
Cell D13 the Max
Cell D14 the Min
Page 82 of 150
Answers will be the same as the ones on
the top of Page 19 of this handout.
Working with Ranges of Data
If we insert new data it must go inside the range
Insert two rows, one above item “C”, one Below item “C” O
Right‐click on Row Number 7, INSERT
Right‐click on Row Number 9, INSERT
Enter in data
 In Cell A7 Type: D
 In Cell B7 Type: 159
Page 83 of 150
 In Cell C7 Type: 951
 In Cell A9 Type: E
 In Cell B9 Type: 357
o In Cell C9 Type: 753
– Select Cells D5:F5
O The first SubTotal through the First Total
O The numbers should not change, just be selected
– Use fill handle to pull down through all the yellow numbers
Freeze Panes
– Click inside Cell B5
Page 84 of 150
– From the View tab in the Window group, choose
Freeze Panes
This will lock the first four rows and the first column into place, so as you scroll through the
worksheet you can always cee the content in those cells.
Freeze panes above
and to the left of
THIS cell. (B5)
If Function (if there is time)
 In Cell G4 type: Continue
 Draw Logic Tree
 If our Total for this item is greater than $250,000, then yes we want to continue
 If our Total for this item is less than $250,000, then no we don’t want to continue
Page 85 of 150
IF Item Total > 250000
T F
Yes No
– In Cell G5 type: =
O From the list of functions choose IF and fill in the parts from our logic tree
– Use fill handle to pull down the equation
– Edit the function in cell G4 to 280000
– DON’T FORGET TO: Use fill handle to fill down again
harts
A chart helps you display your data into a graphical representation. There are many types of charts, but
in this class we’ll focus on simple column, line, and pie charts. There are examples of other charts near
the end of this handouts.
The first thing to know is the data has to be organized so Excel can understand what you are trying to
chart. Excel will chart your data selection or your connected data range. As long as there are no blank
columns and no blank rows within your dataset, you can skip selecting the cells.
Page 86 of 150
Chart Title
Value Axis
Here is a dataset we will use in class:
Item 1st Qtr 2nd Qtr 3rd Qtr 4th Qtr
Pants 456 489 423 468
Shoes 498 435 472 436
Socks 128 168 157 138
Blouses 579 498 531 589
Hats 126 129 123 119
This is a structured collection of related data set in a table
format. When plotted onto a clustered column chart, like
the one shown above, the titles in the first column of the
dataset appear along our category axis. The titles in the
first row appear within the legend. The values are
represented by the height of each column.
Line charts are usually set up to go across a period of time,
think Time Line. For this chart I’ve used the Switch Row
Column tool so we can see the trend of the sales through
the year. In this case our first column titles appear in the
Page 87 of 150
legend, and the first row of titles appears in our category
axis.
Pie charts are usually created to display the breakdown of
the total values within the whole. Pie charts can only be
based on one set of data. When you try to create one with
the above dataset, you will only see the first value set
appear within the chart. If you want to go to an extreme
and have all four quarters show, try using a Doughnut
chart.
Creating a Chart
To create a chart make sure your cursor is in the dataset you would like to plot. If you want a subset
of the dataset, select that portion. You can use your Ctrl key to add to a current selection.
You will find the Charts group on the Insert tab. Click on any small chart button to see a list of possible
charts.
Page 88 of 150
If you are unsure of the best chart option for your data use the Recommend Charts button. It will
open the Insert Chart window shown here.
Chart Tools
Page 89 of 150
When you select a chart, three buttons appear along the right side of the selection.
The plus sign is the Add Chart Element button. This option is used to add and remove different pieces of
your chart. The list of options will vary depending on the type of chart. For example, a pie chart will not
have a set of category axis titles. As you hover your mouse over each option, you will see a small arrow
head pointing to the right. This will open another menu with more detailed choices. Each menu also has
a “More Options…” button which will open a Format Pane on to customize each chart element.
There is a Chart Style Gallery and a Colors menu on
the Design tab, but the Chart Styles button, the
paint brush next to the chart, offers the same
options.
Page 90 of 150
If you are patient while you hover over each option,
Excel will provide you with a Live Preview of the
result.
The Color options are available at the top of the
menu.
The third button is a funnel. This is a Chart Filters button.
The Values group allows you to add and remove data points from the chart.
The Names page allows you to change the labels that appear in the legend (series) and axis titles
(category).
The Select Data… option at the bottom of the window opens the same window as the Select Data
button on the Design tab. From there you can change or adjust the range of cells used to create
this chart.
Chart Tool Tabs
When a chart is selected two chart tool tabs appear at the end of the ribbon, Design and Format.
Design Tab
1. Add Chart Element – A menu of chart elements that can be added or removed to the chart. Each
option will have a expand arrow at the end of the element name that will provide specifics and a More
Page 91 of 150
Options button to open the Format Pane. This is the same as the Add Chart Element button that appears
next to the selected chart.
2. Quick Layout – A variety of layouts that offer suggested views and choices that adjust
the chart elements such as adding a title, varying the space between columns, and moving the
legend.
3. Change Colors – Different color that can be applied to your chart. Changing the Theme on
the Page Layout tab will give you a different sets of colors.
4. Chart Style Gallery – Different chart styles that can be applied to your chart. Because Excel
automatically adjusts the Ribbon to fit on your screen, your copy of Excel may show less options than
the picture above. Use the scroll arrows and open menu buttons at the right side of the gallery for more.
Page 92 of 150
5. Switch Row/Column – Changes the direction the chart
looks at the data. In our column chart, each column is plotted on
the chart, when we Switch each row is plotted. We are swapping
the category labels with the legend labels.
6. Select Data – Opens a Select Data Source window
where you can customize the source of the chart data,
even edit the labels. Use this window to reorder your
legend and change how Line charts deal with blank cell
values.
7. Change Chart Type – Opens Insert Chart
window where you can change to other chart types. If
you have multiple series you can change each to be
different chart type by choosing the Combo chart type
from the bottom of the left pane.
8. Move Chart – By default when you create a chart it
is placed on the same worksheet as your data set. You can
move the chart to its own worksheet or to any existing
worksheet with the workbook.
ITEM 1ST QTR
2ND
QTR
3RD
QTR
4TH
QTR
PANTS 456 489 423 468
SHOES 498 435 472 436
SOCKS 128 168 157 138
BLOUS
ES 579 498 531 589
HATS 126 129 123 119
Page 93 of 150
Format Tab
Current Selection
a. Chart Elements – This box shows the currently selected Chart Element, and the menu provides
a list of the major chart elements. Choose an item from this list to select that element.
b. Format Selection – Opens the Format Pane based on the current selection shown in the Chart
Elements box.
c. Reset to Match Style – Changes the current selection to match the original style of the chart.
2. Insert Shape
a. Shape Gallery – Use this gallery to find a shape such as a block arrow to add to
your chart.
b. Change Shape – Use this tool to change the current shape to a different one,
perhaps a rectangle into a rounded rectangle.
3. Shape Styles
Style Gallery – Different shape styles, options will vary based on the current selection.
Shape Fill – Menu of the most common fill colors and options, such as pictures and textures. For
more options, open the Format Pane.
Page 94 of 150
Shape Outline – Menu of the most common outline colors and options, such as dashes and
arrows. For more options open the Format Pane.
Shape Effects – Menu of the most common shape effects, such
as shadows. For more options open the Format Pane.
4. WordArt Styles
WordArt Gallery – Different WordArt styles
Text Fill – Menu of the most common fill colors and options,
such as pictures and textures.
Text Outline – Menu of the most common outline
colors and options, such as dashes and line weight.
Text Effects – Menu of the most common Text effects, such as
shadows. For more options open the Format Pane.
5. Arrange – Change the alignment and arrangement of multiple charts. Use the Shift key
to select more than one chart at a time.
6. Size – Change the height and width of the chart.
Format Pane
There are multiple ways to open the Format Pane.
Click on the Format Selection button in the Format tab
Click on More option from any menu
Page 95 of 150
Right‐click on a chart element and choose Format…
Double‐click on a chart element
The format pane can remain open for as long as you need it. The properties shown change
depending on the current selection. The current selection is shown on the Format tab and in the
title of the Format Pane.
The pane can be pulled free from the side by dragging the title toward the middle of the window. To
return the pane to the side of the window drag it back into place or double‐click the title of the Format
Pane.
To close the pane, click on the X in the upper right hand corner. If you accidentally close the pane,
use any method above to reopen it.
Within the Format Pane, click on each icon to see the subset of properties. Click on the expand arrow
in front of the options to see the relevant properties.
Fill & Line Effects Size & Properties Options Text Fill & Outline Text
Effects Text Box Options
The Fill & Line and Effects options are the same for all the of the chart elements. If an option cannot
be applied to that chart element, Excel will disable (grey out) that option.
Below are the fill options for a Chart Element. Each Fill choice provides a new set of options.
Page 96 of 150
How do I …?
Change Axis Numbers
Select the Axis by clicking on a number in the area. Open the Format pane, be sure the title says
Format Axis. Click on the Options button.
From here you can:
 Change the Minimum and Maximum numbers shown. These can be greater than the
minimum and less than the maximum if you want.
 Change the Major unit, this is how the displayed number is chosen. If the major unit is
100 the chart axis will read 100, 200, 300. If it’s 25 the chart will show 25, 50, 75.
 Change the Display Units to Thousands, Millions, Billions. This will change the unit
 shown in the labels and data tables as well.
 Change the Format of the numbers; number of decimals, include a dollar sign, etc.
Page 97 of 150
Change Distance Between Columns
Select any column. Open the Format pane, be sure the title says Format Data Series.
Click on the Options button.
From here you can:
 Change the selected series to be on a secondary axis
 Change the distance the series overlap
 Change the width between the each category grouping
Explode a Pie Chart
In the chart: Hover over a pie wedge. Click and drag the piece away from the center. To move one
piece at a time, select the single pie wedge first, and then move it from the middle.
In the properties: Select a pie wedge. Open the Format pane, be sure the title says Format
Data Series. Click on the Options button.
From here you can:
 Change the rotation without changing the order of the data
 Change the explosion, how close the wedges are to each other
Page 98 of 150
Add Trendlines and Error Bars
Select the chart. Click on the Add Chart Element button in the Design tab, or on the button next to
the chart.
You can add your own custom error bars, if needed, from the error bar options. You do have to
format one series of error bars at a time.
Make Charts the Same Size
Use the Height and Width properties found on the Format tab in the ribbon, or on the Format Pane for
the Chart Area’s Size & Options. You can use the alignment options on the format tab to make the
charts line up.
Changing the Data Source
From the Worksheet
When you select a chart you will see the Chart
Tool tabs in the ribbon, and the three options
buttons along the right side of the chart. If you
can see the cells in the worksheet used for the
chart you will also be able to see the data is
selected and each section is shaded.
If you hover your mouse over the bottom right‐
hand corner of the data grouping you will get the
two‐way sizing arrow. If you click and drag the
Page 99 of 150
selection you can manually change the chart data
source.
If you know you will have more categories and
series you can grow the data area beyond what’s
showing and Excel will assign new colors and
make room in the chart for the new values.
From the Select Data Source Window
From the Chart Tools Design tab choose Select Data.
The Chart data range option can be a bit finicky
so I recommend deleting the current range and
selecting the new set from the worksheet.
The chart is initially arranged to follow the order
of the data, but if you would like the legend in a
different order, you can rearrange the Legend
Entries using the up and down arrows.
Removing data
Both of the above options will help you add and remove data. You can manually adjust the range in the
worksheet or you can select a different range from the Select Data Source window. Both are great as
long as you are using a consecutive range of data.
The Select Data Source window also had a Remove button to delete a series
from the chart. Notice there is not one for the Category/Axis labels. To be
able to remove one you will need to first Switch Row/Column. Once you’ve
removed the categories, Switch Row/Column again.
Page 100 of 150
From the chart itself you can click on the series you want to remove and press Delete on the keyboard.
You can only delete the series, so the same actions apply in order to remove a category you will need
to switch the row/columns first.
However, we now have a Filters button along the side of the selected chart. From here we can uncheck
any of the values we do not want on the chart; Series and Categories. You must click the Apply button
at the bottom of the menu for the filter to take effect.
Sorting and Filtering
Sorting Data
On the far right side of the Home tab you will find a large Sort &
Filter button. The menu you see when you click on the button is
reflected in the Sort & Filter group of the Data tab.
If you make a selection of cells, Excel will think you only want to sort or
filter by that selection. But if your
dataset has no blank rows and no
blank columns Excel will see the
whole range as one data set.
You can have blank cells, but not completely blank
columns/rows; if you are not sure that your dataset is consistent, click inside one cell, and press
Ctrl‐A. This will select all the cells within the dataset. A second “Ctrl‐A”, or pressing the shortcut
in an empty cell, will select the entire sheet.
When you have completed a sort, you can click the Undo button (or Ctrl‐Z). Excel will undo the
sort and it will select the dataset it used in the sort. This is another way to see your dataset.
Page 101 of 150
Ascending Sorts
1) Text: Sort alphabetically from A to Z
2) Numbers: Sorts from smallest number to largest number
3) Dates: Sorts from the newest date to the oldest date
Descending Sorts
3) Text: Sort alphabetically from Z to A
4) Numbers: Sorts from largest number to smallest number
5) Dates: Sorts from the oldest date to the newest date
Custom Sorts
When you first open this window, Excel will show the most recent sort options. If you haven’t
created a sort yet, this window may be blank.
Page 102 of 150
In Excel 2016, we can sort by 64 levels. From this sort window we can add levels, delete
levels, copy levels, and even change the order of our sort using the up and down arrows in
the toolbar.
Column: The column drop‐down menu will show the names of your columns, your ‘fields’. If
your data doesn’t have titles Excel lists the column heading letters instead. If you were expecting
titles, but is only showing the column letters, you can click on the check box in the upper right
hand corner of the Sort window to let Excel know your data has headers.
Sort On: You can Sort on the values of the cells, the cell colors, the font colors, or the cell icons.
Order: The order options change depending on the values in the cells.
Text Number Date
Font Color Cell Color
Custom Lists
Custom lists can be built through the Excel Options under the File menu in the Advanced section
under General. Or by choosing Custom List… option at the bottom of each order box above.
Page 103 of 150
If you choose this option, you will be able to select from one of these lists. Alphabetically, April
comes before January. With the Custom List order, we can ensure January comes first.
These custom lists will work as patterns with the fill handle. Notice the “Shopping” list? Once I
set this up, I can type any of the words in a cell and use the fill handle to follow this pattern.
Sort Options
Case sensitive: Sort lowercase letters before uppercase letters
Orientation: Sort vertically (top to bottom, sort rows) or
horizontally (left to right, sort columns)
Filtering Data
Filters hide rows (records) based on criteria you set. You can turn the filter on and off by choosing
Filter from the Sort & Filter button on the Home tab, or choosing the Filter button on the Data
tab.
Excel will place a drop‐down arrow at the end of each cell in the title row (the first row of the
dataset). When you click on this arrow we see several options including our sort orders:
1) Sort Ascending, Descending, and by color
2) Clear the Filter
Page 104 of 150
3) Filter by Color
4) Set a custom filter (text, number, date)
5) Search for a matching value in the column
6) List of values in the column (field). Select All will
toggle between everything and nothing.
Once a filter has been set Excel will hide all the rows that don’t match the criteria. The status bar will
show how many records (rows) were found that matched. The row numbers of the original data will
remain the same, but will appear blue. The dropdown arrows of the columns that are being filtered
will show the filter icon (funnel). The double line between the row numbers indicate hidden rows.
Custom Filters
Depending on the data in the column you will have the option to set a custom filter based
on text, numbers, and dates.
Page 105 of 150
Text Filters Number Filters Date Filters
If you choose one of the options on the Filter List with the ellipsis (…),
you will see a Custom Auto Filter window such as this. From here we
can set up to two filters.
Be careful with the AND/OR relationships. If you ask Excel to show the rows where the City equals
Jacksonville AND the City equals Gainesville, you will get no results, because one cell cannot equal
both values. But if you ask for the same using the OR, Excel will show all the records for both cities.
Page 106 of 150
Or’s tend to work for exact matches (Equals This OR Equals That), whereas AND’s tend to work for
ranges (Greater than This AND Less than That).
You can use the “Wildcards” ? and * to help you with your filter. ? is used for one character, *
for multiple.
Equals Jacks* ‐> Jacksonville, Jacksonville Beach, Jackson Heights
Some of the filter choices may work just as well. I could say Contains ‘Jacks’ or Begins with ‘Jacks’.
SUBTOTAL Worksheet Function
We can do common mathematical functions with our filtered lists using the SUBTOTAL worksheet
function. The syntax is for this function is “SUBTOTAL(function_num,ref1,ref2,…)”.
Function_num is the number 1 to 11 that specifies which ‘function’ to use in calculating subtotals
within a list (see below). The ref1, ref2… are the ranges of data that should be used in the equation,
there can be up to 29 different ranges used in this function.
Function numbers 1 through 11 will include manually‐hidden rows, ones you have hidden
yourself. Function numbers 101‐111 will exclude your hidden rows from the function. Filtered‐
out rows are always excluded.
Function_Num Function Function_Num
Functio
n
1 AVERAGE 7 STDEV
2 COUNT 8 STDEVP
3 COUNTA 9 SUM
4 MAX 10 VAR
5 MIN 11 VARP
6 PRODUCT
Page 107 of 150
Page 108 of 150
Using the IF Function
The If function allows users to make logical comparisons between values, returning a value of
either True, or False. Based on the result of the logical comparison, the if function can carry out a
specific operation, based on a true or false value.
The syntax for an if function is: =IF(logical_test, value_if_ture, value_if_false)
Logical test: any value or expression that can return a TRUE or FALSE
Value_if_true: The value to be returned, or operation to be performed, if the condition is TRUE. This
can be text (must be in double quotes “text”), a number, or a function.
Value_if_false: The value to be returned, or operation to be performed, if the condition is
FALSE.
Page 109 of 150
Excel Comparison Operators
Comparison Operator
• = (equal sign)
• > (greater than sign)
• < (less than sign)
• >= (greater than or equal to sign)
• <= (less than or equal to sign)
• <> (not equal to sign)
Steps for an If Statement:
1. Place cursor in the cell that will contain the If Statement.
2. Navigate to the Formulas tab, and click on the Insert
Function icon.
3. Within the Insert Function
window, search for If in the search
textbox and hit the Go Button.
4. Select the if function from the select
a function box. Select it by either
double clicking on it or by selecting
it and clicking the OK button
Page 110 of 150
5. In the Functions Argument window, we are able to see each argument of the if function.
For the logical test, we want to select the Cell that we are comparing.
6. Value_if_true. What do we want to happen if our argument is True?
a. This can be as simple as a number, text(must be in quotes “”, an arithmetic
function, or even another function entirely.
7. Value_if_false. What do we want to happen if our argument is False?
a. This can be as simple as a number, text (must be in quotes “”, an arithmetic
function, or even another function entirely.
8. When we have all of our arguments filled out, click on the OK button and our function
is now complete.
9. To copy this function to the remaining cells, click
back on the cell that contains the original function.
Move your mouse into the lower right corner until
your mouse turns into a dark plus sign.
10. Click and hold with your mouse as you drag to the bottom of the rest of the data. The
function has now been copied to all of the other cells.
Page 111 of 150
Best practices with if statements
Generally, it is not good practice to put static values (values that may need to change from time to
time) directly into functions in Excel. The main reason for this is because if a change ever has to be
made, the values can be hard to find and change. It is best to put these values into their own individual
cell, and then reference the cell containing the value in the function. This way, any time the values has
to change, the referenced cell’s value is changed and all functions related to that function are now
updated immediately. There isn’t a need to search for each function to update them.
Display a formula with color coded cell references
When troubleshooting a function while in Excel, hit the F2 key. This will display all arguments in
a function as color coded cell references and cells.
Cell References
When creating functions in Excel, users must know that there are two different cell reference
types, Relative and Absolute.
Relative cell reference
It is important to understand how Excel identifies cells in formulas. By default, all cell references
are relative references, which means the function is related to the cells around it. As a function
is copied across multiple cells, it is updated based on the relative position of rows and columns.
The way Excel reads a function is by looking at the relationship of the cell references in relation to
the cell containing the function. For example, in the
Page 112 of 150
function contained in cell C3 to the right, Excel
reads this as “take the number located two cells to
the left and add that to the number in the cell
located one column to the left”. Therefore, when this function (which appears as =A2+B2) is copied to
the cell below, it performs the calculation using the same pattern, “take the number located two cells
to the left and add that to the number in the cell located one column to the left”, but updates the
formula to reflect the appropriate cell addresses. This is called a Relative Reference and is the feature
that enables users to copy the same function to a different location within a worksheet.
Absolute cell reference
An absolute reference is a reference to a particular cell, or range of cells, that never updates as the
function is copied to a new location. Absolute references can be made to an individual cell, or to keep
a row or a column constant as the function is copied to a new location.
To make a cell reference absolute so that it will not adjust when a formula is copied, insert a dollar
sign ($) in the appropriate position. By navigating into the formula in the Formula bar, users can cycle
through the absolute reference options by hitting the F4 key on the keyboard. The first time the F4
key is pressed, a user will create an absolute reference to the cell reference in the function, the second
time the reference will be to the row only, and the third time the reference will be to a particular
column.
Press the F4 key one time
Page 113 of 150
Two times
Three times
Example 1: $B$12 Both the Row and the Column are held Constant
Example 2: B$12 The Row is held constant; the Column will update as the function is copied.
Example3: $B12 The Column is constant; the Row will update as the function is copied.
Using OR & AND Functions
The And & Or functions are great functions to use within an if function because they will return a
value of either True or False, which is what the first portion of an if statement needs in order to
complete the function.
The And and Or functions will allow for multiple logical expressions to be created within the argument
of the function. Each logical expression must be separated by a comma.
And Function
The And function is used to check multiple logical expressions and will return a True value if every one
of the logical expressions is true.
The syntax is; =AND(logical1,[logical2],…)
OR Function
The OR function is used to check multiple logical expressions and will return a True value as long as
one of the logical expressions is true.
The syntax is; =OR(logical1,[logical2],…)
Nesting an AND or OR function into an If function
Page 114 of 150
The easiest to incorporate an AND or OR function into an if function is to first start with the And or
Or Function and then incorporate the function into the logical_test argument of an IF function.
Steps to create a nested AND or OR function within an IF function;
1. Navigating to the cell to contain the IF function and type in an equal sign, followed by AND. Select
the AND function by double clicking on it, or using the arrow keys to select the function and then hit
the tab key.
2. When the AND function is complete, finish the function by entering in a closed bracket ).
4. Now, use the mouse and place the cursor between the equal sign and the A in the front of the AND
function.
Users may also use the formula bar to do this, as it may be easier to access the first portion of the
function in the formula bar.
With the cursor between the equal sign and the A, start typing if. Excel will populate a list of functions
under the cell. Either double click on the If function, or use the arrow keys to highlight IF and then hit
the tab key.
Excel will now display the arguments for the IF function underneath the cell, or the formula bar,
depending on where the user is working. The argument that will be bolded will be the logical_test,
which has already been completed
with the AND function. To enter in to the second portion, value_if_true, of the expression, move the
cursor to the far right of the AND function, after the closed bracket and type in a comma.
Page 115 of 150
6 Enter in what is to be done if the AND function is true, then type a comma to get to the last
portion of the IF function, the value_if_false.

When all arguments have been fulfilled, hit the type in a closed bracket to complete the
Note: Users may hit the Enter key to complete the function, which may result in the following error;
This error is just notifying the user that the last closed bracket
is missing. By accepting this error, Excel will insert the closed bracket to complete the function.
Nested IF Statement
Sometimes there is more than one condition that needs to be tested in order to return a value. To test
multiple conditions, users can use a nested IF function. A nested IF statement is and easy way to test
for multiple conditions. Typically, the first condition will be tested and an answer will be provided if
the condition is True. If the condition is false, a new IF statement will be used to test in the
value_if_false portion of the first if statement.
The syntax would look similar to this;
=IF(logical_test,value_if_true,IF(logical_test,value_if_true,value_if_false))
Note: Excel can nest up to 64 if statements.
Tip: When comparing values within a scale, make sure the values are sorted in either increasing or
decreasing order to help make the nested if statement easier to assemble. If the scale is in Increasing
order, the less than < sign will be used in the arguments and if the scale is in decreasing order, the
greater than > sign will be used in the arguments.
Steps for a Nested IT statement.
1. Position the cursor in the cell that will contain the formula. Type in an equal sign, followed
by if. When if populates under the cell, either double click on if to select it,
or use the arrow keys to highlight it and then hit the tab key to select the IF
function.
Page 116 of 150
2. Enter in the first logical test within the If function, then hit the comma key to get
to the value_if_true.
3. In the value_if_true portion of the argument, enter in what is to be done if the first logical
test is true, and then hit the comma key to get to the value_if_false.
4. In the value_if_false, a new if statement will have to be started since we are comparing
multiple logical tests. To start a new if statement, start typing IF within the value_if_false portion, the
exact same way we would start a brand new IF function, but without the equal sign.
Excel will populate the if function underneath the cell that is being typed in. Select the if function, just as
if this was a brand new if statement that was being started, by double clicking on the If, or by highlighting
it and then hitting the tab key.
When this is done, users will notice that the argument below the cell changed from being in the
value_if_false portion of the first IF function to now being the logical_test of the second if statement.
Tip: Treat this new IF function as its own, free standing if function by entering in all arguments within
the IF function.
In the second IF functions logical_test, enter in the second comparison and then hit the comma key to
get into the value_if_true.
Page 117 of 150
In the value_if_true portion of the argument, enter in what is to be done if the first logical test is true,
and then hit the comma key to get to the value_if_false.
In this example, we are only comparing two values, so in the value_if_false portion, enter in what is
to be performed if neither of the logical_tests are met.
Note: If more logical tests were being compared, another IF function would be entered into the
value_if_false portion, following the same steps that were taken in step 4.ss
After the value_if_false portion is completed, complete the second IF function by typing in a closed
bracket ).
notice that the IF function argument below the cell will now display the value_if_false argument of the
first IF function. Since the second IF statement has completed the value_if_false portion of the first IF
function the nested if is now complete.
To complete the nested IF function, either type in another closed bracket ), or hit the Enter key.
Note: Users may also hit the Enter key after completing the second IF function. If this is done, Excel
will populate a window indicating that there is a typo in the formula, meaning there aren’t any closed
brackets to complete the function. To have Excel enter in the closed brackets, hit the Yes button.
Page 118 of 150
To verify the nested IF was entered in correctly, click on the cell containing the IF function and then
hit the F2 key. This will display the function with colored cell references related to the cells on the
worksheet.
s
PIVOT TABLES
Learning Objectives
 PivotTables
 Formatting
 Pivot Tables ‐ Excel 2016 Help File
 Exercises
 Things to Remember
Page 119 of 150
PivotTables
PivotTables are summary reports. They give you the ability to take a large boring set of
repetitive data and summarize it into a neat table that you can very easily rearrange, filter,
format, and even chart.
Here’s an example of a repetitive raw data set, three PivotTables, and a PivotChart.
Planning
The most important part of building a PivotTable is planning. You have to remove yourself
from the raw data and think about the final result. There’s a learning curve, have patience with
yourself and with Excel and you’ll get there.
Page 120 of 150
The Data
The data has to be repetitive. It looks wrong at first, but the more boring and repetitive your
data, the more you can do with it. In the Large Data 1 class we learn about sorting and filtering
in Excel. We don’t need to keep each quarter, each department, each person on a different sheet.
If we keep all of the data in the same place, we can look at each category, one at a time, using
the filter, and beautifully because it is all in the same place, we can look across categories and
quickly summarize with a PivotTable.
The data has to be consistent. We don’t want to see entries like: 4th Qtr, 4th Quarter, Qtr 4.
In the Large Data 3 (vLookups) class we’ll learn about validation rules, and in Large Data 4
(report) we’ll see how to use vLookups to help us cleanup inconsistent data entry.
The definition of a database is a structured collection of related data. Rows of a
data table are records.
When we filter in Excel, the Status Bar tells us how many Records were found.
Columns of a data table are fields. The column titles of your original dataset will appear in the
PivotTable Field List. You’ll use these titles to control the structure of the PivotTable. If possible,
use clear, concise, and unique column titles. If you use the same column title more than once,
Excel will add a number after the subsequent titles. Example: Home Address, City, State, Zip,
Work Address, City, State, Zip becomes Home Address, City, State, Zip, Work Address, City(2),
State(2), Zip(2).
The Result
By all means, jump in and play with the tools, get comfortable with how to make a PivotTable.
But when it comes down to needing a specific report, you have to “show your work”. Think about
what it is you actually want to see.
Page 121 of 150
“I want to know how many items we ordered.” Okay. Do you care what the items were? Do
you care about the data across the year, or do you only want the total? What about the other
details? Each versus boxes? Would you like to compare your orders to another departments?
“I want to know how many patients were admitted last month.” Okay. Do you want to see the
break down by time, perhaps by morning and afternoon? By shift? By Department? Or would
you like to have the flexibility to change (filter) the shifts and departments?
The more you think about what you want out of the glorious summary report known as a
PivotTable, the better it will come together for you.
Building
The PivotTable field list shows the column titles of our original dataset, these are our Fields. If
you rename, add, or delete columns in the dataset you will not see the change here until you
refresh the data. The Refresh button is on the Analyze tab of the PivotTable Tools, and can be
found on the shortcut menu if you right‐click inside the table.
Fields in the Filters will appear above the table.
Fields in the Columns will appear at the
top of each column of the PivotTable.
Fields in the Rows will appear at the left of
each row of the PivotTable.
Fields in the Values will be summarized.
By default, text and date fields will be
counted, number fields will be summed.
Page 122 of 150
Arranging Fields
Adding
Click the check box in front of the field name
a. Text fields will go into the Rows showing each unique value from the dataset
b. Date fields will go into the Rows grouping the values across time
c. Number fields will go into the Value as a sum
2. Drag fieldname from the field list to an area
a. You will have to drag to add a field to the value area multiple times
3. Right‐click on the fieldname in the field list and choose an area
4. Moving
Drag fieldname from an area to a new area
Right‐click on the fieldname in the field list and choose a new area
Left‐click on a field in an area and choose a new area
Deleting
Drag fieldname out of the area section
Left‐click on a field in the area and choose Remove Field
Page 123 of 150
Value Field Settings
Fields added to the value section are summarized within the grouping of the row and column
headings set in the PivotTable. Numbers will sum, other values will be counted. To change
how the data is summarized, left‐click on the fieldname in the Values area and choose Value
Field Settings.
You can reformat your numbers with the Excel formatting tools, but if you reformat from the
Value Field Settings window you’ll format the Table, this means if you change the structure of
your table by adding fields you won’t have to reformat any new cells occupied by the table.
Page 7 of this handout discusses the Show Values As options.
It is possible to create other summary options, but it’s beyond the scope of this class. If
you would like to explore, look at the Fields, Items, & Sets option on the Analyze tab
Formatting
The PivotTable Design tab has lots of style options to make the PivotTable look good.
Change the Options settings to see how they vary in the style you chose.
Page 124 of 150
The style options help with the look of the data, but in my opinion the first set of buttons on
the Design tab are way better, as they determine how the data is pulled together within the
PivotTable.
Original Subtotals at Bottom, Blank Rows inserted
No Subtotals, no Grand Totals
Report Layout: Outline
Report Layout: Tabular, no Subtotals, Blank Rows inserted
Notice with the Outline and Tabular Report Layouts the Row Labels are no longer grouped,
instead you see a heading for each column and row label.
Create a PivotTable
Page 125 of 150
If you have limited experience with PivotTables, or are not sure how to get started, a
Recommended PivotTable is a good choice. When you use this feature, Excel determines a
meaningful layout by matching the data with the most suitable areas in the PivotTable. This
helps give you a starting point for additional experimentation. After a recommended
PivotTable is created, you can explore different orientations and rearrange fields to achieve
your specific results.
1) Select the PivotTable that looks best to you and press OK. Excel will create a PivotTable
on a new sheet, and display the PivotTable Fields List.
1) Click a cell in the source data or table range.
2) Go to Insert > Tables > PivotTable.
If you’re using Excel for Mac 2011 and earlier, the PivotTable button is on the Data tab in the
Analysis group.
3) Excel will display the Create PivotTable dialog with your range or table name selected. In
this case, we’re using a table called “tbl_HouseholdExpenses”.
In the Choose where you want the PivotTable report to be placed section, select New
Worksheet, or Existing Worksheet. For Existing Worksheet, you’ll need to select both the
worksheet and the cell where you want the PivotTable placed.
Page 126 of 150
1) If you want to include multiple tables or data sources in your PivotTable, click the Add this
data to the Data Model check box.
2) Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.
Working with the PivotTable Fields list
In the Field Name area at the top, select the check box for any field you want to add to
your PivotTable. By default, non‐numeric fields are added to the Row area, date and time
fields are added to the Column area, and numeric fields are added to the Values area. You
can also manually drag‐and‐drop any available item into any of the PivotTable fields, or if
you no longer want an item in your PivotTable, simply drag it out of the Fields list or
uncheck it. Being able to rearrange Field items is one of the PivotTable features that
makes it so easy to quickly change its appearance.
PivotTable Values
Summarize Values By
By default, PivotTable fields that are placed in the Values area will be displayed as a SUM.
If Excel interprets your data as text, it will be displayed as a COUNT. This is why it’s so
important to make sure you don’t mix data types for value fields. You can change the
default calculation by first clicking on the arrow to the right of the field name, then select
the Value Field Settings option.
Page 127 of 150
Next, change the calculation in the Summarize Values By section. Note that when you change
the calculation method, Excel will automatically append it in the Custom Name section, like
“Sum of FieldName”, but you can change it. If you click the Number Format button, you can
change the number format for the entire field.
Tip: Since the changing the calculation in the Summarize Values By section will change the
PivotTable field name, it’s best not to rename your PivotTable fields until you’re done setting up
your PivotTable. One trick is to use Find & Replace (Ctrl+H) >Find what > “Sum of”, then Replace
with > leave blank to replace everything at once instead of manually retyping.
1) Show Values As
Instead of using a calculation to summarize the data, you can also display it as a percentage of a
field. In the following example, we changed our household expense amounts to display as a % of
Grand Total instead of the sum of the values.
Once you’ve opened the Value Field Setting
dialog, you can make your selections from
the Show Values As tab.
3) Display a value as both a
calculation and percentage.
Page 128 of 150
Simply drag the item into the Values
section twice, then set the Summarize
Values By and Show Values As options for
each one.
Refreshing PivotTables
If you add new data to your PivotTable data source, any PivotTables that were built on that
data source need to be refreshed.
To refresh just one PivotTable, you can right‐click anywhere in the PivotTable range, then select
Refresh.
If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to
PivotTable Tools >
Analyze > Data > Click the arrow under the Refresh button and
select Refresh All.
Create a PivotChart
1) Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.
2) Click Analyze > PivotChart.
3) In the Insert Chart dialog box, click the
chart type and chart subtype you want.
You can use any chart type except an XY
(scatter), bubble, or stock chart.
4) Click OK.
5) In the PivotChart that appears, click any
interactive control, and then pick the
sort or filtering options.
Page 129 of 150
After you create a PivotChart, you can customize it, much like you’d do with any standard
charts.
When you select the PivotChart two buttons
appear next to the chart so you can quickly
add or change chart elements such as titles or
data labels, or change the chart style and
colors of your PivotChart the same way you
would in a standard chart.
The PivotChart Tools are shown on the ribbon.
On the Analyze, Design, and Format tabs,
you can pick options to work with or
customize your PivotChart.
Change the source data
After you create a PivotTable, you can change the range of its source data. For example,
you can expand the source data to include more rows of data. However, if the source data
has been changed substantially—such as having more or fewer columns, consider creating
a new PivotTable.
To change the data source of a PivotTable if it’s a range of cells or an Excel table, do the
following:
Page 130 of 150
Click anywhere in the PivotTable to show the PivotTable Tools
on the ribbon.
In the Table/Range box, enter the range you want to use.
Tip: Leave the dialog box open, and then select the table or range on your worksheet. If the
data you want to include is on a different worksheet, click that worksheet, and then select
the table or range.
Delete a PivotTable
When you no longer need a PivotTable, select the entire PivotTable, and press the Delete key
to remove it. If you get a “Cannot change this part of a PivotTable report” message, smake sure
the entire PivotTable is selected. Press Ctrl+A, and press Delete again.
If you’re using a device that doesn’t have a keyboard, try removing the PivotTable like this:
5) Pick a cell anywhere in the PivotTable to show the PivotTable Tools on the ribbon.
6) Click Analyze > Select, and then pick Entire PivotTable.
Page 131 of 150
7) Press Delete.
Tip: If your PivotTable is on a separate sheet that
has no other data you want to keep, deleting that
sheet is a fast way to remove the PivotTable.
PAGE SETUP
Customizing the Quick Access Toolbar
Quick Access Toolbar
File Menu
Customize
Quick
Access
(Backstage Toolbar
view)
The upper left hand corner of the Excel Window has a Quick Access Toolbar. This is a very
convenient location to place commonly used buttons. By default, this toolbar has Save, Undo,
and Redo. For this Printing and Setup workshop we would like to add the Print Preview button to
the toolbar.
There is a drop down button ( ) at the end of the Quick Access Toolbar that will help you decide
which buttons to display. This drop down list displays several common choices, including our Print
Preview and Print. When you choose a list item, Excel will place the button on the toolbar.
Page 132 of 150
If you don’t see the option you would like, you can choose More
Commands. This will open the Excel Options window. From the
Customize Section, choose the command you would like to see and
choose the Add > > button.
Page Setup
There are several of ways to customize your printouts in Microsoft Excel. Many of these options
can be found on
The Page Layout tab in the ribbon.
All of the page setup options can be found through the Page Setup window. You can view this
window by clicking any of the launch dialog box (“More”) buttons in the bottom right of any
of the sections shown here. You can also open the window by clicking on the Page Setup button
in the Print Preview.
Page 133 of 150
Page Tab
The first tab in the Page Setup window
allows you to change some general page
options.
Orientation
This option lets you set your
print out to Portrait or
Landscape. The image portrays
the actual direction of the
paper.
This is also a button on
the Page Layout Tab
and in the Print
Settings in the print
preview.
Scaling
This option can be used to change the “zoom” of the printout with the Adjust to: option or force
the number of pages the printout must fit within using the Fit to: option. Like the zoom in the
worksheet, you can adjust this option from 10% to 400%.
Page 134 of 150
Scaling is also available on the Page Layout tab in the Scale to Fit section. The
Width and Height options are the same as the Fit to: option allows you to
specify how many pages wide or tall you want your printout to be. The Scale
option is the same as to the Adjust to: option, it allows you to change the zoom
percentage of the printout.
The Scaling option in the print preview offers four options.
No Scaling – Scale to 100%
Fit Sheet on One Page – Scale Width of only One Page
Fit All Columns on One Page – Scale Height of only One Page
Fit all rows on One Page – Scale whole printout to fit everything on one page.
Paper size
This option allows you to choose paper sizes such as Letter, and Legal. This
option can also be changed by using the Size button on the Page Setup section
of the Print Layout tab, and in the Print Settings in the print preview.
Print quality
This option allows you to specify the print quality of your worksheet. The higher the
resolution (dots per inch – dpi), the better the quality of your printout. This option can only be
changed in the Page Setup window.
Page 135 of 150
First page number
By default, a printout starts on page one,
but this option allows you to start on a
different number. You might do this if you
have a cover page, or if you’re adding it to
another report. This option can only be
changed in the Page Setup window.
Margins Tab
Margins are the distance between your data and the edge of the page. Here on the second tab
of the Page Setup window you can adjust the Top, Bottom, Left, and Right margins to an
accuracy of a hundredth of an inch.
A preset list of Margins can also be found on the Page Setup section of the Page Layout tab and
in the print preview menu. If you choose Custom Margins…, Excel will open the Margins page of
the Page Setup window.
Header is the text that appears at the top of
each printed page, and Footer is the text that
appears at the bottom of every page. These
do not show in the Normal view of the
worksheet, but can be seen in the Print
Preview and the Page Layout View.
Print Titles
This option allows you to set which
Rows to repeat at the top of each
page, and which Columns to
repeat on the left of each page.
These do not have to be the first
row or column; any column can be
chosen as your titles and will be
repeated on each page of the
printout. If you click on this button
in the Page Layout tab, it will open
the in the Page Setup window to
the Sheet page.
As with the Print Area, to set the rows and
columns to repeat, click inside the blank in
the Page Setup window and use your mouse
Page 136 of 150
to select the rows/columns you would like
repeated on every page.
worksheet, but can be seen in the Print Preview and the Page Layout View.
Keep the distance of the Header: and Footer: margins smaller than the Top:
and Bottom:margins to prevent overlapping the data.
Center on page
This option allows you to adjust how the data will be placed within the set margins.
No Centering Centered Horizontally Centered Vertically Horizontally &
Vertically
Header/Footer Tab
The third tab of the Page Setup window is Header/Footer. Remember, Headers appear at the top, and
Footers appear at the bottom of every page.
The Header and Footer drop down lists offer predefined options. The items listed pull the information
such as Author from the Document Properties. These can be modified through the File tab, Info,
Properties. Other information is derived from the Worksheet names, Workbook names, computer date
and time settings, and calculated page numbers. Remember you can set the beginning page number on
the Page tab.
Page 137 of 150
There are several Header and Footer options at the bottom of the window.
Different Odd and Even Pages – Custom Headers and Footers can be created for odd vs. even
pages.
Different First Page – Custom Headers and Footers can be created for the first page to be
different from the rest of the pages.
Scale with Document – Keeps the same scaling as the worksheet. If we set the scale on the Page
tab to print at 200%, this check box will ensure the header and footer also scale to 200%.
Align with Page Margins – Aligns the Header and Footer with the left and right margins
of the worksheet instead of the page itself.
Custom Header/Footer
Page 138 of 150
If you choose the options for Odd, Even and First Page, Excel will show new tabs here so you can
set each section to have its own custom header and footer.
Header/Footer Elements (Custom Toolbar)
There are three sections you can enter text. The Left Section aligns its contents on the left side of the
margin, the Center Section aligns its contents in the middle of the margin, and the Right Section aligns
its contents to the right side of the margin. If you would like the Header/ Footer to align with the page
instead of the margin,
1 2 3 4 5 6 7 8 9 10
turn off the Align with Page Margins option on the Page Setup.
1. Font – You can have several different fonts in the same section. You can set the format before or
after you type your text. If you format after, you will need to select the text before you go into the Font
window.
2. Page number – Inserts the text “&[Page]” which represents the current page number.
Page 139 of 150
3. Total number of pages – Inserts the text “&[Pages]” which represents the total number of printed
pages. If we wanted to see the phrase Page 1 of 6, we would need to type in the “Page” and ” of ” (don’t
forget the
spaces!) So our code would look like ‐ Page &[Page] of &[Pages] 4. Current date – Inserts the text “&[Date]”. This is the code to display the day the worksheet is
printed. This button is automatically updated. If you want it to display the day the worksheet was
created, you will need to type the actual date.
5. Current time – Inserts the text “&[Time]”. Like the Date button, this will automatically update each
time the file is printed.
6. File path – Inserts the text “&[Path]&[File]”. This is the code to show the full file path. Like the Date
and Time buttons, this will automatically update. If you move the file, or Save As into another location
or with another name, this option will automatically change.
7. Filename – Inserts the text “&[File]”. This is the code to display the current name of the file.
8. Worksheet name – Inserts the text “&[Tab]”. This is the code to display the printed worksheet name.
9. Insert Picture – Inserts a graphic in the header/footer. This is most often used for logos and
watermarks. The image will show in actual size behind the data. Very light images, such as a large
graphic of the word DRAFT or CONFIDENTIAL can be inserted into a header and will appear on every
page in the printout.
10. Format Picture – This button allows you to format the picture you have inserted. If there is no
picture, this button will be disabled (grayed out).
Page 140 of 150
The Page Layout View (found on the View tab), allows you to view and edit your Header/Footer and still
see the data in your worksheet. From this view you will see the edges of the page and the page numbers
in the status bar.
When you click in the Header/Footer area you will get a Header & Footer Tools – Design tab. From here
you will see many of the options we have available when setting up our Header/Footer in the Page Setup
window.
You may find it useful to create or edit your Header/Footer in this view, where you can see the changes
as you make them. To switch views or work within the sheet, double‐click back in the cells to leave the
Header/Footer design tab.
Sheet Tab
The final tab in the Page Setup window allows us to take things from the worksheet to add to our
printout.
Print area
This option allows you to choose the range of cells to
print. This option is not available if you enter Page
Setup from the print preview, because you cannot
“choose” cells.
The Print area can also be set from the Page Setup group of the Page Layout tab. Select the desired
range of cells and choose, Set Print Area.
Page 141 of 150
To set the print area from the Page Setup window, click inside Print area box and use the mouse to
point to the cells you want. You can move the dialog box by clicking and dragging the blue title bar.
Print Titles
This option allows you to set which Rows to repeat at the top of each page, and which
Columns to repeat on the left of each page. These do not have to be the first row or column;
any column can be chosen as your titles and will be repeated on each page of the printout. If
you click on this button in the Page Layout tab, it will open the in the Page Setup window to
the Sheet page.
As with the Print Area, to set the rows and columns to repeat, click inside the blank in the Page Setup
window and use your mouse to select the rows/columns you would like repeated on every page.
Gridlines
This option will print the gridlines around all the cells within the print area. The
alternative is to place “border” around your cells. Gridlines can be turned on and off
from the Page Layout tab, in the Sheet options section.
No Options Set Gridlines
Page 142 of 150
Black and white
This option will print your data in simple black and white. This does not include shades of gray. Excel
will remove all color formatting from the printout.
No Options Set Black and white
Draft quality
This option is the ideal quick printout. Depending on your printer it may reduce printing time. This
option will not print gridlines or graphics.
Row and column headings
This option will print out the row headings (the row numbers) and the column headings (the column
letters). Row and Column Headings can also be turned on and off from the Page Layout tab, in the
Sheet options section.
No Options Set Row and column headings
Comments
These can be inserted through the Review tab in the Comments section. By default, comments are not
printed, but here on the Sheet tab of the Page Setup window we have three options that will print the
comments: At end of sheet, As displayed on sheet, and the default ‐ (none).
Page 143 of 150
Cell errors as
This option allows you to choose how you want the errors to print. The default is to print the errors as
you see
them on the screen. But you can change this option to leave those cells blank or fill them all in
with N/A or dashes (‐‐‐‐).
Page Order
This option allows you to decide how multiple pages will print out. By default, Excel will print data
from the upper left corner straight down to the end, and then over to the next page full of data and
down. If you want to change the print order, such that Excel reads across and then down, change this
option.
Page Breaks
You can tell Excel where to start a new page by choosing Insert Page Break from the Page Setup section
of the Print Layout tab. The page break will be set above and to the left of the selected cell. In Normal
view the page break will appear slightly thicker gridline. In Page Break Preview the page break will
appear as a solid blue line.
Normal View Page Break Preview
Page 144 of 150
Soft Page Breaks
(natural breaks)
Hard Page Breaks
(inserted breaks)
You can remove page breaks that have been inserted. Put yourself in the first cell of that page (below and to
the right of the break) and choose Remove Page Break from the Page Setup section of the Print Layout tab.
The Reset All Page Breaks option will remove all of the inserted page breaks and return the worksheet to
the default soft page breaks (natural breaks).
Page Break Preview
The Page Break Preview can be accessed through the View tab, in the Workbook
Views group. As with the normal view, you cannot see the headers and footers.
The Page Break Preview shows the order of the pages and allows you to manually move the page breaks.
The dashed lines are soft (natural) page breaks. As you change the margins, scale, and orientation of the
page, these page lines will automatically update. In this view only, you can drag a page break (a blue line)
to another location. When you change where a page break is located, it changes for the entire worksheet.
For the whole row and the whole column.
LEARNING OBJECTIVES S
Printing sounds like a simple concept, until that Excel data set just WON’T fit on that printed page! In
this workshop we will work with the print preview; set paper size, page orientation, margins, headers
and footers; learn how to print titles, gridlines, and column headings; work with the views Page Layout
and Page Break Preview; change the document scaling to force the printout into a certain number of
Page 145 of 150
pages; and use print areas and page breaks to adjust what goes on each page. This basic
workshop assumes some experience with Microsoft Excel.
How to use VLOOKUP in Excel 2016
VLOOKUP is a powerful function in Excel. In this tutorial, I’m going to show you how to use VLOOKUP in
Excel 2016.
Specifically, I’m going to review an example with the steps needed to apply a VLOOKUP. But before we
begin, let’s first review the elements of the VLOOKUP function.
Elements of the VLOOKUP in Excel 2016
The VLOOKUP function in Excel has the following structure:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s now explain each element within this function:
1. lookup_value is the value that is present in your first table, which you wish to find in your
second table
2. table_array is the range of cells for your second table
3. col_index_num is the column position in your second table. For example, the value of ‘2’
represents the second column in your table
4. [range_lookup] can assume either a ‘TRUE’ value for an approximate match, or a ‘FALSE’ value
for an exact match
In the next section, we’ll review an example to demonstrate how to use VLOOKUP in Excel 2016.
Example of using the VLOOKUP function
To start, let’s review an example, where you have two tables in Excel:
 The first table contains Client Data with 3 columns: Client First Name, Client Last Name and
Country Code. Let’s call this table the ‘Client’ table
 The second table contains Country Data with 2 columns: Country Code and Country Name.
Let’s call this table the ‘Country’ tables
For each client name, under the first Client table, the goal is to display the Country Name (to be
taken from the second Country table). You can then use the VLOOKUP function to automate this
task.
Here is the Client data that can be copied into Excel (under the range of cells A1 to C26). You’ll
need to rename the first sheet to Client Data:
Page 146 of 150
Client First-Name Client Last-Name Country Code
Elden Stanhope 107
Emile Nation 103
Velda Labriola 105
Vanesa Rakowski 109
Sharron Gorby 120
Cecilia Swearingen 101
Doretha Saffell 106
Mariah Heilman 103
Rana Bigby 107
In Vallecillo 116
Librada Taylor 119
Elane Mayes 103
Alix Haskett 109
Anastacia Goldblatt 115
Melania Whitlock 108
Dario P arker 111
Rochel Witherspoon 106
Lucrecia Tarrance 114
Jermaine Lamprecht 102
Regine Bascom 109
Bryanna Stansberry 107
Naomi Arai 114
Darin Blumstein 108
Greg Ricketts 102
Wilburn Guse 118
This is how the client data would look like in Excel:
Page 147 of 150
Next, copy the Country data into a second sheet under the range of cells A1 to B21. You’ll need to
rename the second sheet to Country Data:
Country Code Country Name
101 Australia
102 Austria
103 Belgium
104 Belize
105 Canada
106 China
107 Finland
108 France
109 Germany
110 Greece
111 Italy
112 Japan
113 Mexico
114 Morocco
115 Nigeria
116 Peru
117 Russia
118 Sweden
119 United Kingdom
120 Uruguay
And this is how the country data would look like in Excel:
Notice that both of the tables have the column Country Code, which contains common values under
both tables. We will use the values under the Country Code column to connect between the two
tables.
Page 148 of 150
Once you connect the two tables via the Country Code, you’ll be able to achieve the goal of displaying
the Country Name for each client under the Client table.
Steps to apply the VLOOKUP in Excel 2016
Let’s now look at the steps to apply the VLOOKUP function:
(1) First, go to the ‘Client Data’ sheet
(2) Next, double-click on cell D2
(3) Then, type/copy:
=VLOOKUP(C2,’Country Data’!A:B,2,TRUE)
 C2 is your lookup_value. It represents the value that is present in your first Client table, which
you wish to find in your second Country table
 ‘Country Data’!A:B is your table_array. It represents the range of cells for your second
Country table, where you want to find your lookup_value
 The value 2 is your col_index_num. It reflects the column position in your Country table. Here,
‘2’ means the second column under your Country table. The values under this second column
(i.e., the Country Name column) will then be displayed when you apply the VLOOKUP
 TRUE is your [range_lookup] which reflects an approximate match
This is how your VLOOKUP function would look like in Excel:
Page 149 of 150
(4) Press Enter, and you’ll now see the result of your VLOOKUP function in cell D2. In our case, the
country name associated with the country code of 107 is “Finland”:
(5) Finally, drag-down the VLOOKUP function from cell D2 to cell D26, so that you can display the
Country Name for each client under your first Client table:
Page 150 of 150