MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2016

MS EXCEL 2016
BASIC MANUAL

INTRODUCTION TO MS EXCEL 2016

Lead-Leap Consulting Limited
1, Adegbeyemi Street, Off Allen Avenue, Ikeja, Lagos
Email: leadleapl@gmail.com

LEAD-LEAP CONSULTING LIMITED

08062468296
1
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

TABLE OF CONTENTS
Module One: Getting Started .............................................................................................................5
Opening Excel ........................................................................................................................................... 6

Understanding the Interface..................................................................................................................... 7

Using Backstage View............................................................................................................................. 10

Understanding Worksheets .................................................................................................................... 11

Understanding Workbooks ..................................................................................................................... 11

Module Two: Entering and Editing Data ............................................................................................ 14

Entering Data .......................................................................................................................................... 14

Editing Data ............................................................................................................................................ 16

Using the Wrap Command ..................................................................................................................... 16

Adding Rows and Columns ..................................................................................................................... 17

Checking Your Spelling ............................................................................................................................ 19

Module Three: Building Formulas ..................................................................................................... 21

The Math Basics of Excel ........................................................................................................................ 21

Building a Formula .................................................................................................................................. 23

Editing a Formula.................................................................................................................................... 24

Copying a Formula .................................................................................................................................. 25

Relative vs. Absolute References ............................................................................................................ 27

Module Four: Using Excel Functions ................................................................................................. 28

Formulas vs. Functions............................................................................................................................ 28

Using the SUM Function ......................................................................................................................... 29

Using Other Basic Excel Functions .......................................................................................................... 31

Understanding the Formulas Tab ........................................................................................................... 32

LEAD-LEAP CONSULTING LIMITED

08062468296
2
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Using the Status Bar to Perform Calculations ........................................................................................ 32

Understanding the New Function Names ............................................................................................... 34

Module Five: Formatting Your Data .................................................................................................. 35

Changing the Appearance of Text .......................................................................................................... 35

Changing the Appearance of Numbers................................................................................................... 36

Setting Alignment Options...................................................................................................................... 38

Using Merge ........................................................................................................................................... 38

Removing Formatting ............................................................................................................................. 39

Adding Borders ....................................................................................................................................... 40

Adding Fill Color ...................................................................................................................................... 42

Using Cell Styles ...................................................................................................................................... 43

Changing the Theme ............................................................................................................................... 46

Module Six: Using Time Saving Tools ................................................................................................ 47

Using AutoFill .......................................................................................................................................... 47

Using AutoComplete ............................................................................................................................... 49

Adding Sparklines ................................................................................................................................... 51

Module Seven: Working with Excel Files ........................................................................................... 53

Saving Files ............................................................................................................................................. 53

Publishing Files to PDF ............................................................................................................................ 54

Sending Files via E-Mail .......................................................................................................................... 56

Closing Files ............................................................................................................................................ 57

Opening Files .......................................................................................................................................... 58

Module Eight: Printing Excel Data ..................................................................................................... 60

An Overview of the Page Layout Tab...................................................................................................... 60

LEAD-LEAP CONSULTING LIMITED

08062468296
3
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Setting Up Your Page .............................................................................................................................. 60

Using Print Preview................................................................................................................................. 62

Printing Data........................................................................................................................................... 62

Module Nine: Creating Charts........................................................................................................... 63

Inserting a Chart ..................................................................................................................................... 63

Overview of the Chart Tools Tab............................................................................................................. 65

Understanding the Parts of a Chart ........................................................................................................ 65

Changing the Chart Style ........................................................................................................................ 66

Resizing and Moving the Chart ............................................................................................................... 67

Wrapping Up ................................................................................................................................... 70
Words from the Wise .............................................................................................................................. 70

LEAD-LEAP CONSULTING LIMITED

08062468296
4
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Learning is the beginning of wealth.

Jim Rohn

Module One: Getting Started

In this module, we will discuss how to open and close Excel. We will take a look at the interface and the
new Backstage View. We will also explore the difference between worksheets and workbooks.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Opening Excel
Use the following procedure to start Excel.

1. Select START (or press the Windows key on the keyboard) to open the Start menu.

2. Select ALL PROGRAMS.

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Next, highlight the Microsoft Office program group. Select MICROSOFT OFFICE EXCEL2010.

Understanding the Interface

Excel 2010 has a new interface that builds on interface from the previous version of Excel. Excel 2010
uses the RIBBON interface that was introduced in Microsoft Office 2007 applications. Each TAB in the
RIBBON contains many tools for working with your document. To display a different set of commands,
click the tab name. BUTTONS are organized into groups according to their function.

In addition to the TABS, Excel 2010 also makes use of the QUICK ACCESS TOOLBAR from the MS Office 2007
applications.

The Formula bar is where you can edit contents of a cell. The Status Bar includes information about your
worksheet when you highlight different cells.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

View the Excel interface, including the Ribbon, worksheet area, the Formula Bar, the Quick Access
toolbar, and the Status Bar.

Quick Access
Toolbar
Ribbon

Formula Bar
Worksheet
area

Multiple
Status Bar
Worksheet Tabs
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Use the following procedure to customize the Quick Access Toolbar.

1. Select the arrow next to the Quick Access Toolbar.

Add an item to the Quick Access Toolbar by selecting it from the list. You can remove an item by
reopening the list and selecting the item again.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

If you select More Commands, Excel opens the Customize window.

To add a command, select the item from the list on the left and select ADD. Select OK when you have
finished.

Using Backstage View

Select the FILE tab in the Ribbon to open the Backstage view. The Backstage view is where you will find
the commands for creating, saving, opening, and closing Excel files, as well as information about the
current workbook. The Backstage view includes new interfaces for printing and sharing your workbooks.
The Options command is also available to open a new screen for setting your Excel Options.

Use the following procedure to view the Backstage View.

1. Select the File tab on the Ribbon.

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Excel displays the Backstage View, open to the Info tab by default. A sample is illustrated below.

Understanding Worksheets
WORKSHEETS are simply collections of CELLS. A cell is the intersection of a ROW and a COLUMN. Columns
labels are letters and row labels are numbers. Only one cell can be active at a time, and that cell is called
the ACTIVE CELL. You can move around a worksheet using your mouse or your keyboard.

Understanding Workbooks
A WORKBOOK is a collection of worksheets. When you save an Excel file, you are actually saving a
workbook. A workbook includes 3 worksheets by default when you open a new file. You can change the
names of these worksheets and add more worksheets to the workbook. You can also change the order
of the worksheets.

Use the following procedure to switch to a different worksheet.

1. At the bottom of the worksheet window, there are tabs for each worksheet in the workbook.
Click on the sheet you want to view.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Use the following procedure to rename a worksheet.

1. Right click on the sheet tab and select Rename from the context menu.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. Enter the new name over the highlighted text.

Use the following procedure to insert a new worksheet.

1. Click the New Worksheet tab.

Excel opens the new worksheet to the first cell, so that you can begin entering data right away. You can
rename the worksheet if needed.

Use the following procedure to move a worksheet.

1. Click on the worksheet tab that you want to move and drag it to the new location in the
workbook.
 Being busy does not always
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL mean real
MANUALLEVEL
work. The object of all work is production
or accomplishment and to either of these
ends there must be forethought, system,
planning, intelligence, and honest purpose,
as well as perspiration. Seeming to do is
not doing.

Thomas Alva Edison

Module Two: Entering and Editing Data

This module introduces you to your first worksheet, where you can enter or edit data. Your worksheets
will have a purpose in mind, whether it is to add a series of numbers, like sales figures or expenses, or to
track things like inventory or class registrations. Time spent thinking about the structure of your
worksheet will pay off later, but you can also add rows and columns at any time to rearrange your data.
This module will also introduce the ability to wrap cell contents and check your spelling.

Entering Data
Data is entered into cells. Click the cell you want, and type the desired entry. You can enter either
numbers (VALUES) or text (LABELS) in Excel.

Once you have finished typing, you can press Tab or Enter to complete the entry.

When you type data into a cell, all of it is contained in that cell, even if it is not all visible at once. If you
cannot see all of the data in your cell, you can widen the column.

In the following example, “Household Budget” and “Car Payment” are both too long for the column
width.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Use the following procedure to widen a column.

1. Click on the column you want to widen. Notice the cursor changes to a cross with double
arrows. The screen tips indicate how wide in pixels the column currently is.

2. Drag the border to the new width

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Editing Data
Shown here is the relationship between the active cell and the Formula Bar.

Using the Wrap Command

Use the following procedure to wrap the contents of a cell that contains a label.

1. Click the cell that you want to wrap.

2. Click the Wrap Text tool on the Ribbon.

Use the following procedure to force a line break in a cell with wrapped contents.

1. Click on the cell with wrapped contents. The contents appear in the Formula Bar.
2. Place your cursor on the Formula Bar where you would like to force a line break. Press Alt+
ENTER.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Adding Rows and Columns

New columns are inserted to the left of your currently selected column, and new rows are inserted
above your currently selected row.

Use the following procedure to add a new row.

1. Highlight the row below where you want to insert a row. Click to the left of the row number to
highlight the whole row.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. Select Insert Sheet Rows from the Ribbon.

The new row is highlighted.

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Checking Your Spelling

To check your spelling, display the Review tab, and click the Spelling button (in the Proofing group).

Use the following procedure to check spelling.

1. Select the Spelling tool on the Review tab of the Ribbon.

Excel opens the Spelling dialog box and begins indicating any spelling errors.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. Highlight the correct spelling and select Change. If the correct spelling is not listed, you can
correct the spelling by editing the text in the Not in Dictionary field.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

All which is beautiful and noble is the

result of reason and calculation.

Charles Baudelaire

Module Three: Building Formulas

The backbone of Excel is its ability to perform calculations. There are two ways to set up calculations in
Excel: using formulas or using functions. Formulas are mathematical expressions that you build yourself.
You need to follow proper math principles in order to obtain the expected answer. Building the formula
is simply a matter of combining the proper cell addresses with the correct operators in the right order.
This module will explore how to build, edit, and copy formulas. This module will also explain the
difference between relative and absolute references. We will explore functions in the next module.

The Math Basics of Excel

Excel performs calculations by combining the contents of cells using operators. There are several
different types of operators to perform arithmetic, comparisons, and text concatenation operations, as
well as to provide references to use in those calculations.

Review the different types of operators.

The Arithmetic operators are:

• + Plus Sign – Adds values

• – Minus Sign – Subtracts values
• * Asterisk – Multiplies values
• / Forward slash – Divides values
• % Percent sign – Finds the percentage of a value
• ^ Caret – Exponentiation – Finds the exponential value

The Comparison operators are:

• = Equals sign – Equates values

• > Greater than sign – Indicates that one value is greater than the other
• < Less than sign – Indicates that one value is less than the other
• >= Greater than or equal to – Indicates that one value is greater than or equal to the other
• <= Less than or equal to – Indicates that one value is less than or equal to the other
• <> Not Equal – Indicates that values are not equal
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Text concatenation allows you to combine text from different cells into a single piece of text. The
operator is the & sign.

The reference operators combine a range of cells to use together in an operation. The reference
operators are:

• : Colon – A Range operator that produces a reference to all of the cells between the
references on either side of the colon
• , Comma – A Union operator that combines multiple range references
• Space – An intersection operator that returns a reference to the cells common to the
ranges in the formula
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Building a Formula
All formulas begin with the = symbol. To enter a formula, click the cell where you want the formula and
begin typing. When you click on another cell, the contents of that cell will be included in the formula.
Press Enter when you have finished entering the formula.

Use the following procedure to enter a formula to calculate the Total Value in the sample worksheet

1. Click on the Total Value column for the first product (cell D4).
2. Enter the = sign to begin the formula.
3. Click on cell B4 to use it as the first value in the formula. Excel enters the reference as part of the
formula.

4. Enter the * sign.

5. Click on cell C4 to use it as the second value in the formula. Excel enters the references as part
of the formula.

6. Press ENTER to complete the formula. Excel moves to the next row and performs the
calculations in the formula.

The following illustration shows the answer to the calculation in the cell, and since the cell is active, you
can see the formula in the Formula bar.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Editing a Formula
If you have made an error in a formula, you can easily correct it. Both the operators and the cell
references can be edited.

Use the following procedure to edit a formula. The following example uses an incorrect cell reference in
a formula.

1. Click on the cell with the formula you want to correct to make it active.
2. Click on the Formula Bar. Excel highlights the cell references in the current formula.

3. Highlight the operator or cell references and either type over with the correct reference or
operator, or click the correct cell to replace a cell reference.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

4. Press ENTER to complete the formula. Excel calculates the formula and moves to the next row.

Copying a Formula
In Excel, you often repeat the same formula, changing only the cells you are referencing. You can easily
copy the formula instead of having to re-enter it multiple times. If you are familiar with copy and paste
from other applications, this is an easy way to copy a formula. You can also paste in multiple cells at
once by highlighting all of them before pasting.

Use the following procedure to copy and paste a formula.

1. Right click on the cell with the formula you want to copy. This displays the context menu. Select
Copy. You can also click on the cell and use the keyboard shortcut: CTRL + C.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Excel highlights the cell whose contents you are copying. This will remain highlighted until you finish
pasting, in case you want to paste the cell contents more than once.

2. Right click on the cell where you want to copy the formula. Excel displays a number of paste
options. To paste a formula, select Paste or Paste formula. You can also click on the cell and use
the keyboard shortcut: CTRL + V.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. You can repeat the paste as many times as desired. Or you can highlight multiple cells at once
before pasting to repeat the paste for all highlighted cells.
4. Press ENTER to stop pasting.

Relative vs. Absolute References

Copying formulas works because of RELATIVE REFERENCING. Formula references actually reference the cell
relative to the formula location. This means that when you copy the formula, the new formulas
reference cells in the same way as the original formula, but relative to the new cell location. However,
ABSOLUTE REFERENCING references the exact same cell address no matter where the formula is copied. To
create an absolute reference in Excel, put dollar signs before the column address and before the row
address. For example, $A$2.

Use the following procedure to copy a formula with an absolute reference.

1. Create a new column labeled Taxes.

 I can give you a six-word
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL formula for
MANUALLEVEL
success: “Think things through - then
follow through.”

Sir Walter Scott

2. Click on the Taxes column for the first product (cell E4).
3. Enter the = sign to begin the formula.
4. Click on cell B16 to use it as the first value in the formula. Excel enters the reference as part of
the formula. Use the Formula Bar to enter dollar signs before the column and the row (i.e.,
$B$16).
5. Enter * and the relative reference in the Total Value column.

6. Press ENTER to complete the formula. Excel moves to the next row and performs the
calculations in the formula.

Copy the formula for the other products and click on some of them to see the results.

Module Four: Using Excel Functions

This module introduces Excel functions, which are a little like templates for common formulas. There are
many different types of functions. This module will introduce the new function names introduced in
Excel 2010. We’ll take a look at the Formulas tab introduced in the Ribbon for Excel 2007. We’ll look at
the SUM function and other basic common functions. Finally, this module will explain how to use the
Status Bar to perform simple calculations.

Formulas vs. Functions

Formulas combine cell references and operators to perform calculations. Functions use function names
and arguments. The syntax for a function is always the same: =NAME(Arguments).

Use the following procedure to open the Insert Function dialog box.

1. Click the Insert Function tool right next to the Formula Bar.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Investigate the different categories and functions in the Insert Function dialog box. View the bottom
part of the screen where the syntax and description of the function appear.

Using the SUM Function

The SUM function adds up a series of numbers. Instead of having to repeatedly type + and click cell
references, you can use the SUM function.

Use the following procedure to use a SUM function to add the total for each category in the sample file

1. Click on the Total –First Six Months column for the first category (cell H5).
2. Click on the SUM tool in the Editing Group on the Home tab of the Ribbon.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Excel enters the function with a default selection of the cell references you want to use in the
function highlighted.

4. If the cell references are not accurate, you can drag the highlighted area to include additional
cells or remove cells you do not want used in the function.
5. Press ENTER to complete the function.

Excel performs the calculation and moves to the next row. In the following illustration, the cell with the
function is active, so that you can see the function syntax in the Formula Bar and the result in the cell.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Using Other Basic Excel Functions

In addition to the SUM function, there are several other functions that are commonly used. You can
access these by clicking the arrow next to the SUM tool on the Ribbon.

Use the following procedure to use the AVERAGE function as an example of another function.

1. Add a new label in column I: Average.

2. Click on the cell in the Average column for the first category.
3. Click on the arrow next to the SUM function to see the list of other common functions.

4. Select Average.

Excel enters the function with the most likely cell references.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

5. Replace the cell references so that cell H5 is not included in the average.

6. Press ENTER to complete the function.

Understanding the Formulas Tab

The Formulas tab on the Ribbon is used to help create functions. You can access Excel’s functions in the
Function Library group. This saves you the trouble of typing the function name, and provides you with
some help entering the arguments.

Shown here is the Formulas tab on the Ribbon.

Using the Status Bar to Perform Calculations

To learn how to use the Status Bar to perform basic functions and to learn how to customize the Status
Bar.

Use the following procedure to customize the Status Bar.

1. Right click on the Status Bar to see a list of Functions that can be displayed. For this example,
select MIN and MAX.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. Press ESC or click elsewhere in the worksheet to close the Customize Status Bar list.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

View the calculations performed when you highlight a group of cells.

Understanding the New Function Names

Some of the functions in previous versions of Excel have been renamed. The new functions have
improved performance.

The old functions are available if you need backward compatibility. If you do not need compatibility with
previous versions of Excel, always use the new function names for best performance.

Shown here are the icons that appear when entering a function name. The old names have a yellow
warning triangle next to them.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL
Quick Tip: Use the Undo arrow on the
Quick Access toolbar to reverse unwanted
changes.

Module Five: Formatting Your Data

In this module, we will look at how to make your worksheet more appealing by changing the font type
and size, alignment, formatting numbers, and by adding color and borders. This module also explains
how to use the merge feature and how to remove formatting.

Changing the Appearance of Text

There are a number of things you can do to enhance the appearance of your text. You can change the
font, the size, the color, and you can add bold, italic, or underline effects. The Home tab includes many
formatting options. There is also a context menu to easily apply formatting anytime.

Use the following procedure to apply formatting to text.

1. Click on the cell or highlight a number of cells that you want to format.
2. Right-click to display the context menu, or use the formatting tools on the Home tab.
a. Use the Font drop down list to select a new font for the text.
b. Use the Font Size drop down list to select a new font size for the text. Alternatively, you
can use the Increase Font Size or Decrease Font Size tools to adjust the font size2 point
sizes at a time.
c. Select Bold, Italics, or Underline to add these features to your text.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Changing the Appearance of Numbers

You can format numbers in a variety of ways, by including symbols and/or decimal places, by formatting
the numbers as a long or short date, by adding percentage signs, and other number formatting options.

Use the following procedure to format a number as currency without decimals.

1. Click on the cell or highlight the cell range that you want to format.
2. Select the type of number formatting you want to use from the Number group drop down list in
the Home tab of the Ribbon.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Select the Decrease Decimal tool (2 times) to remove the decimal places.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Setting Alignment Options

Alignment refers to the position of the contents within the cell. You can align the contents horizontally
and vertically. You can also change the orientation of the cell contents.

Use the following procedure to align cell contents.

1. Click on the cell or highlight the cell range that you want to align.
2. Select the type of alignment you want to use from the Alignment group tools in the Home tab of
the Ribbon.

Using Merge
Merge allows you to combine two or more cells into a single cell. This is useful for centering text across
several columns.

Use the following procedure to merge cells.

1. Highlight the cell range that you want to merge.

2. Select the Merge tool from the Home tab of the Ribbon. Select Merge & Center.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Removing Formatting
Formatting is not removed when you delete the contents of a cell. To see this, select a cell with
formatting, and press Delete on your keyboard. Re-type the contents, and you’ll see that the new
contents are formatted. You must clear the formatting if you no longer need it.

Use the following procedure to clear formatting.

1. Click on the cell or highlight the cell range that you want to clear.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. Select the Clear tool from the Home tab on the Ribbon.

3. Select Clear Formats.

Adding Borders
By default, there are no borders around any of the cells. However, you can add a variety of different
borders using the Border button.

Use the following procedure to add borders.

1. Highlight the cell or cell range where you want to apply your border.
2. Select the type of border you want to apply from the Borders tool on the Home tab of the
Ribbon.
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Adding Fill Color

Fill color is how you add shading to the cells of your workbook. You can choose from standard colors,
theme colors, or you can create a customized color.

Use the following procedure to apply fill colors.

1. Highlight the cell or cell range where you want to apply your fill color.
2. Select the color you want to apply from the Fill Color tool on the Home tab of the Ribbon.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Using Cell Styles

Styles are a group of formatting features that you can apply all at once. Styles provide more consistency
to your workbooks and may be easier to apply to individual formatting features that you are going to
use repeatedly.

Use the following procedure to apply a cell style.

1. Highlight the cell or cell range where you want to apply your style.
2. Select the Cell Styles tool from the Home tab of the Ribbon to see the style gallery.
3. Select the style that you want to apply. You can see a preview before you select a style.

Use the following procedure to create a new cell style.

1. Highlight the cell or cell range where you want to apply your style.
2. Select the Cell Styles tool from the Home tab of the Ribbon to see the style gallery.
3. Select New Cell Style to open the Style dialog box.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

4. Enter a name for the style in the Style Name field.

5. Check the Style Includes boxes to indicate what formatting features the style should include.
The options vary based on the active cell.
6. Select Format to open the Format Cells dialog box.
7. Use the Format Cells dialog box to indicate each formatting feature for the style. Select OK
when you have finished indicating all of the formatting features for the style.
a. The Number tab allows you to set number formatting for cells that contain values.
b. The Alignment tab allows you to set text alignment for cells that contain text.
c. The Font tab allows you to set the font for the style.
d. The Border tab allows you to set customized borders for the style.
e. The Fill tab allows you to set customized fill color for the style.
f. The Protection tab allows you to protect the cells from changes if you use the Protection
feature.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

8. Select OK to save your style and close the Style dialog box.

The new style appears at the top of the Cell Style gallery.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Changing the Theme

A theme is a collection of formats that can be applied to a worksheet. It includes settings for colors,
fonts, and effects (such as shadows and colors for graphics). If you change the theme for a worksheet
after you have formatted cells, the theme may not override all of your customizations.

Use the following procedure to apply a theme.

1. Select the Page Layout tab.

2. Select the Themes tool to see the Theme gallery.
3. Select a theme from the Theme gallery.
 If you want work wellMANUALLEVEL
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL done, select a busy
man - the other kind has no time.

Elbert Hubbard

Module Six: Using Time Saving Tools

This module introduces some time saving tools to make basic worksheet creation even easier. The
AutoFill feature can help you quickly repeat formulas or do things like fill in the days of the week or
months of the year. The AutoComplete feature can help you to quickly write functions. This module will
also look at sorting and filtering your data to efficiently find the data you need. Finally, this module will
introduce Sparklines – a new feature in Excel 2010 that allows you to create cell-sized graphs that can be
helpful in showing trends.

Using AutoFill
AutoFill is a feature that quickly creates copies of a cell based on that cell’s contents. If the cell contains
a formula, the formula is repeated with relative references. If the cell contains a date, AutoFill creates a
list with that date as the starting point.

To use AutoFill, simply select the AutoFill handle and drag to fill the contents of consecutive rows or
columns. The AutoFill handle is present for the active cell. It is a small square at the bottom right corner
of the cell.

You can only fill in one direction at a time.

Use the following procedure to copy a formula using the AutoFill handle. This example uses the formula
in the “# Left to reorder” column in the Inventory sample worksheet.

1. Click on the cell with the formula you want to copy. Excel displays a handle around the cell.

2. Drag the handle at the bottom right corner of the cell to the end of the range of cells where you
want to copy the formula.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Release the mouse button at the end of the range. Excel displays a menu to help determine you
AutoFill options. For this example, we want to Copy Cells.

Use the following procedure to create a list using AutoFill. This example creates new columns in the
Budget worksheet to cover the second six months.

1. Create a new column heading with the text “July” in cell J4.
2. Click on that cell to make it active. Excel displays a handle around the cell.
3. Drag the handle across the columns. Excel displays a screen tip showing what AutoFill will place
in those cells.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

4. Release the mouse button at the end of the range. Excel displays a menu to help determine you
AutoFill options. For this example, you can choose either Fill Series or Fill Months.

Using AutoComplete
AutoComplete is an Excel feature that helps you save time. You can begin typing the name of a function
that you want to use in a cell. Excel displays a list of functions that start with those letters. Then you can
simply select the appropriate function from the drop-down list. This helps save time for the functions
you use frequently. It also helps ensure accuracy for your arguments in the function.

Use the following procedure to use the AutoComplete feature.

1. Begin typing the SUM function. As soon as you type the Equals sign and the letter S, Excel
displays a possible list of matching functions.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. To select the SUM Function from the list, double-click on the SUM function.
3. Excel enters the function, but you must still enter the arguments. You can simply click on
multiple cells, or click and drag to select a cell range. You can also type in the cell references.

4. Enter the final parenthesis mark to end the function.

5. Press ENTER to enter the function in the cell.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Adding Sparklines
Sparklines are tiny charts that are actually in the background of a cell. Sparklines can be used to visually
display trends that might be harder to spot just from the data alone. Sparklines provide valuable
information at just a glance.

Use the following procedure to create a sparkline. This example shows a sparkline for the “heat”
category on the budget.

1. Click on the cell where you want to create the sparkline.

2. Click the Insert tab.
3. Click the type of Sparkline you want to include from the Sparklines group. For this example, line
or bar chart work best.
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL
 Quick Tip: Once you have
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL saved your file,
MANUALLEVEL
you can quickly update it by pressing Ctrl +
S – making sure you’ll never lose your hard
work!

Module Seven: Working with Excel Files

This module working with Excel files, including how to open files and how to save them. It is important
to save files early and often to protect your work. The file format introduced in Excel 2007 provides new
options. In this module, we’ll cover how to publish your worksheets as PDF files and how to send a
workbook via E-mail. This module also discusses how to close Excel files.

Saving Files
The new Backstage View provides options for saving your files. You can also save a previously saved file
using the keyboard shortcut or the Quick Access Toolbar.

Use the following procedure to save a new file.

1. Select the File tab to open the Backstage view.

2. Select Save or Save As. (For unsaved data, either works the same.)
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Navigate to the location where you want to store the file.

4. Enter the File Name.
5. Select Save.

Publishing Files to PDF

PDF stands for Portable Document Format. Almost all computers have software to read a PDF file,
making it an extremely portable file format. After loading the PDF add-in, it’s just as easy to save a PDF
as a regular workbook file.

Use the following procedure to publish a workbook to a PDF file.

1. Select the File tab to open the Backstage view. Select the Share tab.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

2. Select Create PDF/XPS Document under File Types. Select Create PDF/XPS Document.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Navigate to the location where you want to store the file.

4. Enter the File Name.
5. The default type is PDF. The Open file after publishing and Standard optimization are selected
by default. These can be changed, if necessary.
6. Select Publish.

Sending Files via E-Mail

Use the following procedure to attach their files to an E-mail.

1. Select the File tab to open the Backstage view. Select the Share tab.

2. Select Send as Attachment.

Excel opens an E-mail message with the file attached. You can enter the addressee and message
contents as needed.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Closing Files
If you have finished working on a workbook, but are continuing to work in Excel, you should close the
file.

Use the following procedure to close a file.

1. Select the File tab to open the Backstage view.

2. Select Close.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Opening Files
The Backstage View includes the Open command, as well as a list of recently used files. You can use
either method, or the Ctrl + O keyboard shortcut to open a file.

Shown here is the Recent list in the Backstage View.

Use the following procedure to open a file.

1. Select Open from the Backstage View.

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Quick Tip: You can change the view and

zoom control on the Status Bar at the
bottom of the Excel window.

2. Navigate to the location of the file you want to open. Highlight one or more files to open them.
3. Select Open.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Quick Tip: The new Backstage View

includes many options for controlling how
your Excel data prints.

Module Eight: Printing Excel Data

This module discusses printing your worksheets. First, the module covers the Page Layout tab for setting
up the worksheet page. Next, the module goes into more detail on setting up your pages. The module
discusses how to use Print Preview, Finally, the module explains how to print your worksheets.

An Overview of the Page Layout Tab

The Page Layout tab is used to set up your page. There following groups of buttons are on this ribbon:

• Themes
• Page Setup
• Scale to Fit
• Sheet Options
• Arrange

Shown here is the Page Layout tab on the Ribbon.

Setting Up Your Page

The Page Setup group on the Page Layout tab of the Ribbon includes several tools to help you set up
your page. You can control the margins, orientation, and size of your page. You can also control the print
area, if you don’t want to print all of the contents of your worksheet. Finally, there is another
opportunity to control page breaks. You can also include a background and printing titles.

Use the following procedure to use custom margins.

1. Select Custom Margins from the Margins tool on the Page Layout tab of the Ribbon.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Excel displays the Page Setup dialog box.

2. Use the up and/or down arrows to control each of the margins (in inches). When have finished,
select OK.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Using Print Preview

Shown here is the Print tab in the Backstage View.

Printing Data
You can print a worksheet, an entire workbook, or any part of a worksheet. You can either print the
active sheet (Quick Print) or control exactly what you print using the Print dialog.
 We can chart our future
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL clearly and wisely
MANUALLEVEL
only when we know the path which has led
to the present.

Module Nine: Creating Charts

Charts provide a visual way of relating information. This module will explain how to insert a chart. You’ll
learn about the chart tools tab and gain an overview of the parts of a chart. You’ll learn how to change
the chart style, as well as how to resize and move a chart.

Inserting a Chart
You can create a visual representation of your data by inserting a chart. It’s as simple as selecting the
data you want to represent and then selecting the type of chart you want to use.

Use the following procedure to insert a chart.

1. Select the cells, including the labels to include in the chart.

2. Select the Insert tab from the Ribbon.

3. Select the type of chart you would like to use.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Excel displays the chart.

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Overview of the Chart Tools Tab

You may have noticed the Chart Tools tabs that appear when you inserted your chart. These contextual
tabs are used throughout Office 2010. The appropriate tab appears, depending on which type of object
you are using.

TOOLS TABS FOR WORKING WITH CHARTS

Understanding the Parts of a Chart

THE PARTS OF A STANDARD CHART .

• The Chart area includes all other parts of the chart that appear inside the chart window.

• A data marker represents a single value in the worksheet. Depending on the type of chart, this
may be a bar, a pie slice, or another shape or pattern.

• A group of related values make up the chart data series. Charts usually have more than one data
series, except pie charts, which only represents one data series.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

• An axis is a reference line for plotting data. A two-dimensional chart has an X-axis and a y-axis.
For many charts, the label is on the X-axis and the values are on the y-axis. Three dimensional
charts also have a Z-axis. A pie chart does not have an axis of any type.

• A tick mark intersects an axis as a small line. It may have a label and can indicate a category,
scale, or chart data series.

• The Plot area includes all axes and data point markers.

• Gridlines can make it easier to view data values by extending tick marks across the whole plot
area.

• You can add chart text to include a label or title. The chart text can be attached to the chart or
axis, which cannot be moved independently of the chart. Unattached text is a text box simply
shown with the chart.

• The legend defines the patterns, colors, or symbols used in the data markers.

Changing the Chart Style

The Chart Style gallery includes a number of predefined formats to enhance the look of your chart. You
can easily select a new style that changes the color and style of the chart elements.

Use the following procedure to select a new chart style.

1. Select the chart you want to format.

2. Select the Chart Tools Design tab.
3. Select the arrow in the Chart Styles area.

Select the desired chart style.

 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Resizing and Moving the Chart

Picture handles appear around the chart when you select it. These handles help when resizing the chart
manually. Make sure these handles are present when you need to move a chart.

Use the following procedure to resize a chart.

1. Click on the chart to select it. Notice the border around the chart. Each corner has three small
dots.
2. Select one of the corners and drag the picture. Notice the cursor changes to a diagonal line with
arrows at both ends. You can make it smaller or bigger, depending on which direction you drag.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

3. Release the mouse when the chart is the desired size. Notice that Excel may rearrange the
graphic elements for the best look and fit.

Use the following procedure to move the chart to a new worksheet in the workbook.

1. Select the chart.

2. Select the Chart Tools Design tab.
3. Select the Move Chart tool.
 MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL MANUALLEVEL

Excel displays the Move Chart dialog box.

4. Select New Sheet.

5. Give the new worksheet a new name, if desired.
6. Select OK.

Excel creates a new worksheet in the workbook (notice the tabs at the bottom). The chart has also been
resized to fill the worksheet.
 Man's mind, once stretched
MS EXCEL 2016 ASIC MMMANUALKKKKMMMMMMANUAL by a new idea,
MANUALLEVEL
never regains its original dimensions.

Oliver Wendell Holmes

Wrapping Up
Although this workshop is coming to a close, we hope that your journey to improve your Excel skills is
just beginning. Please take a moment to review and update your action plan. This will be a key tool to
guide your progress in the days, weeks, months, and years to come. We wish you the best of luck on the
rest of your travels!

Words from the Wise

We’d like to leave you with a few thoughts to accompany you on your Excel learning journey.

• Remember to spend some time planning your worksheet. Be clear about why you are creating it.

• Remember that everything can be changed if needed.

• Save often, and backup your work regularly.

• Try to use cell references instead of numbers in your formulas and functions.

• Try to write a formula or function once, and then copy it or use AutoFill, instead of repeatedly
writing it.

• Practice as much as you can, and as soon as you can.

• Remember the Undo button!

• If you find you are getting frustrated, come back to this manual, and try the guided exercises to
refresh your skills.