Contents

© Michael Olafusi 2014

Preface
Microsoft Excel: It’s more powerful and easier to…
How Excel Handles What You Type
Data Consistency, starting with the end in view
Building Datasheets that can easily scale
Sorting
Filtering
Data Cleaning
Data Formatting
Charts
PivotTable and PivotChart
Business Data Analysis
Power Excel Formulas
Named Range, Goal Seek and Scenario Manager
Introduction To Excel VBA (macros)
 Copyright © 2015 Michael Olafusi

Published by
www.urbizedge.com

All rights reserved. No part of this publication may be reproduced, distributed, or

transmitted in any form or by any means, including photocopying, recording, or other
electronic or mechanical methods, without the prior written permission of the publisher,
except in the case of brief quotations embodied in critical reviews and certain other
noncommercial uses permitted by copyright law.

eBook written and formatted by mike@urbizedge.com

Preface
Microsoft Excel is the world’s most used business intelligence tool. Its knowledge is even
compulsory for an MBA degree and the business world depends greatly on it.

This book is aimed at making you very good in Microsoft Excel for business data analysis,
teaching you with companion videos and practice files that can be access at
www.urbizedge.com/about (bottom of the page). It’s intended for Sales Managers,
Financial Analysts, Business Analysts, Data Analysts, MIS Analysts, HR Executives and
frequent Excel users.

It is written by Michael Olafusi a two time Microsoft Excel MVP (most valuable
professional) and a full-time Microsoft Excel consultant. He is the founder of UrBizEdge,
a business data analysis and Microsoft Excel consulting firm. He has trained hundreds of
business professionals on Microsoft Excel and has used the experience gained from
interacting with them both during such trainings and while consulting for companies to
write this excellent guide for the busy professional who needs the improved work
productivity Microsoft Excel provides.

If you feel any part of this book can be better improved or expanded, please send an email
to mike@urbizedge.com
Microsoft Excel: It’s more powerful and
easier to use than you think!

We’ve got Home menu, Insert menu, Page Layout menu, Formulas menu, Data menu,
Review menu and View menu. Each of these menus will be discussed in practical terms.

The other menus — Developer menu, Load Test Menu and Power Query menu — are not
displayed by default. I enabled them as I do a lot of programming and other advanced stuff
in Excel which I need them for.

Home Menu
The home menu is Excel’s most used menu. It has very straightforward sub-menus.

Clipboard: Allows you to copy, cut and paste in Excel

Font: Allows you to set font size, color, background color (fill) & turn on bold or italics
or underline.
Alignment: Allows you to set the position of whatever you’ve typed (or copied) into
Excel. It also allows you to set how it’s written: horizontal, vertical or slanting.
Number: Allows you to set how a number is shown in Excel: regular number, currency,
scientific, percentage, fraction…
Styles: Allows you to set the format of an Excel cell based on the data it holds
(conditional formatting). It also allows you to convert a selection of cells to table, and to
set quick formats for a cell.
Cells: Allows you to insert new cells, delete cells and change cell format.
Editing: It houses the very useful Sort and filter tools. And also Find & Select, Find &
Replace. There’s also AutoSum which helps you sum all numbers in a selection.

Insert Menu

The Insert menu houses some of Excel’s best tools.

Tables: Allows you to insert PivotTable, PivotChart and Table. Inserting a table in Excel
allows for quick formatting, and better formulas (via named ranges). PivotTable and
PivotChart will be discussed later.
Illustrations: Allows you to insert images and shapes.
Charts: Allows you to insert charts, which will be specially discussed later.
Tours: Houses Map which takes you straight to Power Map. Power Map is part of
Microsoft’s new Power BI. It enables you make geo-maps and create amazing data
visualization tours.
Reports: Lets you access Power View, another Power BI tool. Power View allows to
create a data model, loading up many different databases and creating analysis that cuts
across all the databases, allowing you to see insights that are beyond a single database.
Sparklines: Allows you to insert charts that fit into one Excel cell. They make some
reports beautiful and easy to read.
Filter: Allows you to filter out field values you are not interested in.
Links: Allows you point a cell content to a website or an email address.
Text: Allows you to insert texts and objects (pretty much anything, including a PDF
document)
Symbols: Allows you to type out equations and special symbols.

Page Layout Menu

The Page Layout menu does just that: setting up your Excel document’s page look and for
printing.

Themes: It’s not often used; it sets the look of the Excel window itself.
Page Setup: It allows you to set how the page comes out when printed. Most used are the
Orientation (to set as Portrait or Landscape) and Print Area (to select on the cells you want
to print).
Scale to Fit: It allows you to set how much is printed per page. Most frequent use is to
force Excel to print on one page, or fit all the fields (columns) on one page width.
Sheet Options: You wouldn’t want to change the default. It allows you set whether Excel
gridlines be printed or not, and headings too. Default is no/off (unticked).
Arrange: It lets you rearrange overlapping objects (shapes, images, textboxes…). Or
align them.

Formulas Menu

The Formulas menu gives you access to Excel’s built-in formulas.

Function Library: It has the formulas grouped by category. Once you have an idea of
what you want done, it helps you locate the formula to use. It’s good to look through it
once in a while to have an idea of the out-of-the-box analysis Excel can do.
Defined Names: Lets you name a cell or selection of cells. Can be very useful when
analyzing a big database or building a model.
Formula Auditing: Allows you to check for errors in your formulas, trace formula cells
and see how your final result is being calculated.
Calculation: Allows you to set when the formulas in your Excel sheet are calculated:
automatic (whenever a cell value changes) or manual (at first entry and when you force
them to be recalculated).

Data Menu

The Data menu allows you to work with external data and do basic data formatting.

Get External Data: It allows you to import or link to an external data file (non-Excel
file). You’ll use it whenever you have a data in text file and need it worked on in Excel.
Connections: Allows you to make changes to the connections/links to an external data
file. Or force a refresh of the connections to capture changes made in the external data file
since last connection.
Sort & Filter: Allows you to sort data and do some filtering too. Filter allows you to
specify values to display.
Data Tools: Allows you do very basic data analysis. Especially removing duplicate
entries, and splitting one field into several (text-to-columns). Example is splitting full
name into first name and last name.
Outline: Allows you to group (and hide) several rows. Useful for large data reports with
few categories; helps to group categories.
Analysis: This is only visible after you enable Data Analysis add-in or Solver add-in. It
allows you access a large collection of statistical analysis tools and modelling.

Review Menu

The Review menu is for spell checks, commenting and setting access restrictions.

Proofing: Allows you to carry out spell checks and word meaning checks.
Language: Allows you to translate the Excel file content from one language to another.
Comments: Allows you to include comments in an Excel sheet, view all comments at
once or delete comments.
Changes: Allows you to set access restrictions and track changes to the Excel file. Also
allows you to share the file.

View Menu

The View menu allows you to change the window layout of the Excel document. It doesn’t
change anything in the actual document, just the way it’s displayed.
Workbook views: Allows you to set how the workbook (Excel file) is displayed.
Show: Controls what non-printing details are shown: Gridlines, Headings, Formula bar
and Ruler. The one you’ll be interested most in is Gridlines. If you want your Excel sheet
to look more like a Word file, untick the Gridlines. That’s what’s done to every Excel
sheet you see that has no Gridlines.
Zoom: Does what it says: sets zoom.
Window: Allows you to freeze headers so when you scroll they will never be out of view.
And also allows you to split the Excel sheet display, so you can compare two different
parts of the sheet.
Macros: Allows you to see the macros programmed in the Excel file (if there’s any macro
in it). Shows only when the developer menu is enabled.
 How Excel Handles What You Type

In Excel, you type into small rectangular boxes called cells. I would be referring to
everything you type or copy into Excel cells as Data.

Every cell has an address, because each cell is an intersection of a row and a column. The
cell selected in the image below, is addressed as cell A1. It is the intersection of column A
and row 1. A collection of millions of these cells make an Excel sheet. And an Excel file
(also referred to as Excel workbook) is a collection of one or more Excel sheets.

Sometimes, what you type into a cell takes more space than the cell has. Don’t worry, just
expand the column width by dragging the right border of the column header.

Like this:
Different Data Types in Excel

Excel recognizes 4 different data types: Text, Number, Boolean & Formula. Anything you
type into Excel will fall under one of these.

Text: Whenever you type alphabets, or a mix of alphabets and numbers into Excel
(without proceeding with =), everything is recognized as text. By default, Excel aligns text
to the left of the cell.
Number: If all you type into a cell are digits, they are recognized as Number by Excel. By
default, Excel aligns number to the right.

Boolean: FALSE and TRUE are Boolean entries. You’ll hardly use them. They are used
for setting up complex formulas. By default, whenever you type false or true in a cell,
Excel will put it in upper case and align it to the center.

Formula: Once you begin a cell entry with =, Excel treats everything you type after as a
formula.
As a recap, see the image below.

The practical importance of this is that if you receive a sales report in Excel and the
numbers are aligned to left instead of right you should be concerned. There are three
reasons this can happen and two of those reasons will cause some of your mathematical
formulas to not work correctly. You wouldn’t want to do an incorrect analysis, so it’s best
to check why the numbers are aligned to the left and not to the right as expected.
1. It could be that the author forced the numbers to align to the left. To find out if
that’s the reason, check the alignment under Home menu.

2. It could be that the author forced the number to be treated as text by setting the
cell format to text

3. It could be that the author preceded the number with a single apostrophe (’) before
typing the number. This is a trick savvy users use to force Excel to keep the zeros at
the beginning of your phone number or bank account number. Unfortunately, it forces
Excel to treat the cell entry as a text and align it to the left.
 Out of these three ways of making a number show as aligned to the left, only the first
one leaves the number intact. The other ways transform the number to text and will give
you issues when you include them in calculations that normally would work on numbers.

Now you have an idea of how useful an understanding of the default ways Excel treat
the different data types can be in your day to day use of Excel.
 Data Consistency, starting with the end in view
Excel is different from every other Microsoft Office program you use. Most of the
documents, reports and analysis you do with Excel will be used some day in the future for
another report or analysis.

To become an expert in Excel, you have to always work with the end in mind. You have to
create your Excel documents in such a way that you can easily use them for some bigger
reports in the future. And there are some general rules I’ll recommend you work with to
achieve this.

1. Always use a compact table structure for entering you core data in Excel. This means
using the minimum number of rows and minimum number of columns. Example of a
compact table and non-compact table is shown below:

Same table but not compact, shown below.

In the non-compact table example, you can delete rows 37 and 38 without deleting any
data in the table.

2. Use descriptive names for your column headers and row headers.
Be as descriptive as possible in naming the fields in your table, make it easy for anyone
who will view your table to understand the information it convenes.

Below is a table with field names that are descriptive enough for anyone to understand the
information the table convenes.
Below is a table that has headers that are not descriptive enough. It’s hard to figure out the
specific information in the table. Is it a table of revenue or expense? Are the companies
clients or suppliers? What year do the months represent — January 2014 or January 2015?
Whomever you send this report to will call you back for a detailed explanation of what
you intended to report.
3. Name your Excel sheets
Don’t just go ahead with the default names — Sheet1, Sheet2, … — rename the sheets to
reflect the contents of the sheet. This makes your work better organized and future use
more convenient.

To rename a sheet, right click on the sheet name (the default names are Sheet1, Sheet2,
…) and select rename. And as you see above, you can change the color from the default
too.
4. Also use descriptive names when renaming the Excel sheets.

5. Avoid putting too many tables in one Excel sheet. Best to keep just related tables in the
same sheet if you must put more than one table in the sheet. It makes naming the sheet
easy and straightforward.

6. Use same naming conventions and table structure across all similar Excel files,
especially weekly, monthly and yearly reports of the same data.

7. Don’t use CAPS excessively. It makes your reports very unprofessional.

 Building Datasheets that can easily scale

Occasionally, you will have to work on a table whose data grows continually. We can refer
to such tables as datatables. All tables hold data and can technically be referred to as a
datatable, but in this book we will refer to all tables as just tables and limit the term
datatable to only tables that grow perpetually.

An example of such a table is an Employee Record table. As long as the company exists
the table will keep growing and even if the company aims to not have over a 100
employees, there will always be old employees leaving and new ones taken to replace
them. And they all have to be captured in the employee record table, no employee’s record
is deleted when he leaves, there’s only a field added to capture his resignation.

There are some peculiar ways of treating a datatable.

1. Have only one datatable on a sheet. Since a datatable is a table you expect to grow over
time, having only one on a sheet lets you have access to all the rows and the columns in
the Excel sheet.

2. Start a datatable as close to cell A1 as possible. Again, this is to afford you the
maximum space in the sheet for your growing table.

3. Avoid meaningless gaps in the datatable. Make it as compact as possible.

4. Arrange the fields such that the most important or basic fields come first. For example,
in an Employee record table, name should come before contact address.
5. Have a field for every meaningful chunk of data. It is better to have separate fields for
first name and last name than have one field hold both.

6. Avoid merging cells in a datatable. It is better to repeat cell entries than merge the cells.
Merged cells aren’t formula friendly.

And as a bonus, avoid hiding rows and columns in the datatable. This will save you a lot
of future headaches.
 Sorting
Sorting is one of the most frequent task we do in Excel. Sorting lets you re-arrange data in
alphabetical order, lowest to highest, highest to lowest, and even by cell color.

We are used to having data arranged in a particular order — A to Z, January to December,

1 to 10, and so on.

Below is an example of a table that has its records haphazardly arranged. The states are
not arranged alphabetically and the months are not in the natural order.

First, let’s have the data sorted by State alphabetically

Below are the recommended steps to sorting a table. Select the table, go to the Home
menu and click on Sort & Filter.
The sorting dialog box comes up.

This dialog box allows you to add more than the default one level of sorting.

Select “State” in the Sort by box and A to Z in the Order box.

The result is shown below.

You can add an extra level of sorting in the sorting dialog box. This would be useful in
sorting tables like a national population census table. You might want to sort first by state
(from Abia to Zamfara) and then an extra level of sorting by Local Government Areas. So
you’ll have a setting similar to the one below
Next is to sort the months in the natural order we are used to — Jan to Dec. This will
require a type of sorting called “Left to Right” as against the one we just did, called “Top
to Bottom”.

So to get this done, we select the table starting from the first month to the last month. We
will leave the state field out because we want it to remain in the position it is.

Then select the row the months are on (Row 2) and set the order to Oldest to Newest.
Below is the result.
 Filtering

Filter is one of the Excel power user’s most used tool. It allows you selectively choose
what you want to view in a table and hide the rest.

It is very easy to access and can be accessed from three different places in Excel.

By right clicking and selecting Filter.

From the Home menu, clicking on Sort & Filter at the right.
From the Data menu.

Once you’ve turned on the Filter tool by clicking on it, you will see a dropdown box
beside the headers of the table.
Clicking on the dropdown box shows you all the unique items in that field and you can
select the ones you want to view (hiding the rest). By default, all items are selected so you
will have to unselect the ones you don’t want to see.

In the screenshot below, all the pizza items were unselected except the BBQ Chicken
(meaning only BBQ Chicken was selected).
Notice the blue row numbers, it is Excel’s way of visually hinting you that some rows
have been hidden as they did not contain the items we want to view.

Filtering is that simple and straight forward.

 Data Cleaning

A lot of times the data you are given to work on in Excel is not in a format usable for you
and need some cleaning before you can go ahead with the analysis you intended doing on
it. In most cases you have to manually clean the data and fix whatever issues it has one by
one before progressing with the original analysis you intended to do on the data.

Fortunately, Excel has some nifty tools to help you automate some of this data cleaning
process. The most common ones are,
Removing duplicates, and
Text to Columns
Then we’ll cover a special tool that can help you do a quick categorization of your data:
Subtotal. Finally we’ll cover Data Validation, an ingenious tool for reducing data entry
errors in your Excel files.

Removing Duplicates.

Occasionally, you will have a table and you’ll want to remove duplicate entries. If it were
a sales transaction table, you might want to remove the duplicate sales entries. In the
example below, it is a table of items (Pizzas) and we want to remove the duplicate entries
leaving only unique entries.
As illustrated above, you select the entire records first and then go to Data menu, click on
Remove Duplicates. You will get a confirmatory dialog box. Click on OK.

You’ll see a result showing the number of duplicate values that were found and the
number of unique values found. Basically, what Remove Duplicates does is it leaves one
record of each item and removes all the extra record for that item that it finds.

When you select a table with more than one field entry, the Remove Duplicates remove
only the entries that have same value in all the fields as a previous entry except you
specify which fields to exclude in the search for duplicates.

In the screenshot below, we excluded Car Sales from the fields to include in the search for
duplicates. So rows that have the same entries in all the other fields will be deleted except
one.
Text to Columns

There will be times you will have data you would prefer split across multiple columns
squeezed into one column. This happens a lot when you copy data from an external source
into Excel or you open an exported data from other business software like CRMs and
ERPs.

Excel’s Text to Columns tool is the magic tool for splitting such data entries into multiple
columns provided there is a recognizable character separating each field or they have fixed
lengths per field. Below is a simple example for splitting a full name in one column to first
Name column and last Name field column.
Delimited is the option to specify that there is a recognizable character separating each
field. In this example, there is a space separating every first name from the last name.
Notice how Excel shows a line between the first names and the last names once you select
the appropriate delimiter (space, in this case). Click on Next and Finish.

Below is the result. Just as desired.

Subtotal
Subtotal is a secret tool for doing a quick analysis of a table in Excel. It breaks the data
down by categories and creates grouping that shows you different levels of details.

It is also very easy to use.

Below is an example where we’ll use it.

It is a market research data table showing the different car make sold in three different
auto dealerships.

We can apply a subtotal to this to see some interesting analysis.

Select the table, go to Data menu and click on Subtotal. In the dialog box that comes up, in
the “Add subtotal to” section tick all the fields that have numeric values (except you don’t
want to see a numeric analysis of them).

Once you click on OK, you get a result that looks like the following —

Level 1:
Level 2:

Level 3:
And the best part is that you can remove the subtotal and have your original table data
back just as it was before. To remove is as easy as clicking the Subtotal again and clicking
on Remove All.
See the result below. All the level 1, 2 & 3 groupings gone.

Data Validation

This is another secret but powerful tool in Excel. It helps you put in place some error
check mechanism and can be used by a skilled Excel user to make powerful Excel
dashboards.

Let’s see some of the common uses of it.

The table below is an Employee records table. In it we want to force people to enter just
departments specified at the left of the table. In fact, we want them to have the easy option
of seeing a pre-populated dropdown list and pick a department from the list options.
We select the cells we want to give this functionality, go to Data menu and click on Data
Validation (sometimes twice).

You’ll see that there are many options to pick between.

 1. Any Value. This is the default and it is same as not having any data validation. The
user can enter any value into the cell.
2. Whole Number. This forces the user to enter only numeric values that are whole
numbers. If the user enters a text or decimal entry he’ll get an error. This might be
applicable in an invoice sheet, for the cells that hold the order quantity if you don’t
sell fraction of your products.
3. Decimal. This forces the user to enter a whole number or decimal entry. A whole
number is same as a decimal with zeros after the decimal point. This might be great
in a financial model sheet, to hold values of growth assumption, exchange rates and
risk premiums.
4. List. This is the one we are most interested in. It enables us to limit the cell entries
to a list of options. We will use this in the example under review.
5. Date. This forces the user to enter a valid date entry.
6. Time. This forces the user to enter a valid time entry.
7. Text Length. This allows the user to enter any value as long as the character length
is not more than the specified value here. It is good for fields that hold phone
numbers, maybe you want to limit the entry to the +2348123456789 14 characters
long entry format.
8. Custom. Just as the name specifies, you want to limit the cell entry to something
less conventional and not covered by the other options.

In this example we are going to use the List option. So let’s select it.
Once you click the icon on the far right corner of the Source box, select entries to limit the
users to. In this case we have typed out the list options in cells G2:G6, holding the
different departments.
And it’s done.

The user is forced to choose between the options in the list. He even sees a dropdown
arrow that expands to a dropdown lists the moment he tries to fill the cell.

And that is how Data Validation works. When used creatively it can save you from the
stress of making corrections to forms people filled and can be used in conjunction with
formulas like VLOOKUP to make a dynamic report and dashboards.