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 35 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 36 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 Excels
 Times in Excels
 Useful Date/Time Shortcuts
 Custom Formatting
 Simple Date/Time Math
 Date Worksheet Functions
 Time Functions
 Dated if 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 37 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 38 of 150
 Date Month Year
d 3 m 2
dd 03 mm 02 YY 04
ddd Tue mmm Feb
dddd Tuesday mmmm February YYYY 2004

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.

Times for 1:02:05

Shortcut Result Note

Ctrl-; Current Date Control Semicolon
Ctrl-; Current Time Control Colon

Appropriate designator at the end. Access will accept any of the following: AM/PM; am/pm; A/P; a/p;
AMPM.

Page 39 of 150
 Times for 17:02:05

Hours 24 hour clock 12 hour clock

H 17 h:m 17:2 h:m 5:2 PM
am/pm
hh 17 h:mm 17:02 h:mm 5:02 PM
am/pm
h AM/PM 5 PM hh:mm 17:02 hh:mm 05:02 PM
am/pm

If you need to go smaller, adding .00 after the second format (ss.00) will give the fraction of a second.

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

36 Hours 75 Minutes 75 Seconds
h 12 m (assumes s 15
month)
hh 12 mm (assumes ss 15
month)
[h] 36 [m] 75 [s] 75

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

Page 40 of 150
Why doesn't it look right? You have to format the result as a true number field.
Time * 24 (formatted as
a
Time
number)
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: DATE VALUE (date_text)

Date text represents a date between 01/01/1900 and 12/31/9999 saved as text

Page 41 of 150
 Equation Result Notes
=DATEVALUE(3/15/2009) #VALUE! Date not in Text format

=DATEVALUE("10/15/1905") 2115

=DATEVALUE("3/20/2011") 40622

A
=DATEVALUE(A1) #VALUE! 1 = 12/5/1976

A
=DATEVALUE(A1) 28099 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

Serial Number not a Date (Excel
=DAY(3/15/2009) 0 sees 3÷15÷2009)

=DAY("10/15/1905") 15

=DAY(40622) 20 Equivalent to 3/20/2011

=DAY(A1) 5 A1 = 12/5/1976

Page 42 of 150
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: DAYS 360 (start date, end date, [method])

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

Page 43 of 150
 EDATE ‐ Returns serial number of the date that is a number of months away from a 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 44 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: NETWORK DAYS (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 Res Notes

ult

=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 C = 07/01/2009, C2 = 07/15/2009

1

Page 45 of 150
 C = 07/01/2009, C2 = 07/15/2009,
1
=NETWORKDAYS(C1, C2, C3) 11
C = 07/04/1999
3

D1 = 11/15/2009, D2 = 11/15/2010,
D3
=
=NETWORKDAYS(D1, D2, D3:D4) 259
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 46 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 51 Wednesday, December 15,

("12/15/1976") 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 47 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

Equivalent to 3/20/2011
=YEAR(40622) 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 48 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

Page 49 of 150
MINUTE‐ Returns the hour of a time value
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 50 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.

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

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

Page 51 of 150
Datedif Function:

The DATEDIF function in Excel is a versatile and powerful tool for calculating the difference between two
dates in various units such as days, months, or years. This function, though not listed in Excel's function
library, offers a straightforward way to measure time spans, making it invaluable for tasks such as
determining age, tenure, or the duration between events.
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

Number of days, not counting years and months

MD

Learning Objectives

 Fill Handle
 Mathematical Operations
 Building an Equation
 Order of Operations
 Formatting Dates
 Formula View
 Absolute/Relative
 Functions.
 Naming Cells

Page 52 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 53 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 54 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 55 of 150
 Parentheses Exponents Multiply Divide Add Subtract

Please Excuse My Dear Aun Sally

t

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.

Page 56 of 150
 1993 3.00
/1995 6.00
002 9.00
2008 3.00
/2010 7.00
013 3.00
/2018 4.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 57 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

Page 58 of 150
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 59 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:

1 5 6 =A1+B1
2 12 4 =A2+B2

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

Page 60 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+B SubTot 123 456 789
1 als:
2 12 4 =$A$1+B TaxRat 6.5%
2 e:
3 Taxes: =F1*$F =G1*$F =H1*$
$2 $2 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 61 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 62 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 63 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 64 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 65 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 66 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 67 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 68 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

Page 69 of 150
Calculate SubTotals

 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

Page 70 of 150
 - In Cell D2 type: Tax Rate

- In Cell E2 type: 10%

- In Cell E4 type: Taxes

- 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 71 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"

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.

Page 72 of 150
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 73 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

O This will open the Function Arguments window

 Clear the contents in the Number1 box

Page 74 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 75 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 76 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

Page 77 of 150
Final Result

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 78 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 79 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

Page 80 of 150
Typing in Functions
Clear cell D10

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 81 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 82 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 Sub Total 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 83 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 84 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

Charts

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 85 of 150
Value Axis

Chart Title

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 86 of 150
0
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 87 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.

Page 88 of 150
Chart Tools

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 89 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 90 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.

2ND 3RD 4TH

ITE 1ST QTR QTR QTR
M QTR

PA 456 489 423 468

NTS

SH 498 435 472 436

OES

SOC 128 168 157 138

KS

BL
OU 579 498 531 589
S
ES
HA 126 129 123 119
TS

Page 91 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.

Page 92 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 93 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 94 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 & T
Outline e
x
t
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 95 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 96 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 97 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 98 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 99 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 100 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 101 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 102 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 103 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 104 of 150