Excel

2022
For Beginners
A Quick Step-By-Step Guide to All You Must Learn in Excel to Increase
your Productivity and Confidently store, analyze, and present data.

ELIZABETH A.
 Copyright

All right reserved. No part of this book, Excel 2022 for Beginners publication, may be
reproduced, transmitted in any form or by any means, including photocopying, mechanical
method, or stored in a retrieval system without the author's written permission.

Printed in the United States of America.

Copyright © 2022 Elizabeth A.
[email protected]
 TABLE OF CONTENTS

Copyright
Chapter 1: Introduction
1.1 Most Essential Excel Features
1.2 Getting Started
1.2.1 Buy Excel
1.2.2 Use MS Excel Freely
1.3 Opening And Pinning Excel
1.4 Excel Start Screen
Chapter 2: Getting To Know Excel User Interface
2.1 Excel User Interface
2.2 Title Bar
2.3 Quick Access Toolbar
2.3.1 Customizing Quick Access Toolbar
2.4 The Ribbon System
2.4.1 Ribbon Tabs, Groups, and Commands
2.4.2 Ribbon Tabs and what you can likely do with them
2.4.3 Customizing the Ribbon
2.5 Backstage View
2.5.1 Excel Options
2.6 Worksheet Area (Cell, Row, Column, and Range)
2.7 Name Box
2.8 Formula Bar
2.9 Scroll Bars
2.10 Sheet Tab Control
2.11 Status Bar
2.11.1 Zoom Bar
2.11.2 Views
2.11.3 Customizing the Status Bar
2.12 Excel Context Help Feature.
Chapter 3: Working With Excel Sheet
3.1 Navigating the Worksheet
3.2 Entering, Editing, Selecting, and Deleting Data.
 3.3. Entering Symbols And Special Characters.
3.4 Data Validation.
Example: Inserting Drop-Down List and more
3.5 Moving, Copying, And Pasting Data.
3.6 Paste Special Options and Clipboard
3.7 Moving and Duplicating
3.8 Undo and Redo
3.9 Inserting and Deleting Rows and Columns
3.10 Resizing Columns and Rows
3.11 Hiding and Unhiding Rows or Columns
3.12 Wrapping Text and Merging Cells
3.13 Auto filling and Creating Series
Chapter 4: Custom and Conditional Formatting
4.1 Custom Formatting
4.1.1 Font formatting
4.1.2 Alignment
4.1.3 Text orientation
4.1.4 Number formatting and Setting of Decimal Points
4.2 Conditional Formatting
Example on Highlight Cells rules
Example on Top/Bottom Rules
Example on Data Bars
Example on Color Scales
Example on Icon Sets
4.3 Format Painter
Chapter 5: Using Formulas In Excel
5.1 Entering Formula
5.2 Complex Formulas: Order of Mathematical Operations in Excel
5.3 Copying Formula with References
5.3.1 Relative References
Example: Copying formula with relative reference
5.3.2 Absolute References
Example: Copying formula with relative and absolute referencing
5.3.3 Mixed References
5.3.4 Range References
Chapter 6: Working With Functions
6.1 Function Library
6.2 AutoSum.
 Example: Use AutoSum
6.3 SumIf Function
Example: Use the SumIf function
6.4 Count, Counta, Countblank, and CountIf Functions
Example: Use Count, Counta, Countblank, and CountIf Functions
6.5 IF, IFAND, IFOR, and Nested IF Functions
6.6 Vlookup And Hlookup Functions
Example: Using Vlookup
Example: Using Hlookup
6.7 XLOOKUP Function
Example: Using Xlookup
6.8 Filter Function
Example: Applying Filter function
6.9 How To Use Any Function In Excel
Chapter 7: Data Analysis - Sort & Filter, Tables, PivotTables, and Totals
7.1 Applying Conditional Formatting to an Entire Row
Example: Visualize data with conditional formatting
7.2 Sort and Filter.
7.3 Working with Tables.
7.4 Creating a Table
7.5 Exploring Table Properties
7.6 Total Rows
7.7 Removing Duplicates
7.8 Easy Calculations with Table formula (structured references)
Example: Table Formula (Structured references)
7.9 Pivot Tables
Example: Work with PivotTable
Chapter 8: Charts and Quick Data Analysis
8.1 Working with Charts
8.2 Inserting a chart
Example: Insert a Chart into a Dataset
8.3 Setting Up a Blank Chart with Dataset
Example: Insert a Blank Chart and Set it up with Data
8.4 Set your Default Chart
8.5 Adding Series to a Chart
Example: Add Series to a chart.
8.6 Creating a Combination or Composite Charts
Example: Create a Combination Chart of Different Chart Types
 8.7 Formatting a Chart
Example: Format a chart
8.8 Quick Analysis.
Example: Analyze your Data Quickly
Chapter 9: Workbook Editing and Management
9.1 Find and Replace
9.2 Commenting on a Cell
9.3 Checking Spelling
9.4 Thesaurus
9.5 Protecting your Work with Excel Security Features
9.6 Saving Your Workbook
9.7 Exporting your Workbook
9.8 Sharing your Workbook for Collaboration
9.9 Page Setups For Printing
9.10 Closing Your Workbook
9.11 Recovering Unsaved Workbook
9.12 Opening Saved Workbook
Chapter 10: 100+ Excel Shortcuts
 Chapter 1: Introduction

Microsoft Excel 2022 for beginners is a step-by-step and up-to-date guide that provides all
you need to work confidently with data and be highly productive. This book will teach you how
to use Excel’s unique features without getting frustrated. You will learn how to create a
spreadsheet from scratch, how to use the top formulas and functions like COUNT, IF,
VLOOKUP, XLOOKUP, and many more without being a programmer, how to format like a
professional, how to save, print, share and secure your vital data, how to work smartly with top
shortcut commands, tips, and tricks, how to work with tables, charts, pivot tables, conditional
formatting for data analysis and many more.
Microsoft Excel is a computer spreadsheet application developed by Microsoft Corporation.
You can utilize it on Windows, macOS, iOS, and Android operating systems. Excel forms part of
Microsoft Office Suite, and it has been a widely used spreadsheet since the release of version 5
in 1993, which has replaced Lotus 1-2-3 (another spreadsheet) as an industrial standard for
spreadsheets. Microsoft has released several updated versions of Excel, each offering more
features and incorporating better technology than the previous. The most current version of Excel
is part of Office 2021 and Office 365 suites.
Most of the amazing Microsoft Excel features have been around for a while, and the basics are
similar for most versions. Therefore, if you have an older version of Excel, Excel 2021, or Office
365, read on; you will greatly benefit from this book.

1.1 Most Essential Excel Features

Excel has many excellent features that you might not know if you do not learn the skill.
Learning the skill will save you time trying to figure things out, work smartly, and increase your
productivity.
Microsoft Excel is used for:

Calculating and Analyzing Data

Creating graphs and charts
Storing massive data for quick references with a battery of functions to answer
statistical, engineering, and financial needs.
Presenting Data.
Sectioning of data to view its dependencies on various factors using pivot table
and scenario manager.
Automating your work and programing with Macros and Visual Basic for
Applications (VBA).
It also has a wide variety of interacting features that allow user interfaces that
completely hide the spreadsheet from the user. The spreadsheet presents itself as a
self-code application or decision support system with varying customer user
 interfaces.

These amazing features are worth learning in this age of data.

WELCOME, AND LET’S GET STARTED!

1.2 Getting Started

To start using excel, you must install it on your computer or use it online. Some computers
come with Microsoft Office preinstalled, but if you do not have it, you can get it following the
steps below:
1.2.1 Buy Excel

1. Open your web browser, e.g., Google Chrome.

2. Go to the office website: www.office.com.
3. Click on Get office if you want an office or excel on your desktop and buy from the
available options:

Office 365 Family and Office 365 Personal: You can share Office 365 Family
with six people, while Office 365 Personal is limited to only one person. They
are both the same in function, and both require continuous subscriptions.
Office 365 is the best option for any user who wants access to up-to-date office
apps and cloud services. It can run on windows 11, 10, 8, 7, and macOS.
Office Home & Student 2021: This is available for a one-time payment and
contains only the essential apps (Excel, Word, PowerPoint, Access, Outlook,
OneNote, Team, and Publisher). You can only use it on Windows 11, 10, and
macOS.

4. Install Microsoft Office, and Excel is available on your desktop for use.

1.2.2 Use MS Excel Freely

If you're not interested in buying Microsoft office, you can use it freely on the Microsoft
official website. The online excel version is a lite version and does not contain all the features in
the desktop version. The website version cannot work without connectivity, making the offline/
desktop version a good choice.
To use Excel Freely Online;

visit their website, www.office.com.

sign in if you have an existing account or
create a new one if you do not have one, and excel will be available for your use.

1.3 Opening And Pinning Excel

To open excel:

1. Type excel in your search bar.

2. Left-click on the icon or Open to open a new excel workbook.
3. Choose a file from the recent list to open an already existing file.
 If you often use excel, it will be better to pin it to the start or taskbar.
To pin excel to start or taskbar:

1. Right-click on the excel icon or click the arrow down as shown

2. Choose Pin to Start or Pin to taskbar as desired.

1.4 Excel Start Screen

When you first open Excel, you will land on the start screen Home page, as shown below. The
page has a green vertical bar on the left side containing tabs for the right-side contents. The rest
of the tabs have their shortcuts link on the Home page.
 On the Home screen, you have a Blank workbook that you can use if you want to start from
scratch. Also available are several learning tutorials and templates that can quickly get you
started with excel. If you want more templates, click on more templates or the New tab at the
left-hand bar.
Below the templates is the list of your recent excel workbooks. The Open tab at the left-
sidebar or the more workbook link opens more available workbooks at the right bottom corner
of the list. Frequently open workbooks can be pinned and accessed in the Pinned beside the
Recent list.
To pin your frequently used file to Pinned:

Hover your mouse on the file name.

Click on the pin icon that appears in front of the file.
 Chapter 2: Getting To Know Excel User Interface

2.1 Excel User Interface

Click on Blank workbook on the excel start screen Home page to open excel workbook.
Excel workbook has several bars, tabs, ribbons, commands, etc., as shown and explained below.

2.2 Title Bar

Title Bar shows the title of the workbook. Book 1 (and then Book2, etc.) is the excel default
title name. You can change the workbook’s name to the desired one when you save the
workbook.
The far-right of the title bar contains buttons to control the workbook views, as shown below.
2.3 Quick Access Toolbar
The Quick Access Toolbar contains the icon list of all the most frequently used
excel commands such as Save, Redo, Undo, etc.
2.3.1 Customizing Quick Access Toolbar
You can customize the quick access toolbar by adding your commonly used command.
To customize the quick access toolbar:

1. Click the drop-down button at the far right of the toolbar, check or uncheck any of
the commands to add or remove it, respectively, as desired.

2. Or right-click on the desired command found in the Home ribbon or any other tab
ribbons and select Add to Quick Access Toolbar.
Alternatively,

1. Right-click on the quick access toolbar. A menu appears.

 2. Select Customize Quick Access Toolbar from the listed option.
Excel Options dialog box appears.

3. Search and select the desired command(s) from the left-side pane.
4. Click the Add button.
5. Press OK.

To remove any of the Quick Access Toolbar commands, right-click on the tool and click on
Remove from Quick Access Toolbar from the list.
2.4 The Ribbon System
Microsoft Excel has a lot of commands that are neatly organized into the ribbon system. This
system makes it easy to locate any command.
2.4.1 Ribbon Tabs, Groups, and Commands
The Ribbon system is tailored into three components:

Tabs: These are buttons with descriptive names that allow the user to quickly
find and use a group of commands to complete a specific task. Examples are
Home, Insert, Draw, Page Layout, etc. The ribbon of each tabs will be
displayed below it when you click on the tab.
Groups: Ribbon contains groups of related commands or Excel features; e.g., the
Home tab ribbon has Clipboard group, Font group, Alignment group, and so
on.
 Commands: These are Excel feature buttons that perform a specific task.

Most groups but not all have a dialog box launcher at their right-side corners for more
group-related commands not available in the ribbon.
2.4.2 Ribbon Tabs and what you can likely do with them
Listed below are the ribbon tabs with their groups.

a. File – leads to the backstage view where some basic required options such as
save, print, share, account, options, etc., are found (to be discussed under section
2.5 backstage view).
b. Home - Home tab ribbon contains frequently used commands such as copying
and pasting, sorting and filtering, formatting, etc. It consists of Clipboard, Font,
Alignment, Number, Styles, Cells, and Editing groups. You can access more
commands by clicking the dialogue box launcher arrow in each group.
c. Insert – Most of the commands in this ribbon are used to add different objects
like images, charts, pivot tables, headers, footers, equations, hyperlinks, etc., to
the worksheet for data analysis and presentations. It consists of Tables,
Illustrations, Add-ins, Charts, Tours, Sparklines, Filters, Links, Text, and
Symbols groups.
d. Draw – You can use the draw ribbon for data highlighting. It helps you draw with
a digital pen, mouse, or finger. It consists of the Tool, Pens, and Convert groups.
e. Page Layout – You can use this tab to manage worksheet appearance, both on-
screen or printed, by setting the gridlines, page margins, object alignments, and
print area. You can also use it when ordering graphics on your worksheet. It
consists of Themes, Page Setup, Scale to Fit, and Arrange groups.
f. Formula – You can use the formula tab to insert formulas and functions (an
essential and handy feature of excel) in the worksheet area and check for errors
within the formulas. You can also use it to define the name, create the name
range, review the formula, and make valuable calculations for dynamic reporting.
It consists of Function Library, Defined name, Formula Auditing, and
calculation groups in the default arrangement.
g. Data – Various command buttons used to import data by connecting to the server
or automatically from the web, MS access, etc., are found in the Data tab groups.
Sort & Filter, exceptional tools for data analysis, are also available in the data tab.
The tab also contains commands to group and ungroup rows and columns.
h. Review - The review tab contains commands to proofread existing data, protect
your worksheet area, and mark up your worksheet area for others to review. It
consists of the Proofing, Accessibility, Insights, Language, Comments,
Protect, and Ink groups.
i. View – The view tab contains Workbook View, Show, Zoom, Window, and
Macros groups in the default arrangement.
j. Add-ins - These are optional commands and features added or that you can add to
excel for additional functionality. Excel has some inbuilt add-ins, such as Solver
and Analysis ToolPak, and some others can be added.
k. Help – This is an excel feature to assist users in finding online help.
 l. Tell me what you want to do or Search (for excel 2021 and 365) is a search bar
where you can search for whatever thing you want to do, and excel will bring out
the command.
m. Contextual Tabs - These tabs only appear when table, image, picture, figures,
chart, etc., are inserted or selected in the worksheet. They are majorly used to
analyze, design and format the selected item.

2.4.3 Customizing the Ribbon

To suit your desire and work, you can remove or add any ribbon tabs, groups, and commands.
To customize the ribbon:

1. Right-click on the ribbon. A menu box appears.

2. Select Customize the Ribbon.

The Excel Options dialog box appears.

3. Select an option from Choose commands from the drop-down button

 4. To create a new tab, click the New Tab button.
To add a new group to an existing tab, select the tab at the right-side pane and click
the New Group button. Use the Rename buttons to rename your custom group or
tabs.
Note: You can only add commands to a custom group, i.e., you cannot add commands
to the Excel default groups.

5. Search and select the desired command(s) from the left-side pane.
6. Click the Add button or double-click on the command.
7. Press OK.
To remove a custom tab, group, and command, select it in the right-side pane and click the
Remove button. You can reset all customization using the Reset button at any time.
2.5 Backstage View

The Backstage View is the central managing place for all Excel sheets. To go to
the backstage of the excel workbook, click on the File menu at the top left corner of the Ribbon
Tabs bar.
You can create, save, open, print, or share your worksheet from backstage. Starting from the
top, the:

New option allows you to open a new excel sheet in the current workbook.
Open option enables you to open the workbook you have created earlier from
different locations.
Info option gives information about the excel workbook, allowing you to protect,
inspect and manage your workbook.
Save option save the current workbook as it is.
Save as will enable you to rename, select the desired location and save the current
workbook.
Print option allows you to print your workbook in the desired format.
Export allows you to create the PDF or XPS document of your excel workbook.
 Publish allows you to publish your spreadsheet in Power BI. You must be signed
in to be able to do this.
Close will enable you to exit the current workbook.
Account option contains all the document holder’s details.
Options opens the Excel Options dialog box.
The Top-left-corner arrow allows you to go back to the workbook area.

2.5.1 Excel Options

Excel Options is where you can do all excel customizations and default settings. Most
customize commands, when you click them in excel, bring up this same dialog box called Excel
Options.

Come to this box if you think you need some changes to the default settings and customization
of your excel. Select any option that seems related to what you desire to do in the left-side pane
and make the settings at the right-side window.
2.6 Worksheet Area (Cell, Row, Column, and Range)
The Excel worksheet area is where you will input your data: texts, numbers, formulas, or
functions. Excel worksheet is made up of a grid of cells (i.e., thousands of small rectangles)
arranged in numbered rows and letter-named columns.

Rows are horizontal blocks, starting from 1 to 1,048,576. i.e., there are one
million, forty-eight thousand, five hundred and seventy-six rows in an excel
worksheet area.
Columns are vertical blocks named by letters A to Z through, AA to AZ, etc.
There are 16,384 columns in an excel sheet.
A Cell is the intersection of a row and a column. Each excel cell has a cell name
(also called cell reference, or cell address). An excel cell gets its name from the
column and the row that meet to form the cell. For example, the name of the cell
indicated below is C7.
 Active Cell: It is the currently referenced cell. It is the last clicked cell or the first
cell A1 when you first open an excel sheet. Any data you enter will be put in the
active cell.
Cell Range is a group of cells. Cell range name is the cell names of the first and
the last cell separated by a colon. For example, the cell range containing cells B1,
B2, B3, B4, B5, B6, and B7 will be called B1:B7. See more examples below.

A5:D5 A1:A5 A3:D7

2.7 Name Box
The name of the active cell is displayed in the Name Box as the intersection of its column
name by its row name, e.g., the first cell is addressed as A1. You can also use the name box to
name a group of cells (range). The drop-down arrow at the name box side displays the named
range(s) list.
2.8 Formula Bar
The formula bar always displays whatever data you enter into your selected cell or what is
really in the selected cell. Your cell can hold three things; texts, numbers, or formulas. A formula
is a mathematical calculation you write to get an answer. If the selected cell contains a
calculation or function solution, the formula bar displays the actual formula that gives the value
in the cell. The X tab deletes an entry, the check tab marks an entry, and fx indicates functions
you can access when you want to write your formulas.
2.9 Scroll Bars
There are two scroll bars in an excel worksheet area, the vertical and horizontal scroll bars.
The vertical scroll bar allows you to scroll your worksheet downward or upward along the
numbered rows. The horizontal scroll bar will enable you to scroll your worksheet sideways
along the letter-named columns. To scroll your sheet, left-click on the scroll bar, hold and drag
down/up or left/right as the case may be. You can also left-click on the arrows at the terminals of
the scroll bar, hold down for fast movement and click intermittently for slow movement.
2.10 Sheet Tab Control
An excel workbook can contain as many worksheets as your device's memory can take. The
blank workbook comes with only one sheet (sheet 1). You can add new worksheets (sheets 2, 3,
etc.) to your workbook with the sheet tab control. You can also use sheet tab control to rename,
hide/unhide, move or copy, rearrange, secure, etc., the worksheets.
 To create a new worksheet, click on the + sign at the sheet control tab and create
a new sheet.
To rename a worksheet, right-click on the worksheet and select rename from the
pop-up menu (You can as well double-click on the worksheet name). Type in
your desired name and press enter
To copy and move a worksheet, right-click on the desired worksheet name at the
sheet tab control and select the copy or move option, choose where you want to
move/copy the sheet from the dialog box that pops up and check the create a
copy option box to create a copy of the excel sheet.
To move or rearrange the worksheets, left-click, hold down and drag the sheet to
the desired place.
To hide a confidential worksheet(s), right-click on the sheet's name you want to
hide and select the hide option.
To unhide an excel worksheet, right-click over the sheet name area. Select unhide
option. A dialog box appears where all the sheets that have been hidden are listed,
choose the desired sheet to unhide, press ok, and the sheet will appear on the
sheet tab control.

2.11 Status Bar

The status bar contains specific information about the excel sheet or selected range of data. It
gives the state of the active cell. There are four states that an excel worksheet cell can be,
namely: Ready state, Edit state, Enter state and Point state. The ready state indicates that the
worksheet can accept the user’s input. The edit state is enabled when you double click a cell that
already has data, enter state is enabled when you select and double click an empty cell to input
data. Point state is enabled when you enter a formula into the cell referencing the data present in
another cell.
By default, the status bar contains the zoom slider and some page view icons. It also gives the
sum, average, and count of any highlighted range of data.
2.11.1 Zoom Bar

The zoom bar allows you to zoom in and out of the sheet to make your sheet cells
larger and smaller.
Drag the slider towards the right side (+) or left side (-) to zoom in and out. You can
also click on the bar to position the slider.
Click on + and – to increase or reduce the view with multiples of 10
Click on the percentage tab to open the zoom window to set the page view.
The zoom can only be set between 10% and 500%.

2.11.2 Views
Views suggest ways to look at your files. There are three default view options available on the
status bar: normal, Page layout, and Page break preview. You can find other view options in
the View tab.
2.11.3 Customizing the Status Bar
There are quite a number of information you can display on the status bar. You can customize
the status bar for whatever information you desire: To do this;
 right-click on the bar.

A Dialog box appears. You can scroll down for more options.

check or uncheck an option to add or remove it from the status bar as desired.

2.12 Excel Context Help Feature.

The context help feature gives Excel users appropriate information about Excel commands to
educate them about their work. To get information about a particular command:
• Hover on the command, i.e., take your cursor over the command and wait for a little.
• A context help widow describing the command will appear as shown below.
• Click the Tell me more link to learn more about the command on the Office website.
With this feature, you can have an idea of what all commands do in Excel.
 Chapter 3: Working With Excel Sheet

Excel worksheet can be used to store, process, and present data. The data can be texts,
numbers, and formulas. Written below are step-by-step guides on how to work with an excel
worksheet.
3.1 Navigating the Worksheet
It is essential to know how to move around excel workbook for ease of use. Navigation can be
done either with your keyboard or with a mouse.
With keyboard
Use;

Tab key to move the cursor to the right of the active cell.
Shift + Tab keys to move the cursor to the left of the active cell.
Enter key to move the cursor down to the cell below the active cell.
Shift + Enter keys to move the cursor to the cell above the active cell.
Arrow keys to move the cursor in the direction of the arrows.
Home key to move the cursor from the active cell to the first column of the same
row.
Ctrl + Home to move the cursor to the first cell, i.e., A1
Ctrl + End to move the cursor to the last cell with data on the sheet.
End + Arrow Key to move the cursor to the last or first cell of the present
column or row with data in the direction of the arrow.

With Mouse
The mouse pointer on the worksheet area is usually a cross , but it changes shape in certain
areas of the worksheet or when performing specific tasks. The two buttons of the mouse and its
center scroll button perform the same functions as the general computer use.
Step-by-step guides on how to work with excel using your mouse and keyboard are given
below.
3.2 Entering, Editing, Selecting, and Deleting Data.
To Enter Data into a cell:

1. Select the cell by moving your cursor to the cell and left click
2. Input your data using your keyboard.
 Note: If the selected cell already has content, the newly inputted data will automatically
replace the content.
To edit a cell with content:

1. Select the cell you want to edit.

2. Double click the cell or Press F2 on your keyboard. You can also click on the
formula bar to make changes from there.
3. Input your data into the cell or formula bar using your keyboard.

To select:

A group of cells – double-click, hold down and drag the cursor to the desired
point.

A single column of a sheet or table - Click the column letter or the first cell of
the table's column.
Multiple columns of a sheet or table – Double-click, hold, and drag over the
column letters or first cell of the table's columns.
A single row - Click the row number or first row of the table.
Multiple rows – Double-click, hold, and drag over the row numbers or first row
of the table.
Entire sheet or table - Click the top left corner of the sheet or table.

A range of cells, columns, or rows that are together – left click to select the
first item, then hold the shift button on the keyboard and click on the last cell or
drag down to the last cell.
A range of cells, columns, or rows that are not together (non-contiguous) –
left click to select the first item, then hold the ctrl button on the keyboard and
click on each additional item.

To Delete Data from a Cell or Range:

1. Select the cell or range.

2. Press the Delete button on the keyboard.

Or

1. Select the cell or range whose data you want to delete.

2. Right-click over it. A menu appears.
 3. Choose the delete option from the dialog box.

The first option only deletes the cell's content and leaves the formatting, while the second
option clears both. For more delete (clear) options, go to the Home tab in the Editing group and
click the Clear command to get a list of the available delete options, as shown below.

3.3. Entering Symbols And Special Characters.

Some symbols and characters cannot be found on the keyboard, to insert such symbol or
special character;

1. Go to the Insert tab under the symbol group.

2. Click on the symbol, look, and click on your desired symbol.

3. Click the Insert button to display your symbol in the active cell.

For special characters:

4. Click the character tab.

5. Click on the desired character and click the insert button.

There are so many symbols that you can use in this feature. You can change the font and the
subset to view them. The dialog box also displays the Unicode name and the character name of
the selected symbol. You can familiarize yourself with the symbols by changing the font and the
subset for you to quickly locate anyone when you need them.

3.4 Data Validation.

Data validation controls and restricts data entry to avoid mistakes, errors, and unwanted data.
To set data entry restrictions in a cell:

1. Select the cell you want to restrict its data entry.

2. Go to the Data tab.
3. Click on Data Validation in the Data Tools group.
4. Choose the desired validation option, in the Allow field of the Data Validation
window, under Settings.
5. Write out your option data directly in the Source field, separating them with
commas, or select your data set directly in your workbook.
6. Click on the Input Message tab, and Input a message to give an idea of what can
be put in the cell or use the default.
7. Click on the Error Alert tab, input your desired error message, or use the default.
8. Click on Ok.
Note: All excel cells are set to allow any value by default. Choosing any option from the list will
restrict the acceptable cell entry.
You can also use formula to customize what the cell can accept by choosing Custom in the
Allow field.
Example: Inserting Drop-Down List and more
Create a data entry template for your company with the following headers using an excel
spreadsheet.

Sales Person
Product
Product Description and
Price.

The allowable data input must satisfy the following criteria:

1. You must choose the salesperson's name and product from a drop-down list
2. The product description character length should be more than 20 for a proper
description.
3. The price should be greater than $1000.

Solution

Make a separate list of the salespersons and products. You can as well input the
names of the salespersons and products directly in the Source Field later on.
Input your data header.
Follow steps 1 – 8 above.

1. Do as illustrated below to get the drop-down lists for the salesperson.

Follow the steps above to get the product dropdown lists as illustrated below.
 2. Follow the illustration below to set the product description field to accept more
than 20 characters—select Text length in the Allow field.

Check:

3. Follow the illustration below to set the price field to accept values greater than
$1000. Select custom in the Allow field and input =G3>1000 in the formula
field. G3 is the first cell name under Price and can change based on your data
position.
 Check:

The data validation can be applied to the remaining rows by either selecting all the rows
before using data validation or copying and pasting the validation to other rows.
To paste the validation, right-click and select Paste Special…, check the Validation button,
and apply it to all.
3.5 Moving, Copying, And Pasting Data.
Cutting an item removes it from its initial position and places it where it is pasted while
copying reproduces it in another place.
There are various ways of copying, cutting, and pasting items in excel; some of the top ones
include the following:
Method 1:

Select the data you want to cut or copy.

Click on the Home tab and select the Copy or Cut command as desired.
Place your cursor to where you want to paste your item.
Click on the Paste command in the Home tab.

Method 2:

Select the data you want to copy or cut.

Right-click on the selected item.
Select the Copy or Cut option from the menu that appears as desired.
Move your cursor to the desired location and right-click on your mouse.
Select the Paste option with a left-click from a menu that appears.
Alternatively, you can use the shortcut commands:

Ctrl + C to copy
Ctrl + X to cut
Ctrl + V or Shift + Insert to paste
Tips: You can use the paste option to paste the last copied or cut item many times and at many
places as desired.
3.6 Paste Special Options and Clipboard
Paste Special Options
While working on your worksheet, you will most likely want to copy or cut some data that
already have formatting like font type, font size, color, etc. When you copy or cut these
formatted texts into Excel, Excel automatically reformats the texts to the destination format,
which might not be what you want. However, Excel provides Paste Special Option to help you
retain the original format and paste the item as a link, picture, or plain text, depending on your
choice.
Paste special is an Excel feature that provides several format options to Paste your item.
To use Paste Special Options:

1. Copy or cut the item you want to paste, e.g., texts, pictures, shapes, etc.
2. Click where you want to insert the item in your presentation.
3. Go to the Home tab in the Clipboard group and click the arrow under Paste.
4. Select Paste Special from the menu that comes up.
 5. Choose one of the options from the pop-up window as desired. Hover on each of
the paste icon to know how your data will be pasted before selecting an option.

Alternatively, you can use the shortcut command, Ctrl + Alt + V, on your keyboard to call
the Paste Special window.
Note: The Paste Special options change based on the item you want to paste.
Tips: You can use the paste special to link your copied item to its source for automatic update.
Clipboard
A clipboard is where the items you cut or copied are temporarily stored. You can recall the
item for use with a paste command. Microsoft has a multi-clipboard that can store up to 24 items
copied or cut, unlike a window clipboard that can only hold one item at a time. The paste option
only recalls the last item copied or cut, and you can assess the other items by opening the
clipboard.
To paste any of your previously copied items from the clipboard:

1. Go to the Home tab, under the Clipboard group.

2. Click the expandable dialog box button to display the clipboard with the list of all
the copied/cut items up to 24 items.

3. Click on your desired item to paste it at the insertion point or

4. Move your cursor to the item you want, in front, you will see a drop-down button
with options to paste or delete.
 5. Select Paste, and your item will be inserted in the insertion point or Delete to
remove the data from the clipboard if you do not need it again.

Click the X button to close the clipboard panel and arrow down options to either change its
location (move), resize, or close it as desired.
3.7 Moving and Duplicating
Moving an item removes it from its initial position to another location just like cut and paste,
while duplicating an item reproduces it in another place like copy and paste. It is easier to move
or duplicate than to cut, copy and paste.
The difference between Cut/copy, and move/duplicate is that while cut/copy stores their items
in the clipboard, move/duplicate never does. Therefore, it is advisable to use the cut/copy and
paste command if you need the item more because you can easily retrieve them on the clipboard.
To move cells, columns, or rows:

1. Select the data you want to move. You can move rows or columns.
2. Move the cursor to the edge of the selected area until you see a crossed double
head arrow.
3. Drag the area to the desired place and drop.

To duplicate an item:

1. Select the item you want to duplicate.

2. Click and hold your mouse on the item.
3. Press and hold ctrl on the keyboard, and move the cursor to the edge of the
selected area.
4. Drag and drop to the desired location while still holding the ctrl button.
5. Release the ctrl button.

Note: If you release the Ctrl button before releasing the item in the new location, the item will
be moved and not duplicated.
3.8 Undo and Redo
Excel keeps track of most of your tasks while working until you close the workbook. You can
undo tasks like formatting, typing, deleting, etc. Some actions like clicking on a command,
saving your workbook, deleting stored items on a clipboard, etc., cannot be undone. By default,
Excel can save up to 100 tasks you can undo.
To redo a task only a step back;

Click on the Undo icon in the Quick Access Toolbar once or more for more
steps backward.
For many steps back;

Click on the dropdown button in front of the undo icon.

A list of all the tasks you have performed since you opened the workbook or up to
100 appears.
Select a point in the list, and excel will undo everything you have done to that
point. You can only undo all the steps from the present to a point you select on
the list. You cannot undo a single action that is not immediate.

If you do not want to undo your task again, the redo command is also available for you to
use in the Quick Access Toolbar.
The redo and undo action command becomes inactive if there is nothing to undo or redo.
Keyboard Shortcuts:
Press Ctrl + Z to undo.
Press Ctrl + Y to redo.
3.9 Inserting and Deleting Rows and Columns
To insert a new row or column to your work:

1. Select where you want to insert a new row or column in the worksheet area.
2. Click the insert command button found in the Home tab in the cell group.
3. Select from the options as shown below.
 OR

1. Move your cursor to where you want to insert a new row or column.
2. Right-click and select the insert option (if you right-click on the row number or
column header) or insert… option (if you right-click within the worksheet area).

3. Select the desired option in the pop-up dialog box if you select the select…
option, i.e., right-click the worksheet area.

The select option automatically inserts a new row, if you right-click the row number and a
new column, if you right-click the column header. An insert option appears around the new
inserted column or row. Click on it to choose an option.

To insert more than one row or column:

Select the number of rows or columns you want to insert and follow steps 1-3 above.
Note: Excel inserts a new row at the top of the selected row and a new column by the left of
the selected column by default.
To delete rows and columns:

1. Select the row(s) or column(s) you want to delete

2. Click the delete command button found in the Home tab in the cell group.
3. Select from the options as shown below.
 OR

1. Select the row(s) or column(s) you want to delete.

2. Right-click and click on delete or delete… from the dialog box.

3.10 Resizing Columns and Rows

To change the width of rows and columns:

1. Move your cursor to the right-side edge of the column's header or bottom edge of
the row number and look for the double-headed arrow.

2. Drag the double-headed arrow in either direction of the column or row to expand
or clasp as desired.
3. For multiple columns or rows, select the number of columns or rows and follow
steps 1 and 2 to adjust their width uniformly.

You can also:

1. Double click the double-headed arrow to adjust the column(s) or row(s) to fit the
most extended content. This feature is known as autofit.

Alternatively;

1. Go to the Home tab under cell group.

2. Click on the Format command button.
3. Select an options from the drop down menu.
4. Input the desired column width or row height in the pop-up dialog box.
5. Enter ok, and the column(s)or row(s) size will adjust accordingly.
3.11 Hiding and Unhiding Rows or Columns
To hide row(s) or column(s):

1. Select the row(s) or column(s) you want to hide.

2. Right-click on the selected area and press hide from the list

Alternatively:

1. Click on the Home tab, under Cell group

2. Click the Format button.
3. Select Hide & Unhide option.
4. Choose from the list as desired.

OR

1. Move your cursor to the edge of the header of the selected area until it turns .
2. Drag the line of the row(s) or column(s) to clasp with the next one.

To Unhide row(s) or column(s):

1. Select all the areas you want to unhide the row or column
2. Right-click on it and Select unhide from the dialog box. Or
3. Double click on the right-hand edge of the header of the column or row to display
all the hidden rows or columns.
 Alternatively,

1. Locate the missing numbers or letters and move your cursor to the edge of the
header.
2. Drag apart the lines when you see

Note: You can hide a row or column if you don't want to display or print them but not to
protect confidential information. Anyone who has access to the document can unhide it.
3.12 Wrapping Text and Merging Cells
When you input data that is longer than an excel cell into the cell, excel will display the data
across the next cell(s). To make your data fit nicely into an excel cell, you can either wrap the
text in the cell or merge all the cells that the data covers together.
Wrap Text modifies excel cell height to accommodate and display all its contents while
merging combines the selected cells to form a single large cell.
To Wrap Text in a cell:
1. Select the cells you want to wrap their content.
2. Go to the Home ribbon in the Alignment group.
3. Click on the Wrap Text command. The cell height will automatically increase to
contain the cells’ contents.

To merge cells:
1. Select the cells you want to join.
2. Go to the Home ribbon.
3. Click Merge & Center command drop-down button in the Alignment group.
4. Select the desired options from the drop-down list:

Merge & Center to merge all the selected cells into one and keep only the
upper-left data at the cell center.
Merge Across to merge all the selected cells only horizontally and keep
the number of selected rows.
Merge Cells to merge all the selected cells into one big cell and keep only
the upper-left data at its default position.
Unmerge Cells to unmerge already merged cells.

3.13 Auto filling and Creating Series

It is a unique feature of excel that allows you to quickly fill up cells with text, values, dates,
formulas, and other unique predefined sequences. It works differently based on what you want to
fill.
The table below shows how excel auto-fills different entries.
Autofill a single cell Autofill multiple cells
Repeat the sequence of text
Text Repeat the text in the cell
in the selected cells
Excel will attempt to analyze
Number Repeat the number in the cell the sequence and predict the
next numbers
Next date in the sequence but can Excel will attempt to analyze
Dates(numeric) be overridden by right dragging the sequence and predict the
and selecting copy cell next numbers
Excel will attempt to analyze
Dates (text) Next date in the sequence the sequence and predict the
next dates.
To autofill cell(s):

Select the cell(s) that contain the data you want to autofill with other cells.
Place your cursor to the bottom-right of the selected cell(s).

Drag the fill handle at the bottom-right corner of the cell to the desired location.

Tips: You can quickly create a number series as follows:

Enter number 1 or your starting number into the first series cell.
Place your cursor at the bottom-right of the cell.
Press and hold down the ctrl key.
Drag the fill handle to the last cell you want the serial number to stop. Excel does
an excellent job of creating serial numbers instead of repeating the number
 Chapter 4: Custom and Conditional Formatting

Formatting of data in an excel sheet changes the appearance of the sheet and data without
affecting the inputted data for easy readability, easy analysis, and making it look good. You can
also format the cell of an excel sheet for various types of data they can hold, e.g., date, currency,
time, telephone number, etc.
You can find most Formatting commands in the Home tab Ribbon and Contextual Tabs.

To format an excel spreadsheet:

Go to the Home tab and look for the desired commands, as will be explained
further below or
Right-click on the cell or group of cells (range) you want to format and choose
the format cell option or
Select the object (table, figure, chart, etc.) you want to format or design, a
contextual tab(s) comes up; choose the desired format/design or
Use Excel’s shortcut ctrl+1, and the format dialog box pops up.

4.1 Custom Formatting

The categories of Custom formatting in excel are;

Font
Alignment and
Number (including text)

4.1.1 Font formatting

The Font group has three subgroups: font, border, and fill. You can use the font group to
adjust the font style, size, color, and look of the data in a cell or group of cells, as the case may
be.
To format your data:

1. Select the data you want to format.

2. Go to the Home ribbon.
3. Select any of the following as desired:

Font type dropdown list to change the shape of the cell(s) content.
Number dropdown list or the Increase and Decrease buttons to
change the font size.
Bold to make the data darker and thicker.
Italics to slant the data.
 Underline to underline the data with a single black line. Click the
dropdown button in the front to choose the underline color and style.
Font Color to change the color of the cell content. The color you
selected last will be applied to your data. To change the color, use the
dropdown icon.

to change the background color of cells. The color you

selected last will be applied to your data. To change the color, use the
dropdown icon.
Borders to put borders around the selected cells. The border style you
chose last will be applied to your data. To change the border style, use the
dropdown icon.

4. Press OK when you are done with the settings.

You can explore more options by clicking the expandable dialog box button at the right
bottom corner of the group .
4.1.2 Alignment
Alignment is a formatting attribute that determines the position of a cell content with respect
to the cell grid lines. By default, texts are aligned to the bottom-left of the cell, while numbers
are aligned to the bottom-right of the cell. You can align the data in the excel cell either
horizontally or vertically. Each horizontal and vertical alignment has three positionings.
Vertical Alignment can either be;

1. Top aligns
2. Middle align
3. Bottom align:

Horizontal Alignment can also be;

4. Align left
5. Center
6. Align right

To align text or number with the desired alignment option:

1. Select the cell or range you want to align its contents.

2. Go to the Home ribbon in the Alignment group.
3. Select the preferred alignment option.

4.1.3 Text orientation

 You can rotate your text in the cell diagonally or vertically as desired using the orientation
command. This feature is handy when you want to label a narrow column.
To change your text orientation:

1. Go to the Home ribbon; in the Alignment group.

2. Click on the Orientation icon . A list of options appears.
3. Select the desired option from the list, and your text will automatically change
orientation.
4. Select Format Cell Alignment to rotate the text to a specific angle other than the
available ones (optional)

4.1.4 Number formatting and Setting of Decimal Points

The number group is a valuable tool for formatting different data types in an excel worksheet.
To apply number formatting

1. Select the cell(s) you want to format.

2. In the Home ribbon, go to the Number group.

3. Click the drop-down arrow attached to the command.

4. Select your desired command:

General categories specify that there is no specific format for the cell.
Number displays that the cell can hold only numbers.
Currency option serves the purpose of maintaining an account.
Date option allows a different type of date format
Time allows a different kind of time format.
Percentage option shows the cell as a percentage even if you enter a decimal or
fraction number.
Fraction section displays the cell as a fraction even if you enter your data in
decimal number or percentage.
Scientific categories display cells in exponential form.
 Text option is for normal text data.
Special options only allow particular data types such as telephone number, zip
code, social security number, etc.
Custom allows you to format your data uniquely.

General is the default number format for excel cells. With General, excel tries to guess the
most appropriate number format for any content you input into the cell.
To set the Decimal Points of excel cell:

1. Select the cell(s) you want to format.

2. Go to the Home tab; in the Number group.
3. Click the increase decimal or Decrease Decimal command.

The number of decimal places of the selected cell(s) increases or reduces by 1 per
click. Click multiple times to increase or decrease by more number.

4.2 Conditional Formatting

Conditional formatting allows you to format your work with specified rules or conditions. It
helps to identify trends in data based on the set condition. The cells or rows that match the
condition will be displayed:

With a specified color or

Your chosen custom format.

There are predefined rules you can use, and excel also gives an option for you to define your
formatting formula. The predefined rules are highly customizable.
To conditionally format your data:
1. Select the data set you want to format.
2. Go to the Home ribbon under the Style group.
3. Click on Conditional Formatting.

A menu box appears.

4. Select an option based on the condition you want to set.

highlight Cell Rules option has a list of options that defines the selected range
values, texts, and dates that are greater than or equal to, or less than the set value.
Choose the desired option from the list.
top/Bottom Rules option also has options highlighting the top or bottom values and
upper and lower averages.
Data Bars opens up a palate with different predefined color data bars that you can
use. The length of the data bar denotes the value in the cell. A more extended bar
represents a higher value.
Color Scales displays the values in the range of cells as the gradient of two or more
colors. The shade of the color represents the value in the cell.
Icon Set contains different sets of icons with different colors that you can use. Each
icon represents how high or low the values of the cells in the selected range are.

The New Rule... option opens up a new formatting view dialog box for customizing
the above-listed options and more. This same dialog box opens up when you click the
more rules …option in each listed option. You can also use the new rule when you
don't want to use the predefined options or when you want to adjust them.
 The rule types from the New Rule dialog box are in six categories:
➢ The first category helps customize the color scale, data bar, and icon set.
➢ The second category is a valuable tool for personalizing the highlighted cell rules
option,
➢ the third and fourth are for the top/bottom rules option,
➢ the fifth is for duplicate or unique rule and
➢ the sixth category assists in inputting format rules/formulas that are not predefined.
Use the section below the rule type section to customize the chosen rule. The
description options available depend on the type of rule you selected.

Clear Rule allows you to remove conditional formatting rules.

Manage rule option opens up the rule manager dialog box to add, delete, edit or
format the rule according to your preference.

Example on Highlight Cells rules

Given an excel spreadsheet with data set as shown below, Format the revenue cells greater
than $6000 using Highlight Cells Rules.

Solution: Follow the illustration below to format the data.

Example on Top/Bottom Rules
Format the three topmost revenues of the data in the example above.
Solution: Follow the illustration below to format the data.
Example on Data Bars
Format the data in the first example with Data Bars for easy and quick analysis.
Solution: Follow the illustration below to format the data.
 From the formatting, you can easily see the value of the largest revenue as $64,324 and the
lowest as $31,098.
Example on Color Scales
Format the data in the first example with Color Scales to easily see the lowest revenues.
Solution: Follow the illustration below to format the data.
Example on Icon Sets
Use a customized icon sets to format the data set in the first example, following the criteria
below:

Revenues greater or equal to $60000 should be marked.

You should note the revenues less than $60000 and
Revenues less or equal to $35000 should be flagged.

Solution: Follow the illustration below to format the data

4.3 Format Painter
When you want to format a section of your spreadsheet just like you have formatted one,
instead of going through the stress of doing the formatting all over again, Excel has a special
command called Format Painter just for that. Format Painter copies the format of one and
applies it to the other.
To use a Format Painter:

1. Select the cell, range, or table that has your desired format.
2. Go to the Home tab and in the Clipboard group,
3. Click once on the Format Painter.

Your cursor turns to a paintbrush.

4. Move your cursor to the cell, range, or table you want to format.
5. Select the cell, range, or table.

Excel automatically formats the selected portion like the one you copied.
Format Painter turns itself off after each use. To keep it active for continuous use,
double-click on it.
Press the Esc key or click on the icon again when you are done.
 Chapter 5: Using Formulas In Excel

Formulas are one of the most powerful tools of Microsoft Excel. You can use Formulas to
calculate numerical values and thereby make work easy.
5.1 Entering Formula
Formulas always start with equal to sign (=) and they can contain mathematical operators,
values or text, cell references, or worksheet functions. When you input a formula into a cell,
excel will display the result in the cell when you click the enter key. However, you will see the
formula in the formula bar. You can also enter the formula directly into the formula bar, and the
result will show in the active cell.
The standard mathematical operators are used in the excel formula with the indicated symbols
as shown in the table below.
Addition +
Subtraction -
Multiplication *
Division /
Power/Exponential ^
Parentheses ()
Note the keyboard sign for each operator as shown above.
There are various ways to enter a formula in excel;

You can input your numbers directly,

you can use the cell references,
or use what is called the point-and-click method.
For a simple formula, you can quickly enter the formula directly with your keyboard. Use the
cell addresses if the values can change over time. When working with a large amount of data, the
Point-and-click method is better used to save time and reduce stress.
To write a simple formula in excel:

1. Select the cell you want to enter the formula.

2. Type an equal to sign (=).
3. Input the numbers or cell references directly with the operators. e.g., put; =2+3 or
=A1+A2 (this adds the content of cell A1 with that of A2)
4. Press, enter.

The result or solution of the formula will appear in the cell while the formula shows in the
formula bar.
To use the Point-and-Click method

1. Select the cell you want to insert the formula.

2. Type an equal sign (=).
3. Select your first reference cell(s) to use in the formula, the cell address will appear in
the formula, and a dash-colored line will appear around the referenced cell.
4. Type the mathematical operator you want to use.
5. Select the other cell(s) you want to reference in the formula, the cell address will
appear in the formula, and a dash-colored line will appear around the referenced cell.
6. Press the Enter key. The formula will be executed, and the calculated value will be
displayed in the cell.
If you are working with more than two referenced cells, select an individual cell after adding
the mathematical operator continuously till the last cell.
To edit a formula
You might sometimes want to edit an existing formula. You can follow the steps below to edit
a formula;

1. Select the cell containing the formula.

2. Click the formula bar to edit. Alternatively;

Double-click the cell containing the formula to edit directly.

3. When you are done editing, press the Enter key on your keyboard or the Enter
command in the formula bar. Your formula will be edited and updated.
Example: Adding the numbers in the cells below;

Tips: The best option is to use cell references or directly select the cell value from the cell. This
method ensures your formulas are always accurate because even when you change the value of
your referenced cells, you don’t need to rewrite your formula.
Note: If the value or content of a cell is larger than the cell column width, you will get pound
signs (#######) instead of a value in the cell. Increase the width of the cell to display the
content.
5.2 Complex Formulas: Order of Mathematical Operations in Excel
A formula is complex if it contains more than one different mathematical operator—for
example, 10-6+4. It is good to know how excel calculates formulas for complex formulas to
avoid misinterpretation.
The Excel order of operators is as follows:

1. Operation in the Parentheses (Bracket).

2. Powers (Exponents) calculations.
3. Multiplication and Division (whichever comes first).
4. Addition and Subtraction (whichever comes first).

You can use the acronym PPMDAS to remember how excel does its calculation and take care
when inputting formulas to avoid errors.
Parentheses solve from inside to outside following the order if it contains more than one
operator. Multiplication and division solve left to right, and you can interchange them because
the division is a form of multiplication, e.g., you can write X/Y as X * Y-1. Similarly, addition
and subtraction are also solved from left to right. You can also interchange them because
subtraction is a form of addition, i.e., X – Y can also be written as X + -Y.
In the case of the above example:
11-6+4, the solution will be 11-6, which is 5, then 5+4 =9.
If you mean to subtract the sum of 6 and 4 from 11, introduce bracket, i.e., 11-(6+4). Excel
will first solve the formula in the bracket and then subtract it from 11 to get 1.
Note that the above gives us two different answers. Therefore, it is crucial to understand how
excel calculates so as to input your formula for excel to understand.
5.3 Copying Formula with References
You can copy Formulas to other cells just like other cell content, text, and numbers. Excel has
an excellent feature of enabling you to copy your formula with cell references to reuse it in
another location with a different dataset.
You can reference your cell in different ways when working with formula:

Relative referencing
Absolute referencing
Mixed referencing
Range referencing

5.3.1 Relative References

Relative referencing is the excel cell default referencing style. Excel will relatively adjust the
cell references in the new location for the formula to do the same thing.
For example:
If you add cells A1 + A2 + A3 in cell A4(=A1+A2+A3), you can copy the formula to any cell,
say cell C4, i.e., select cell A4, copy it, and paste it into cell C4. Excel will copy the formula and
adjust it to =C1+C2+C3 in cell C4.
 The above type of referencing is called relative referencing. If you copied the cell’s formula,
excel would change the position based on the relative position of the destination rows and
columns.
Relative referencing is greatly useful whenever you want to use the same formula to perform
the same calculation across many rows and columns.
To use relative referencing to autofill:
1. Select and insert your formula into a cell
2. Press the enter key on your keyboard. The result will be displayed in the cell.
3. Select the result’s cell and drag down or up the fill handle to apply the formula
automatically to the other cells.

Example: Copying formula with relative reference

Using an Excel spreadsheet to find the total sales of the products, as shown below.

Solution

1. Copy the data as seen on your spreadsheet.

2. Input the formula under Total sales. You can select the cells that have the values
or write your cell references as shown below.
 3. Press the Enter key—the result shows.
4. Select the cell, and double click on the fill handle + as shown or drag down to the
end of the table.

Excel automatically fills the column with the same formula shifting the references relatively.

5.3.2 Absolute References

Absolute referencing is used when we need excel to stay locked unto a cell no matter where
we copy the formula. Absolute references are designated with a dollar sign ($) in front of
both the column and row designations, e.g., $A$2 will lock excel to the A2 cell no matter
where you copy the formula. You can use absolute referencing when you need excel not to
change the reference.
Example: Copying formula with relative and absolute referencing
From the example above, the unit price of all the products is the same. The unit price can be
put in a cell as shown below. Use an excel spreadsheet to find the total sales as the product of
Quantity and Unit price, fixing the unit price cell.
 Solution

1. Select the cell E2 or the first cell of the Total Sales column.
2. Input the formula =D2*$G$4 as shown below. D2 is where your first quantity
data is and G4 is where the Unit price is. Note that $ is added to G and 4 to fix the
cell, otherwise, excel will adjust the cell reference.

3. Press the Enter key and autofill down the column as done in the example above.
Excel shifts the cell D2 down relatively and G4 absolutely (i.e., fixed).

5.3.3 Mixed References

Mixed referencing, though not common, makes use of both relative and absolute referencing
part e.g.

When you use $A2, the reference stays fixed to column A, but the row number
can change.
When you use A$2, the reference stays fixed to row 2, but the column letter can
change.
 F4 is the reference shortcut key. To use the shortcut, highlight the desired cell name and press
F4 multiple times to cycle through all the variations of reference types: Absolute, row lock
(mixed), column lock (mixed), relative references…
5.3.4 Range References
The range is a group of cells in excel.

Use a comma to reference range when referring to several cells that are not next
to each other, e.g., =sum (A1, B2, E3, F5) is a formula (that contains function)
that sum cells A1, B2, E3, and F5.
Use a colon to reference range (cells) next to each other, i.e., in a contiguous area.
Designate the range from top-left to bottom-right, e.g., = sum (B4:C6) is a
formula that sums all cells in the resulting rectangle, i.e., cells B4 (top-left)
through C6 (bottom right).
 Chapter 6: Working With Functions

Functions are predefined or built-in formulas in excel. They are shortcuts for mathematical
operations. We always use functions in formulas, but not all formulas contain functions.
Functions can handle many of the formulas we defined, i.e., they have been predefined. For
example, you can use the SUM function instead of the addition sign to add plenty of numbers.
Functions also define complex calculations that are difficult to be manually solved just by using
operators.
Function names are designated with a single word. Although they are always in capital letters,
the case does not matter. A function name is always followed by parentheses containing
arguments separated with semicolon e.g.

A formula can contain multiple or nested functions. The nested function should be put in
parentheses, and the excel action follows the order of operation.
6.1 Function Library
Excel has hundreds of built-in functions, and the function library is being updated with every
version. You can find the function library in the Formula tab ribbon, and the library is grouped
to locate the function you need quickly. Lots of help and examples are available to understand
the proper usage of each function.
Excel gives an option of looking for and working with a function you don't even know exists.
To look for and use a new function in excel:

1. Go to the Formulas tab.

2. Click on Insert Function in the Function Library group, and the Insert
Function window pops up.
3. Describe what you want to do in the Search for a function field Or
4. Choose a category in the Select a category field. The list of all the functions in
the category will be available for you.

Excel gives a brief description of any selected function at the bottom of the function list.

5. Click on Help on this function to go online for more details on the function.
 6. Press ok to insert and work with the function.

You can use this method to work with any function in excel.
6.2 AutoSum.
Commonly used functions like sum, average, count, min, max, and shortcuts to all functions
are given a special command button called AutoSum. AutoSum is usually very good at figuring
out what you’re trying to sum up.
To use the AutoSum command:

1. Highlight the cells you want to add up with one extra blank cell.
2. Go to the Formulas tab in the Function Library group or the Home tab under
the Editing group.

or

3. Click on the AutoSum button to sum, or click the drop-down button beside it to
choose an option.
 The sums (average or count, etc.) will appear in the blank cells.
Example: Use AutoSum
Use the AutoSum command in the excel spreadsheet to find:

a. The sum across each (i) salespersons and (ii) product.

b. The average across each product.

Of the Data below.

Solution
Following the steps 1-4 above

a. The sum is as shown below

i
 ii

Note: When excel has a choice of moving in all directions in the AutoSum function, its
default movement is downward. Therefore, if you want to sum downward, you might not
highlight an empty cell as seen above.

b. The average is shown below.

6.3 SumIf Function
SumIf function adds values that satisfy a given condition. The function has three arguments:

Range: The set of data you want to check for the given condition.
Criteria: The condition to be fulfilled.
Sum range: The data set values to sum if the condition is met.

SumIf function is given as: SUMIF(range;criteria;[sum_range]}

Example: Use the SumIf function

From the given data below, write a formula to calculate the sum of total revenue made by a
particular salesperson in an excel spreadsheet.
Solution.

1. Input the data in excel.

2. Select a cell and input the formula using the SumIf function as shown below
6.4 Count, Counta, Countblank, and CountIf Functions
In a selected data set:

Count function counts cells with numbers.

Counta function counts cells that are not empty.
Countblank function counts empty cells.
Countif function counts cells that satisfy a given condition.

Count, Counta, and Countblank functions require only a range of values you want to count, while
the Countif function requires a range and criteria arguments.

Example: Use Count, Counta, Countblank, and CountIf Functions

In the given data set below, count:

a. The number of cells with numbers.

b. The number of cells that are not empty.
c. The number of empty cells.
d. The number of cells with a given name.

Solution.
Step 1: Select a cell you want your answer to be.
Step 2: write the formula starting with an equal sign.
Step 3: Highlight the required argument(s) and
Step 4: Press enter to get the answers as shown below.
1. The number of cells with numbers is 15:

2. The number of cells that are not empty is 20:

3. The number of empty cells is 4:

4. The number of cells with a given name:

Use =COUNTIF(range;criteria)
 The range is the Sales Person’s column, H8:H33 (this can change based on the position of
your data), and the criteria are fixed at the Sales Person field H37(this is also based on the
position you choose) as shown.
6.5 IF, IFAND, IFOR, and Nested IF Functions
An organization's marketing department wants to reward its salespersons that meet the
following criteria at the end of the year.

a. A salesperson with revenue greater than $60000 will be given a Gift and
promotion.
b. A salesperson with revenue greater than $35000 and less than $60000 will only be
given a promotion.
c. A salesperson with revenue greater than or equal to $60000 or a salesperson less
than or equal to $35000 should be noted.
d. A salesperson with revenue greater than $35000 and less than $60000 will only be
given a promotion; a salesperson with revenue greater than or equal to $60000
will be given a Gift and promotion.

Help the company to figure these out using an excel spreadsheet quickly.
Solution.
If function is given as: IF(logical_test; [value_if_true];[Value_if_false])
Copy the data and write the formula as shown below:

a. The logical test: is Revenue > $60000, if yes put Promotion and Gift, if no put
"” i.e., nothing. (the data are placed in cells to make it dynamic).

Input as shown below and press enter.

Double click on the fill handle to autofill down the column and get the result.

Note in the formulas:

$G$1 is the Price for the gift’s field (i.e., its value, $60000).
$G$2 is the price for the promotion’s field (i.e., its value, $35000).
 $G$3 is tagged Promotion and Gift.
$F$3 is tagged Gift.
The above references are used for easy manipulation and are the same for the remaining
examples. G6 is the revenues cell, and it changes in the example based on the position of the
data set.
b. IFAND function is used if more than one condition is satisfied, i.e.,
revenue>35000 and < 60000. In this case, our logical test in if function is
AND(Revenues>35000;Revenues<60000), if yes put Gift, if false put “”

Input as shown below to get the result.

 Note: In this case, G21 or G22 is the Revenues cell. You can choose any of the cells in the
column and autofill the remaining.
c. IFOR function is used if one of more than one conditions is/are to be satisfied i.e.
Revenue>=60000 or < =35000.

In this case, our logical test in if function is:

OR(Revenues>=60000;Revenues<=35000), if yes put Note if false put “”
Input as shown below to get the result.
 Note: In this case, G47 is the Revenues cell
d. Nested If function is used when you have multiple conditions to evaluate, and the
false argument also has conditions. Then, the false argument will be replaced by
another if function, as shown below.

If Revenues>35000 and < 60000, if true, put Gift, if false, check for another condition: If
Revenues>=60000, if true, put Promotion and Gift, if false, then put “Not entitled”
Input the formula as shown below to get the result.
6.6 Vlookup And Hlookup Functions
VLOOKUP function is used to look for a value in the left-most column of a table and returns
a corresponding value in the same table row. In contrast, the HLOOKUP function looks for a
value in the top row of a table or array of values and returns the value in the same column from
a row you specify.
VLOOKUP and HLOOKUP functions work similarly, except that VLOOKUP is for Vertical
lookup and HLOOKUP is for Horizontal lookup. Therefore, VLOOKUP is used for datasets with
a column header and HLOOKUP, a horizontal header.
VLOOKUP and HLOOKUP functions have four (4) arguments:

Lookup_value: The value you know and want to look up. It must be at the left-
most column or top row of the data set (very important).
table_array: your data set
col_index_num or row_index_num: the position of the value you want to
return, taking the first column or row as 1, followed by 2, etc.
The range_lookup can either be 'TRUE' or 'FALSE' to check for an approximate
match or exact match.

Example: Using Vlookup

Using the given data set below, set up a way of getting a staff name, department, and revenue
given their staff ID using a VLOOKUP function.

Solution:
We want to look up a staff ID and return values from the right.
 Step 1: Copy the table in excel as shown below:
Step 2: Select the cell you want to look up its value.
Step 3: Write out the function and fill in the function’s argument.
Step 4: Press enter to get the returned value as shown below.
Note in the formulas: E14 is the cell you want to look up its content, i.e., Staff ID, and
C4:F11 is the data set range. These references can change based on the position of your data.
 Changing the staff ID will return the staff name, department, and revenue from the example
above.
Example: Using Hlookup
Given the same data with the horizontal header, the VLOOKUP function cannot work in this
case. The solution is as follows, using the HLOOKUP function following the similar steps
above.
Solution.
 Note in the formulas: E8 is the cell you want to look up its content, i.e., Staff ID, and B3:J6
is the data set range. These references can change based on the position of your data.

From the example above, changing the staff ID will return the staff name, department, and
revenue.
6.7 XLOOKUP Function
This function is only available for office 2021, Office 365, and free online users. It has the
advantage of looking up data in all directions and returning all data for only one match. The
function has six arguments, and the last three are optional. The fourth argument returns False by
default.
Example: Using Xlookup
From the given datasets in the above example, using the Xlookup function, set up a way of;

a. getting a staff ID by his/her name.

b. getting a staff data by his/her ID.
c. returning ‘not a staff' when Staff ID does not match any data.

Solution.
Step 1: Select the cell you want to insert your returned data.
Step 2: Input the function and select the arguments as shown below.
a. Getting a staff ID by their name: B14 is the look-up value (i.e., a staff name), D4:D11 is
the lookup array (i.e., Staff Name column), and C4:C11 is the return array (i.e., the Staff ID
column). These references can change based on your data position.
 b. Getting a staff data by his/her ID. To do this, select the whole range you want to return, i.e.,
D4:F11 in this case.
c. Returning ‘not a staff' when Staff ID does not match any data.
 With just a formula, you can retrieve all the staff data, unlike Vlookup and Hookup functions
that require a formula for each of the data.
Note: Do not use the Xlookup function if you intend to share your file with those using excel
2019 and older versions because these versions of excel will generate #Name! error where the
function is used.
6.8 Filter Function
The filter function is also a new excel function available for office 2021, 365, and free online
users. The function is easy to use. It has the advantage of returning all matches in a data set. It
has three arguments:

Array: array or range to filter

Include: criteria, i.e., Boolean array
if_empty: values to return when no results are returned. This argument is
optional.

And the syntax is =FILTER (array, include, [if_empty])

Example: Applying Filter function
Assuming in the above example, you want to look up the data of those in either operation,
sales, and marketing department and return all matches; then you need the FILTER function.
Vlookup, Hlookup, and Xlookup return only a match, but the Filter function returns all matches.
 Solution.
Input your data, type in the formula, and select the arguments as illustrated below. Array
argument is the whole data range (I4:L11 in this case). Include argument is: Department range
equals a value (K4:K11=O3 in this case).
6.9 How To Use Any Function In Excel
1. Given that I wish to add some numbers and am not aware of the SUM function, following
the steps below helps me find and use the function.

2. If you know a function but don’t know how to use it, start typing the function, and excel
gives you a hint on the function and the arguments to put. Click Tab on your keyboard to insert
the function, and excel brings out the arguments to put. Look at the examples below.
a.
b.

Each of these arguments is a link to get more online help on the argument.
 Chapter 7: Data Analysis - Sort & Filter, Tables,
PivotTables, and Totals

Data analysis is the conversion of raw data into useful, easy-to-read, and understand data.
Excel has various features for data analysis which include:

Custom and conditional formatting

Sort and filter.
Tables
PivotTables
Totals
Charts
Sparklines

Below are the illustrations of how we can analyze data using these features.
7.1 Applying Conditional Formatting to an Entire Row
We use Conditional formatting to visualize rows of data that satisfies a set of rules. This helps
in the easy visualization of a dataset.
The illustration below gives a step-by-step guide on how to apply conditional formatting to an
entire row of data that satisfies a condition.
Example: Visualize data with conditional formatting
From the given data set below, use conditional formatting to highlight the entire rows that
have Sales greater than $6000 or some other values required later.
Solution

1. Select the dataset without the headers.

2. Go to the Home tab
3. Click on Conditional Formatting
4. Select 'New Rule...'
5. Select 'Use a formula' to determine which cells to format in the New
Formatting Rule window.
6. Input the formula = $I4>$K$5 as shown below, i.e., =(Select the first cell under
sales in the data) greater than sign ( select the cell you put the value to be
analyzed).
 Note: Excel automatically fixed the cells (i.e., $I$4 and $K$5). Make the row number relative
by removing the dollar sign in front of the number, i.e., $I4

7. Click on 'Format…' to format the row(s) that satisfies the condition. There, you
can format based on number, font, border, and fill.

8. Click on OK, and you can quickly analyze the data as shown below.
7.2 Sort and Filter.
Excel has a feature to Sort and Filter data for quick data analysis. Sort is used to rearrange data
alphabetically, in increasing order, in decreasing order, by color and icon as the case may be. The
filter is used to retrieve data that satisfies a given condition.
To Sort & Filter your data set:

1. Select a cell in your data set.

2. Go to the Home tab in the Editing group.
3. Click on Sort & Filter button.
4. Select an option from the list.

Clicking on any of the first 2 Sort options will automatically Sort your data. The Custom Sort
option opens up a window as shown below for customization.
 The filter option applies the filter drop-down button to your data headers. From the drop-
down button, you can quickly Sort and Filter your data from the available options.

The list of the available options in the selected column is given, and you can check or uncheck
the boxes to filter as desired.
The Sort and Filter command is also available in the Data tab, as shown below. The first and
second sort buttons sort data directly; the third button is for a custom sort, while the Filter
buttons apply filter drop-down buttons to your data headers.

You can also access Sort and Filter command by right-clicking your data and choosing the
buttons as desired.
 The sorted column will have a filter funnel or arrow down or up at the side.
To Clear a Filter:

1. Click on the Filter & Sort button in the Home tab.

2. Select Clear in the options. Or

1. Click on the filter icon in the column header.

2. Select Clear Filter From "Headers name" as the case may be.

Alternatively, you can use the keyboard shortcut Ctrl + Shift + L to add/remove the filter.
Note: There is no command button to un-sort your data except you undo your action. But you
can always give your data a serial number, which you can sort to get your original data anytime.
7.3 Working with Tables.
Although the Excel worksheet is in tabular form but working with tables in excel has some
additional features and functionality. The other features and functionality include:

Automatic Styling or formatting.

 Easy naming and organizing data
Ability to sort and filter data
Easy navigation
Auto expansion.
Total row features for easy analysis.
Ability to remove duplicate data
Ability to create a unique list
Easy calculations with Table formulas.
Pivot table integration.
Chart integration.

7.4 Creating a Table

You can either create a blank table and fill it up with data or create a table from existing data.
To create a blank Table:

1. Go to the insert tab

2. Click on the Table command.
3. Adjust the 'Where is the data for your table?' field by putting the range you
want your table to cover, and you will create an empty table.

To create a table from existing data:

1. Select any cell in the data range. You do not have to select the whole range of
data.
2. Go to the Insert tab.
3. Click on the Table command.
4. Check that the data range selected by the prompt dialog box is the desired one,
and if not, adjust it.
5. Check or uncheck the My table has a header checkbox depending on whether
you have a header or not. If you check the box, excel will take your first row as a
header, and if you don’t check it, excel creates a default header with the name
column 1, column 2, etc., for your data.
6. Click OK to create the table.

Once you create the table, you will notice that the table has been automatically styled or
formatted. You can make more adjustments and analyses to the table from its contextual Design
tab.
7.5 Exploring Table Properties
Table name: each table in your worksheet is assigned a unique name found in the properties
group of the contextual Design tab. The default name is Table1, Table2, etc. You can rename it
to a name appropriate to the data. This name is essential for structured table references.
Filter and Sort: The drop-down buttons in the table header row are the filter button. They are
automatically part of the table when created and are used to sort and filter data quickly. They
contain great features like recognizing date columns and grouping them automatically by day,
months, and years, sorting by oldest, newest, and recognizing numbers and text columns, and
putting the sort and filter as appropriate.

Exploring the filter buttons will give you a great idea of all the button's features for different
data types in columns.

Easy navigation: If your Table is long and you scroll down, the column header change to the
row header name. This feature gives easy navigation without having to freeze the header pane.
Auto Expansion: The Table automatically extends, i.e., spills, if new data is inputted or pasted
into a column or row next to the table. Typing 34 into the column below automatically adds the
new column.

You can also extend the table by dragging the side or by dragging down the blue icon at the
bottom right corner of the table
Ability to rearrange columns and rows with drag and drop. To do this:

1. Select the header of the column you want to move. For a row, select the row of the
table you want to move.
2. Move your cursor up to the edge of the cell until you see crossed arrows.
3. Left-click and hold the mouse and move the column or row to the desired location.
You will see a long vertical or thick horizontal bar indicating where you will place
the column.

7.6 Total Rows

You can turn this feature on in the contextual Design tab by checking the Total Row box as
shown below, and it will automatically add the summary row of the last column to the table. You
can check the cell of the total row for each column to put the available option as desired. If you
clicked on any cell of the total row, you would see a drop-down button with the list of all the
total row available options like average, count, maximum, minimum, Standard deviation, and
more. You can explore more options by selecting more functions to allow you to search for a
function or select a function category. The total row feature uses functions that update it
perfectly to the feasible data when the table is filtered.
7.7 Removing Duplicates
Excel has a built-in feature to remove repeated data. To remove duplicates from your table:

1. Select any cell in your table.

2. Check the Remove Duplicate box in the contextual design tap. There is a pop-up
dialog box with the list of all the columns' header names. They were all checked
to search for the set of complete row data that matched.
3. Click on the OK button, and excel brings out the number of duplicates.
4. Press the OK button to remove the duplicate row.

You can also use this feature to get a list of unique data in your table. To do this:

1. Follow steps 1 and 2 above.

2. Uncheck all the column headers except the one whose unique data you want.
3. Press OK, and the column will contain the list of unique data of the row.

You can copy and paste the list as desired.

7.8 Easy Calculations with Table formula (structured references)
You can use Table names and the column headers in formulas instead of normal cell
references for easy calculations and references. These are called structured references, and you
can use them in or outside the table or worksheet. The table name is not put if we use structured
referencing inside the table. Structured reference is written in the formula as:
=Table name[column name] or =[@column name] if used inside the table
Excel automatically uses the table structured referencing when any cell in the table is selected
in formulas and fills the formula applied down the column.
Example: Table Formula (Structured references)
From the given table below, calculate:

i. The Revenue as the product of Quantity and Unit Price.

ii. The Total Quantity and Total sales in the sales summary.
 Solution.
i. Step 1: Copy the table and input the formula in the first revenue column as illustrated below.
Fix the unit price in the cell by putting $ in front of both row number and column letter.

Note: Excel uses structured referencing when you highlight the Quantity as [@Quantity]
instead of the usual referencing P4.
Step 2: Press enter, and excel automatically fills the whole column.
 ii. Step 1: Start typing the formula, and excel brings out the list of all the tables in your
workbook. (It is a best practice to name your table with Table, followed by a word that describes
the table for easy referencing.)

Step 2: Scroll down to TableStaff and press Tap to enter. Immediately, excel brings out the
list of all the columns in the table and some analysis features, as seen below.

Step 3: Select the Quantity column (or any column you want to work with), press Tap to
enter, and input the close brackets. Press enter to get the value.

Step 4: Repeat the steps above in the total sales field. Select the Revenues column, press
enter, and get the answer as shown below.

Note: You can perfectly use the table structured reference in another worksheet of the same
workbook.
7.9 Pivot Tables
Pivot table is used for quick data analysis and summary in excel. It helps to find relations in
data and make calculations with one or more criteria. To use a pivot table that can update
automatically with the insertion of new data:

Your data must be in tabular form.

Each column must have a header.
There should not be an empty column or row in the middle.
There should not be merged cells.
There should not be a total/subtotal row in the middle rows of the selected range.

To insert a pivot table:

1. Convert your range of data to a table if not already a table. We do this to avoid
changing the range each time we add new data.
2. Select a cell in the table.
3. Go to the contextual Design tap or Insert tap.
4. Click the Summarize with PivotTable command or PivotTable in the Insert
tap. You will notice that Table/Range has already set the name of the table
5. Read and select the radio buttons in the Create PivotTable window as desired.
6. Click OK to create the PivotTable.
 To the left of the new sheet is a Blank PivotTable, and to the right, the PivotTable Fields
containing the list of your table headers and the field areas that define the pivot table layout
below it. We can change the PivotTable Field layout from the drop-down button beside it. For
example, if you want more space for your field list (headers), you can select the option that puts
the list and area layout side-by-side.
We can control what is and what is not in the PivotTable from the list of all the column
headers in the PivotTable's fields.
To put a field in your Pivot table:

Either check the header box or

Drag and drop the header to the desired position. There are four available areas
(boxes) to put in the header: FILTERS, ROWS, COLUMNS, and VALUES.

The PivotTable layout is based on the analysis or report you are trying to make or questions
you’re trying to answer. Numerical data works well in the value field, and you can experiment
with rows and columns to know the best. Excel automatically fills in the header in the default
box when you check the header check box in the Pivot Table Field. You can change a header
from column to row and vice versa.
To remove a field in your Pivot Table:

Either uncheck the field in the headers list or

Drag and Drop the field from the layout box to the worksheet space or
Left-click on the field from the dialog box and select Remove Field from the
pop-up window.
 Also available in the window that pops up when you left-click are the options to move the
field and Field Settings… option. You can move your field as desired from the move options.
For more analysis like average, percentage, minimum, maximum, etc., other than Grand total,
go to the Field Settings… You can also get to the Field Settings by right-clicking on the data in
the PivotTable or clicking on the drop-down button beside the value field in the layout area and
choosing to Summarize Values By or Value Field Settings option.

You can explore more options in the Show Values As option.

A drop-down button beside the header of each field gives more options or setting on the field.
Recommended PivotTable command in the Insert tab is to insert PivotTable quickly.
Clicking on this command will open up a window with the list of recommended PivotTables.
There are high chances that your desired PivotTable will be on the list. Click on the desired one,
and you have your pivot table created without stress. You can as well adjust the recommended
pivot table as desired.
Example: Work with PivotTable
From the given data set below, use the Pivot table to analyze the following:

1. The amount sold by each salesperson

2. The total sales by-products and the products that generate the most sales
3. The salesperson that accounts for the highest percentage of total sales

Solution.

1. Using Recommended PivotTables, choose the pivotable that relates

salespersons and the sum of sales as illustrated below. Press Ok, and you have
 the amount sold by each salesperson.

2. To create the PivotTable from blank, follow the illustration below.

 3. Check the sales and product name in the PivotTables field to get the total sales
by-products

4. Sort the PivotTable to get the product that generates the most sales.

White T-Shirt generates the highest sales.

5. Follow the first example below to get the amount sold by each salesperson.
Right-click any of the values and select Show Values As. Then select the % of
Grand Total to get the solution as illustrated below.
 Elizabeth Charles accounts for the highest percentage of the total sales.
Note: You can click on any of the values in the PivotTable to get the breakdown. You can also
add more than two headings to your Pivot Table.
 Chapter 8: Charts and Quick Data Analysis

8.1 Working with Charts

Charts are for the visual analysis of data. They are the graphical representation of data with
symbols. There are different types of charts based on what you use to represent the data, and
some examples are as follows:
The column chart illustrates data with vertical bars.
The bar chart represents data with horizontal bars.
A line chart represents data with lines, and you can use it for trend analysis.
The pie chart illustrates data with slices of circles.
The area chart represents data with the area under a graph.
There are many more types of charts built in excel that you can explore for data analysis as
desired and appropriate.
8.2 Inserting a chart
To insert a chart:

1. Highlight the range or table whose chart you want to create.

2. Go to the Insert tab command,
3. Select your desired chart in the Chart group option.
4. Choose from the Recommended Charts if you don’t know what to choose.
5. Click on the OK button to insert your chart.

Excel Chart has two contextual taps; a Design and a Format tab. From the tabs, you can set
up, design, format, and adjust your chart.
Example: Insert a Chart into a Dataset
Follow the steps above to insert a chart as illustrated below.
8.3 Setting Up a Blank Chart with Dataset
Excel creates a blank chart canvas if you insert a chart without selecting any data, and the
Chart area can be configured or set up with data to get the chart, following the steps below:

1. Select the chart plot

2. Click on the contextual Design tab or right-click on the plot area.
3. Click on the Select Data command or choose it from the list that comes up when
you right-click.
4. Click on Add to add the chart elements manually.
5. Select the Series name field and input the title manually or Select the title of your
data set.
6. Select the Series value field and highlights your data number set. Press OK.
7. Click on Edit in the Select Data Source window and highlight your label to add
 it.
8. Click on the OK button and insert the chart.

From the dialog box, you can add, remove and edit your series.
Example: Insert a Blank Chart and Set it up with Data

If you click Add:

8.4 Set your Default Chart
Ctrl + F1 is the keyboard shortcut to insert a chart for a selected data set. Using this will insert
the default chart type. To change your default chart type:

1. Go to the Insert tap.

2. Click on the expandable dialog box button at the chart group.
3. Choose your preferred chart type in the Insert Chart window.
4. Right-click on it and click on Set as Default Chart.
 So, any time you use the keyboard shortcuts command, you will insert your default chart type.
Chart elements like title, axis, labels, legend, etc., can be adjusted or set up as desired or
required.
8.5 Adding Series to a Chart
To add a Series to a Chart.

1. Select the chart area.

2. Go to contextual Chart Tool Design tap.
3. Click on the Select Data command in the Data group.
4. Select Add and follow the steps on how to insert a chart above.

Example: Add Series to a chart.

Add another series to the chart in the exercise above, following the steps above as illustrated
below.
 If the data series you want to add is next to the previous dataset, you can click on the chart
area to select the dataset and drag it to the new data set when you see a slant double-arrow
cursor. Doing this adds the new data set to the previous chart. The new chart will be of the same
type as the previous one.

8.6 Creating a Combination or Composite Charts

A combination or composite chart is a chart with more than one chart type in a single chart.
To create a combination chart:

1. Right-click on the series chart.

2. Select 'Change Series Chart Type…', and the Change Chart Type window pops
up.
3. Select the drop-down button in front of the series you want and click on the
desired chart type.
Example: Create a Combination Chart of Different Chart Types
Change the Chart type of the second series of the above combination chart

Right-click on the chart area without selecting any series to get an option to change the entire
series chart type, i.e., Change Chart Type option. You can click on Combo to go to where you
can change a series chart type.
Note the plot area and the chart area as indicated below. To move the chart, click on the side
of the chart area and drag it to the desired place. Clicking the side of the plot area will only move
the plot.
 Note: If you use excel 2010 and oldest, you have to select your chart type before creating the
chart because you will not be able to change it after you build it.
8.7 Formatting a Chart
To adjust or format a Chart:

1. Select the chart.

2. Click on the plus sign at the right top corner.
3. Check or uncheck the box of any elements to put or remove them from the chart.
4. Click on the arrow in front of any selected elements for more settings.
5. Click on More Options… to have control of the element from the element
Format dialog box.

Each element has its own different Format dialog box where you can fully format your chart
elements. You can change their color, width, size, gap, etc., as the case may be. The format
dialog box can also be opened by:
 double-clicking on the element or
Right-clicking on the element and choosing Format Data Series from the list of
the options or
Using the keyboard shortcut command Ctrl + 1 on the element.

You can also format your chart in the contextual Chart Tools tap (Design and Format
buttons) or at the brush icon below the + sign.
Example: Format a chart
Format the combination chart in example 6.7 above by changing the title to Sales to Target,
adding Legend to the bottom, and adding the axis title.
Solution:
Follow the steps above to format as illustrated below.

1. Check the Axis Titles, Chart Title, and Legend (Bottom) boxes.
2. Double click on the elements in the chart area to edit.
3. Adjust the font in the Home tap.

8.8 Quick Analysis.

Excel has a unique feature that brings about all the discussed analysis tools with just a click.
This feature is called Quick Analysis.
To quickly analyze your data:

1. Select the data you want to analyze.

2. Move your cursor to the bottom right of your selected data and click on the Quick
Analysis button .
3. Select from the listed options.
 You can preview your options by moving your cursor over each to select a perfect tool.
Example: Analyze your Data Quickly
Analyze the data set above by quickly finding its Total sales and Average Sales.
Following the steps above, the total and average sales are illustrated below.
 Chapter 9: Workbook Editing and Management

9.1 Find and Replace

While working with a workbook with a lot of data, finding some desired data can be a very
stressful and time-consuming task. Find is an excel feature that can help you search your
workbook and quickly find whatever data you are looking for. Excel also provides an option to
find data and replace them with desired data using the Replace command.
To Find a data:

1. Go to the Home tab.

2. Click Find & Select command in the Editing group.
3. Select Find… from the drop-down menu.

Find and Replace dialog box appears. You can alternatively use Ctrl + F to open this
dialog box.

4. Enter the data you want to find in the Find what textbox.
5. Click Options<< to streamline your search (optional).

Options<< button allows you to search within the worksheet or workbook, by rows or
columns, by formulas, values, or comments. You can also search for the data that
match the case or the entire cell contents.

6. Click any of the following;

- Find Next to find the data starting from the current cursor position to the end of the
workbook. The first cell that matches the data will be selected; continue to click the
Find Next button to move to the next occurrence cell one after the other until the end
of the workbook.
- Find All to see the list of all the data in the workbook.

7. Click the Close button when you are done.

 To find and Replace data:

1. Go to the Home ribbon.

2. Click Find & Select command in the Editing group.
3. Select Replace… from the drop-down menu.

Find and Replace dialog box appears.

4. Enter the data you want to find and replace in the Find what field.
5. Enter the new data you want to put in the Replace with field.
6. Click any of the following;

- Replace to replace the data starting from the current cursor position to the end of the
workbook. The first cell that matches the data will be selected and replaced. Continue
to click Replace button to move to the next data occurrence one after the other until
the end of the workbook.
- Replace All to replace every instance of the data in the workbook or worksheet.

7. Click the Close button when you are done.

9.2 Commenting on a Cell

Commenting is very important to clearly describe your work to others or even to you. Excel
allows you to add, modify and format comments easily.
To add a comment to your data:

1. Right-click on the cell you want to add the comment.

2. Select the Insert Comment option.
3. Enter your comment.
4. Click out of the comment box.

You can identify the cell with a comment can by a red mark at the top right corner of the cell
. Moving your cursor around the cell with the comment will display the comment.
To edit, delete, hide/unhide comments:

1. Select the cell that has the comment.

2. Right-click on it and choose Edit comment or Delete comment or
Hide/unhide comment depending on what you want to do.

You can adjust the side of the comment box by clicking any of the small squares around it and
dragging it to accommodate the comment as necessary.
Note: Your comment can be seen and edited by anybody who has access to your work.
9.3 Checking Spelling
To check for spelling errors in your workbook:

1. Go to the Review tab.

2. Select the Spelling button in the Proofing group.

A Spelling navigation pane appears, highlighting the first error in your workbook with a
list of suggestions and action buttons.

3. Select an option.

- Select any suggested word and click Change or Change all button to accept it.
- Click Ignore Once or Ignore All if you think the word is correct or want the word the
way it is.
- Select Add to add the word to your Excel dictionary.
The Spelling pane brings the second error in your workbook.

4. Repeat step 3 as you move through the whole workbook.

5. To close the Spelling navigation pane, click on the X button at the right-top corner of
the pane.

You can turn the feature on or off in the Excel Options in the Proofing tab.
9.4 Thesaurus
Thesaurus helps you find a word that is similar to your chosen word. It suggests different ways
of saying what you want to say.
To use Thesaurus:

1. Select the word you want its synonyms.

2. Go to the Review tab.
3. Click on Thesaurus. A Thesaurus navigation pane appears on the right side,
containing the list of all the synonyms.
4. Move your cursor to a word that appeals to you.
 You can scroll up and down to see the complete list.

5. Click on the dropdown arrow on the right side of the word.

A menu appears.

6. Select Insert to replace your word with the word or

Select Copy to save it on the clipboard for later use.

9.5 Protecting your Work with Excel Security Features
You can protect your sensitive work from plagiarism, stealing, indeliberate editing, and many
security threats. Excel has three levels of security that you can use as needed:
1. File level security: This allows you to lock down your excel file with a password to
prevent others from opening the file. You can also protect your file from stealing,
accidental editing, and some other security features as follows:

Always Open Read-Only: This feature prevents your workbook from accidental
editing by always opening it as read-only. Each time you want to open it, a dialog
box appears, notifying you that you are opening the workbook as read-only. Press
Yes to continue and No if there is a need to make changes.
Encrypt with Password: Adding a password to your workbook is a strong form
of protection, and you can give the password to only those you want to have
access to your workbook. Nobody will be able to open your workbook without
the password, not to talk of editing. Selecting this option, Excel asks you to enter
a password and re-enter it for confirmation.
Restrict Access: This gives people access to your workbook but restricts them
from copying, editing, sharing, or printing it. You will have to connect to the
Information Right Management (IRM) server to help you secure the workbook.
Selecting this option will prompt you to connect and lead you through the
process.
Add a Digital Signature: Protecting your workbook with a digital signature has
 several benefits, like maintaining proof of workbook integrity, signer identity, etc.
You must purchase a digital signature from a verified Microsoft partner to use it.
Selecting this option for the first time will prompt you to where you can get one.
Mark as Final: This makes your workbook read-only (i.e., typing, editing, and
proofing capabilities disabled) with a message at the top of the workbook screen
informing the reader that the workbook is final. However, any reader can still edit
and resave the workbook by clicking the Edit Anyway button in the top message.
Select this security feature only if you just need to notify the reader that it is the
recommended final version of your workbook or to discourage editing.

2. Workbook level security: This allows you to lock the structure of your workbook to
prevent others from adding, deleting, hiding, moving, and renaming the worksheets.
You will be prompted to provide a password to lock the workbook’s structure if you
click the Protect Workbook (Protect Workbook Structure) in the Review ribbon.
Locking with a password is optional. Anybody could unlock the workbook by
clicking on the same command if you did not set the password.

3. Worksheet level security: This allows you to control what a user can do within the
worksheet. You can see the list of the available options by clicking the Protect Sheet
(or Protect Current Sheet) command in the Review ribbon.
To secure your excel file, workbook, or worksheet:

1. Go to the Excel Backstage by clicking the File tab.

2. Click the Info tab in the left side pane.

Info pane appears on the right side.

3. Click the Protect Workbook button.

A dialog box appears.

4. Select an option from the list.

5. Follow all the prompts based on your choice and press ok.
6. Close your workbook for the security setting to take effect.
Warning: You will not be able to recover your file or unlock your worksheet or workbook if
you forget your password.
9.6 Saving Your Workbook
When you create a workbook, it will have a default name, Book 1, 2, 3, etc. To save your
work for the first time:

1. Click on the File tab to go to the backstage of excel.

2. Click on Save As option from the left-hand panel.
 3. Click from the right-hand pane where you want to save your file. Sign in to your
OneDrive account to save a copy of your work online.
4. Type the title of your workbook in the file name.

5. Click Save.
6. And excel saves your work with your name.

The default format in which the excel file will save is .xlsx. You can choose different options
in the file type bar.
To save your work subsequently,

Click on the save icon in the quick access toolbar or

Save in the excel backstage or
use the shortcut key Ctrl + s.

Note: clicking the save icon in the quick access toolbar or Save in the excel backstage or
shortcut ctrl + s for the first time will all initiate the Save as command.
Your file can also be duplicated with the same or different name and same or different location
by selecting the Save As option in the excel backstage and resaving it as desired.
9.7 Exporting your Workbook
Excel files are saved in the .xlsx file type by default. Exporting means converting your file to a
different format other than the default file type. Exporting your workbook can be helpful when
you need to share your file with someone who does not have an excel application or has an older
version of excel.
To export your worksheet as a pdf file:

1. Click the File tab to go to the Backstage of excel.

2. Click Export at the left vertical menu bar.

3. Select Create PDF/XPS command in the right-side ribbon;

The Save As dialog box appears.

4. Select the location you want to export your workbook.

5. Input your file name.
6. Click the Publish button.

Note: Excel exports only the active worksheet by default, even when your workbook has
multiple worksheets.
To export your entire workbook;

Follow steps 1-3 above.

Click Options in the Publish as PDF or XPS dialog box that appears.
Check the Entire workbook button in the Options dialog box. You can also do
any desired settings.

Press Ok.

To export a workbook to other file types:

1. Click the File tab to go to the Backstage of excel.

2. Click Export at the left vertical menu bar.
3. Select your desired option from the Change File Type box.

4. Click Save as command.

 The Save as dialog box appears.

5. Select the location where you want to export your workbook.

6. Enter a file name. You can also choose various file types in the Save as type
field.
7. Click Save.

9.8 Sharing your Workbook for Collaboration

You can share your workbook by saving it on OneDrive for collaboration. Sharing your
notebook by email for collaboration will create different file versions. It is advisable to save your
work online (i.e., on OneDrive) and share the link with your team for real-life editing. Real-life, I
said? Yes. With your file on OneDrive, there will be only one version, and any changes anybody
you share the link with make will take effect instantly.
To share your workbook:
1. Go to the Backstage view by clicking the File tab.
2. Ensure you save your workbook on OneDrive (or save your workbook on
OneDrive)
3. Click Share in the left vertical bar. Excel returns to normal view and opens the
Share pane on the right side of the window.
4. Type the email address of the person you want to invite to share your document.
5. Click the Share button.

From the share pane, you can set whether a person can edit or only view your workbook. You
can also see the list of who has access to the document.
9.9 Page Setups For Printing
Setting up an excel workbook for printing is essential to get our work printed out in a suitable
and desired format. To set up a workbook for printing:

1. Click on the File tab to go to the backstage view.

2. Click on the Print tab at the left-hand green bar backstage.
3. Select and adjust the print settings and preview as appropriate and desired.
 4. Click on Print.

Or, for more control of the page settings:

1. Go to the Page Layout tab.

2. Click on the expandable dialog box arrow at the right bottom of the Page Setup
group to open the Page Setup window.
3. Click on the Sheet option.
 From the Sheet option, there are various options such as print area, print idle, grid line, black
and white, draft quality, row and column headings, and more available for your page setting.
You can use the print area to set a range of regions selected for printing, and this area will be
the active sheet in the sheet option. To set a print area in your worksheet, select the area and click
on the Print area command in the Page Layout tab.
You can use the Print idle to set the row and column at the top and toward the left,
respectively.
The grid line option helps to put or remove the gridline in the printout as desired.
Black and White: Choose this option to print your worksheet in mono color.
Draft quality: this prints the worksheet using the printer draft quality.
Row and column headings: This option allows you to print the sheet row and column
headings.
Page Order option allows you to choose the order you would like to print the sheet.
Margins: This is the white space region at the top, right, left, and bottom sides of the printed
area of the worksheet. All excel sheet pages have margin by default, but you can change the
default to some predefined margin or customize your own. Any margin you set will apply to all
the sheets in the workbook. To customize your margin:

1. Go to the Page Layout tab.

2. Click on the Margin command
3. Choose customize option.
4. Input your values and enter.

Page Orientation: This allows you to print the sheet either in portrait or landscape. Portrait
orientation is the default and prints the page taller than wide, while landscape orientation prints
the sheet wider than tall. You will base your choice on the data in your worksheet. You can
always preview your sheet to see how good it looks.
Header and Footer: They provide some information at the top and bottom of the sheet,
respectively. By default, a new worksheet does not have a header and footer. To add header and
footer:

1. Go to the Page Layout tab.

2. Click on the header and footer option.
3. Choose from several options available to customize the headers and footers
from the pane.
4. Click on the Print Preview option to preview the changes you made to the
header and footer you have added.

Page Break: this command allows you to perfectly control the print of a page, such as printing
the header of a new page and the end of the previous page or restrain from printing the first row
of a table and the end of a page. Page break also allows you to print sheets in the order of your
preference. You can add both horizontal and vertical page breaks to your worksheet. To add a
page break to your worksheet:

1. Select the row or column you intend to add the page break.
2. Go to the Page Layout tab.
3. Click on the Page Break command in the Page Setup group.
4. Choose from the Page Break available options.

A vertical or horizontal line will appear on your worksheet, indicating the page break.
Freezing Pane: this is an essential excel feature that enables you to see the row and column
headings even when you scroll down the page. It fixed the selected header(s) in position. To
freeze a pane:

1. Select the row(s) or column(s) you want to freeze.

2. Click on the View tab.
3. Choose the freeze first column option.

The frozen pane remains on the page even when others move up or down. This option helps
print a long list of data with a heading on several pages with the header showing on each.
9.10 Closing Your Workbook
To close your workbook after you are done:

Click the X button at the top-right corner of the Excel window, Or

Go to the File tab and select the Close option in the left-side pane, Or

Use the shortcuts keys, Ctrl + F4 or Ctrl + W.

Microsoft Excel closes or notifies you if you try to close your workbook without saving it.
9.11 Recovering Unsaved Workbook
It can happen that you mistakenly close your Workbook without saving your last changes; the
good news is that Excel has an autosave feature that allows you to recover your file with the last
unsaved changes.
To recover your unsaved workbook:
 1. Go to the backstage view by clicking on the File tab.
2. Click the Open tab.

Open pane appears.

3. Click the Recover Unsaved Workbooks button at the bottom of the recently opened
workbook list.

The location dialog box appears with the list of unsaved workbooks.

4. Select the likely workbooks. You can check the date to know the likely one.
5. Click the Open button.

The workbook opens.

6. Save the workbook accordingly.

Alternatively,

1. Go to the backstage view by clicking on the File tab.

2. Click the Info tab.

Info pane opens.

3. Select the Manage Workbook drop-down.

4. Click the Recover Unsaved Workbooks menu that appears.
 The location dialog box appears with the list of unsaved workbooks.

5. Follow steps 4-6 above.

9.12 Opening Saved Workbook

You can open your workbook from the excel application or directly from your device.
To open an existing workbook from excel:

1. Go to the backstage view by clicking on the File tab.

2. Click the Open tab.

Open pane appears.

3. Select the location of your workbook.

An Open dialog box appears.

4. Select the folder or your excel file. You can scroll down the left side list of locations
on your device to locate your workbook.
5. Click Open.
Alternatively, if you recently opened your workbook or pinned it to excel, it will be available in
the Recent or Pinned list in the backstage Home panel, and you can click on it to open it.
If you often use or work on your workbook, it will be better to pin it in the workbook.
To pin your workbook to Excel:

1. Locate the workbook in the recent list.

2. Move your cursor over the workbook.
3. Click the pin icon in front of the file.
To open an existing workbook from your device:

1. Ensure you have an excel application installed on your computer.

2. Locate your excel workbook on your device.
3. Double-click on it, and it opens.
Chapter 10: 100+ Excel Shortcuts