Excel training

CRITICAL CORE OF EXCEL
-In this module, you will learn about key foundational features of Excel:
The Excel user interface, basic Excel terminology, how to operate
essential navigational controls in Excel and how to perform basic data
entry with Excel spreadsheets.-

Introduction to
MICROSOFT
EXCEL™
Excel is an Electronic
Spreadsheet Program.
An electronic spreadsheet is a
computer software program that is
used for storing, organizing and
manipulating data.

When you look at the Excel screen - or any
other spreadsheet screen - you see a
rectangular table or grid of rows and
columns.
In newer versions of Excel, each worksheet
contains roughly a million rows and more
than 16,000 columns, which necessitates an
addressing scheme in order to keep track of
where data is located.
The horizontal rows are identified by numbers
(1, 2, 3) and the vertical columns by letters of
the alphabet (A, B, C). For columns beyond
26, columns are identified by two or more
letters such as AA, AB, AC.
The intersection point between a column and
a row, as mentioned, is the small rectangular
box known as a cell.
The cell is the basic unit for storing data in
the worksheet, and because each worksheet
contains millions of these cells, each one is
identified by its cell reference.
A cell reference is a combination of the
column letter and the row number such as
A3, B6, and AA345. In these cell references,
the column letter is always listed first.
ROWS, COLUMNS AND
REFFERENCES
Address (COLUMN + ROW) – A1
COLUMNS A -> XFD (~16000)
ROWS 1 -> 1048576

Selecting Multiple
Cells/Rows/Columns
Selecting multiple cells will display the number of Rows
x Columns in the Address Bar such as:
“number of rows” R x “number of columns” C
For example, selecting cells B2 through G2 will have
the address bar displaying 1 Row x 6 Columns, but as
you end the selection the reference will return to the
address of the cell that was first selected (in these
examples it is either B2 or G20).
When selecting an entire column or an entire row, the
address will list the first cell in the selection. During the
selection of multiple columns/rows, the address bar will
stay blank but the selection address will show up in a
small pop-up (in these examples, 1048576Rx5C or
12Rx16384C)
For formulas, we call the selection by using:
• First Cell:Last Cell for cell arrays – E.g. B2:G20
• Column:Column for an entire column/s – E.g. B:B
or B:G
• Row:Row for an entire row/s – E.g. 5:5 or 5:12

Cell real value
Cell displayed value
Cell real value vs Cell displayed value
Excel allows its users to change data visible in cells through numerous methods, but the formula
bar will always display the real value of the cell. In the example below, the cell “Font color” was
changed to white, thus resulting in a blank cell (displayed value). By selecting the cell, we can see
the real value in the “Formula bar”, which is “1”.
Font color option

THE STATUS BAR
The Status bar appears at the very bottom of the Excel
window when selecting a range that contains values and
provides such information as the sum, average, minimum,
and maximum value of selected numbers. If the select
range does not contain numerical values, the status bar will
simply display a count of selected cells that are not empty.
RANGE
SELECT
ED
STATUS
BAR

CUSTOMIZING
THE STATUS
BAR
You can change what displays
on the Status bar by right-
clicking on the Status bar and
selecting the options you want
from the Customize Status Bar
menu.
Click a menu item to select it /
Click it again to deselect it.
A check mark next to an item
means the item is selected.

Row/Column size
The size of all rows/columns in Excel can be easily changed, either by dragging the separation lines between them or
by “Right Click” --> “Row/Column Width…”
Manually dragging rows or
columns to change size
Adjusting the size through the
“Row/Column Width…” menu

Selecting multiple rows/columns and
changing the size for one of them will
automatically change the size for all
selected rows/columns
Excel also comes equipped with an easy way to auto-dimension
rows/columns in order to properly fit all the information inside the cells.
This can be done by selecting “AutoFit Row Height” or “AutoFit Column
Width” from the “Format” Menu under the “Home” Main Tab or by selecting
the rows/columns we want to AutoFit and double-clicking on the outer
margin

Hiding, Deleting and Inserting
Rows/Columns
Before discussing these functions we need to understand the
notation of rows/columns in Excel. These notations are static and
cannot be changed by addition or removal of any number of
rows/columns.
Hide – this option hides any number of rows or columns selected by
reducing their width to 0;
Delete – deletes all selected rows/columns and redistributes the
notation for the others
Insert – Inserts one or more rows/columns on the left (for columns) or
above (for rows) the selected row/column.
If we delete columns A through E, all the information in
the other columns will move left but the letters for the
first 5 columns will still be A through E.
Performing calculations
-The syntax of formulas is very important in Excel. In this module, you
will get introduced to formulas and functions - learn how to write them,
use them to perform calculations and understand the different cell
references-

RANDOM FACT #23
2 Billion dollars spreadsheets
Back in May 2012, due to a series of spreadsheets which contained data that was not filled
manually but rather copied and pasted wrongfully, the company JP Morgan Chase announced that
they lost 2 Billion Dollars due to these technical errors. Although they never have incriminated
Excel as the root source of the issue, they have pointed out that those files were the main reason
of this loss.
““operated through a series of Excel spreadsheets, which
had to be completed manually, by a process of copying
and pasting data from one spreadsheet to another", and
"that it should be automated" but never was…
…After subtracting the old rate from the new rate, the
spreadsheet divided by their sum instead of their average,
as the modeler had intended. This error likely had the
effect of muting volatility by a factor of two and of lowering
the VaR . . .”

Excel functions
Excel uses functions to execute various logical commands through
formulas. Formulas are predefined in excel and available through the
“Insert Function” button:
All excel functions start with “=“
The most important of basic functions are:
• =IF – returns a TRUE or FALSE value for
a given test
• =AND – checks a series of conditions and
returns TRUE if all are proven to be true or
FALSE if at least one of them is false
• =OR – checks a series of conditions and
returns TRUE if at least one of them is
TRUE or FALSE if all of them are false
• =COUNTIF – returns the number of cells
which comply with a given condition
• =SUM – adds up all the numbers in a
range of cells

Excel functions can also be used to solve mathematical equations.
The input data for formulas can be introduced in the equation or
referenced to specific cells:
The formula reads =5*3-4 which
results in 11
The same result is obtained by
giving the cells A2, B2 and C2
the values 5, 3 and 4 – thus the
formula becomes
=A2*B2-C2

The ability to reference cells into functions and Excel Formulas comes in handy when manipulating large
amounts of data.
For example, if we were to try to find the total SUM for the values
in the A1:A25 range
We could be using addition like the pervious example, but that
would result in a very long formula:
=771.17+17.28+912.23+340.86+412.91+247.55+121.77+670.88
+251.08+85.29+284.53+523.13+376.76+571.73+955.27+345.39
+529.95+67.05+395.30+685.27+555.47+431.99+754.41+417.68
+767.79
Using cell references would also make for a long formula:
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+
A15+A16+A17+A18+A19+A20+A21+A22+A23+A24+A25
The most convenient solution is provided by the excel formula
SUM which returns the sum of all numeric values in a given
array:
=SUM(A1:A25)

Editing formulas in order to change references can be done very easily by double-clicking on the cell containing a
formula or selecting the cell and pressing F2. Once we start editing the formula, all active references will be
highlighted with different colors (our formula only has one range B2:B26). These ranges can be edited by:
• Erasing and modifying parts of the formula
• Moving the range outline and reshaping it with the mouse

Formulas can be easily applied
on multiple cells by dragging the
right-bottom corner of the cell
which contains a formula.
Applying formulas on multiple cells will also lead to
changes in the reference of that formula.
Formula reference is B3:C3. By “dragging” the
formula downwards, the reference cells are also
moving down.

Formulas can also be applied by
double-clicking in the bottom-right
corner of the cell containing a formula,
when the cursor turns into a cross.
However, this automatic way of
applying a formula will continue the
formula as sought fit by Excel.
Usually, the formula will be applied for
as long as there is data on the
right/left columns of the one where we
are working.

Blocking References
References can be blocked so that when we apply a formula to multiple rows or columns, some or all the references will
stay the same.
In the following example we will attempt to find an easy way to calculate the final bonuses for our employees by applying
a 25% multiplier to their current bonus. If we were to write the formula for the first employee we would simply multiply his
bonus with the multiplier box =C5*C2 (1st screenshot). However, upon applying the formula to all employees the
reference for our multiplier will also move down (2nd screenshot).
In order to avoid this we will block the references for C2 with an absolute reference so that they will not change,
regardless of the direction in which we apply the formula. This can be achieved by adding the “$” before the column and
row of our cell. Thus our formula becomes =C5*$C$2.
1 2 3

Types or references:
• Free “=C2” – our reference cell will move
if we apply the formula to other cells or
columns
• Blocked Column “=$C2” – our reference
cell will move if we apply the formula to
other rows but the column will remain
always remain the same
• Blocked Row “=C$2” – our reference cell
will move if we apply the formula to other
columns but the row will remain always
remain the same
• Absolute Block “=$C$2” – our reference
cell will not change regardless of we apply
it Blocked
Column
reference
Blocked
Row
Reference

A matter to be concerned with is the impact of copying, pasting or deleting references to an Excel Formula.
This is where most mistakes occur in workbooks are can be very hard to identify for the “untrained eye”.
Altering references:
• Copying data – if we copy a cell which acts as a reference to a formula, nothing will happen to that cell. Our
old reference was not altered in any way;
• Cutting data – if we cut a cell which acts as a reference to a formula, the cell reference will also move in the
formula. For example, if we cut B3 which acts as a reference in the simple formula =B3, the formula will
automatically follow the reference (so if we paste in A3, the formula becomes =A3);
• Deleting data – A regular delete will only clear the content of a cell, but if we delete a row/column which
contained a reference to an excel formula, that formula will now display a #REF! in the places where the
reference was found;
Altering formulas:
• Copying formulas – when copying a formula, any free reference (with no row/column blocks) will move
exponentially to the formula’s new placement – for example: =A1+B2 a formula situated in C3 – if we copy
the formula in C6, it’s references will move so that the formula becomes =A4+B5;
• Cutting a formula – will not alter any of the references!
• Deleting a formula – will not harm any references since the formula will no longer exist;

Rows/Columns
rows/columns.
Formatting
-Formatting helps to highlight key messages and make the data
presentable. This module covers several formatting tools like font
formatting, borders, alignment, number formatting, as well as the Excel
styles and themes-

Basic cell formatting options
These basic options can be accessed through the “FONT” or “ALIGNMENT” categories under the “Home” Main Tab.
Borders – allows for the
addition of different types of
borders for the selected cells
Text Alignment – several
options for changing the
position of text inside a cell
Wrap Text – increases the size of the row in order to fit
all the information in a cell;
Merge & Center – merges multiple cells into one (The
new cell will keep the address of the first cell)
Wrap Text OFF Wrap Text ON

Merging Cells
Excel allows for the merge of multiple cells as long as the obtained shape is a square or rectangular. Text inside
merged cells will automatically be centered but it won’t be aligned in the middle.
Merged cells will keep the address of the first cell – B2 in the second example and A2/A4 in the third example.

Excel sheets are split into cells by gray borders. However, there is an option to add
extra borders with different colors and style in order to properly highlight important
data and create well defined tables.
These options can be accessed under the “FONT” menu in the “HOME” Main Tab.
Excel provides multiple
options for designing our
tables such as one-sided
borders, different style
borders and line colors.
In the screenshot below
the “All Borders” option
was used for cells B3:F13
Cell Borders

Cell Formatting
Excel offers a wide variety of cell
formatting options. Formatting options
allow for the Cell Displayed Value to
be changed without affecting the input
data or Cell Real Value.
The formatting menu can be easily
accessed from the “Home” Main Tab
under “Number Format” section or by
pressing CTRL+1.

Default Formatting Options
By default, excel offers 11 formatting options, each having multiple variations which
can be accessed through the “More Number Formats…”
• General – Default formatting – it will automatically change if a specific value is
inserted;
• Number – The Cell Real Value will be automatically detected as numbers and will
allow for the addition of more decimal places;
• Currency – Will convert the value into currency (multiple options available);
• Accounting – Similar to the Currency formatting but with better alignment
options;
• Short Date – Will change the formatting of a date to DD/MM/YYYY ;
• Example: 09/20/2017;
• Long Date – Will change the formatting of a date to DDDD, MMMM DD, YYYY ;
• Example: Wednesday, September 20, 2017
• Time – Will convert the value in a HH:MM:SS AM/PM;
• Example: 12:00:00 AM ;
• Percentage – Will covert the given value in a percentage;
• Fraction – Will covert the given value in a fraction;
• Scientific – Will covert the given value in a scientific format;
• Text – Will ignore the format of the input value and treat all characters as strings
(plain text);

In the following example we will cover the
value “1” in all the default formatting's:
• General – 1
• Number – 1.00 – by default will add 2 decimal places
• Currency – $1.00 – by default will add the currency as dollar
• Accounting – same as in currency but will provide a special alignment
• Short Date – 1/1/1900 – the reason for this strange date is the perception
excel uses to store dates; All dates are attributed a number which is the
equivalent of the number of days that have passed since the 1st of January,
1900. This means that the 20th of September will be stored as 42998 (days
since 01/01/1900)
• Long Date – Saturday, January 1, 1900
• Time – 12:00:00 AM – the reason for this similarly strange formatting is the
perception Excel uses to store time. All hours are being processed as a fraction
of 1 divided by something. Excel understands 1 as the hour 12:00:00 AM, thus if
we were to put in 0.75 it would result in 06:00:00 PM and 0.5 will be 12:00:00
PM.
• Percentage – 100.00%
• Fraction – 1
• Scientific – 1.00E+00
• Text – 1

Another example with 0.5 on the C column and 09/24/2017 on column D

Rows/Columns
rows/columns.
Working with Data
-This module is all about working with data – and making it easy to work
with. This week you will learn how you can manage your spreadsheets –
find data with Filter and Sort, retrieve and change data using Find and
Replace, and use Conditional Formatting to highlight specific data-

Conditional Formatting is a simple but extremely important tool in Excel that allows the user to apply specific
sets of rules on a batch of data that will automatically color and/or change the format of that data. Rules may vary
from greater/smaller numbers all the way to complicated formulas.
Conditional formatting can be accessed through the “Styles” Menu in the “Home” Main Tab. For the following
examples we will use the table below presenting statistics about Sales for 10 employees.

Much like all excel options, Conditional Formatting offers a handful of pre-defined conditions that are
easy to apply. Keep in mind that these options need to be applied on a selected range, otherwise they
will not have any effect.
• Highlight Cells Rules:
• Greater Than… / Less Than… - based off a given number, highlights all cells that meet the
condition;
• Between… / Equal To… - highlights all cells that meet the conditions (between A and B or equal
to C);
• Text that Contains… - highlights all cells that contain a given letter or word;
• A Date Occurring… - overs a large variety of options in order to highlight specific cells formatted
as “DATE” (such as Next Month, Last week, and so on);
• Duplicate Values… - highlights all cells in the given range that are identical;
• Top/Bottom Rules:
• Highlights cells based of given conditions (Top 10 Items… / Top 10%... / Above Average… and
so on);
• Data Bars:
• Offers a wide variety of colors to highlight a percentage of a cell based off a given condition (for
example, if James has 79 Sales and we set the condition to 100, then 79% of the cell will be
colored in order to highlight the progress made towards achieving the target);
• Color Scales:
• Multiple options that highlight numerical values based off their average (for example, if our given
values range between 1 and 100 and we select the first option “GREEN-YELLOW-RED”, the
bottom-most values will be red, values around 50 will be Yellow and the top values will be green);
• Icon Sets:
• Different types of icons used to showcase differences between numerical values based off their
average;

In our first example we are going to try
and apply a color set on the Sales Rate
column in order to properly showcase
our 9% target.
We can start by applying a default color
scheme such as “Greater Than…” and
have the value set to 9% with a “Green
Fill with Dark Green Text”. We can then
apply a second rule using “Less Than…”
and use the same value of 9% but this
time with “Light Red Fill with Dark Red
Text”. This way, all values below 9% will
be Red and all values above 9% will be
Green. However, in this particular case
we are left with no color for cells that
contain the exact value of 9%.
In order to avoid such a possibility, we
will use a third rule through “Equal To…”
and select 9% as the value with “Yellow
Fill with Dark Yellow Text”.

Next off we are going to create a rule to showcase the differences in “Sales volume change”.
Going through the available options for conditional formatting we chose “Data Bars” as it seems to be the most
accurate way to highlight the changes in Sales Volume. Once applied, the rule will automatically add colored bars to
show the progress/regress of the changes in Sales Volume,

Each conditional formatting rule has in-depth
options which can be modified by using the
“Manage Rules…” option under “Conditional
Formatting”. Before opening this menu, make
sure to select at least one of the cells to which
the rule applies.

All options available to this type of formatting are displayed in the first cell. Next off we have a handful of secondary options
followed by a third and final option. The number of options may vary for each type of formatting (I.E. “Date Occurring” only
has one secondary option)
Rule types can be easily changed to the
ones that would best highlight our data.
The first 4 options allow for the formatting of
data based on simple conditions, while the
5th one only formats data based on an Excel
Formula.
The “Format” button opens a new window
which us to create a custom format which
will then be applied to all cells that meet the
formatting condition.

In order for the Formula Formatting to function we must
have at least one logical operator, otherwise there will be
nothing to guide the rule towards
Formatting based on a formula
Consider the following example:
Before filling the info in this
table we want to find an
efficient way to color the date
headers according to weeks.
For 10 days it would be easy
to just manually color them,
but if we are to use this on a
daily basis, it would amount to
a large amount of
unnecessary work which can
be avoided by using a formula
to color the cells
automatically.

First we are going to isolate the data in order to create an excel formula and test it. We took the dates on
a separate sheet and decided to create the conditional formatting based on the week number.
First, we used the formula =WEEKNUM which returns the week number of a specific date. For example,
the week containing January 1 is the first week of the year, and is numbered week 1.
We then used the formula =ISEVEN in order to simply but elegantly differentiate odd weeks from even
ones. This way, by uniting the formulas, even week numbers will have the final result TRUE while odd
ones will return FALSE.
=WEEKNUM(B3,2)
=ISEVEN(C3)
Final formula:
=ISEVEN(WEEKNUMB(B3,2))

We can then go to “Conditional Formatting”  “New Rule”  “Use a formula to determine which cell to
format”  we will have to create two formulas, one for odd and one for even weeks.
We then design a cell formatting which we like and proceed to apply the formula on all cells

Paste formatting
The easiest way to apply the
formatting and only the formatting is
by copying a cell that has the
formatting rules (in our case B1) and
then pasting the formatting on the
other cells.
Excel has a vide variety of Pasting
options due to the complexity of it’s
cells. In this particular one, “Paste
Formatting” will only paste the
formatting rules from the original cell
and nothing else.

PASTING Options
Paste – will paste all the information from the copied cell (formatting/formulas or cell content)
Paste Values – will only paste the content of the cell – in case the cell contains a formula,
only the result of the formula will be pasted;
Paste Formulas – will only paste the formula from the copied cell;
Paste Transpose – will paste everything just like a regular paste but every reference of
the cell will be transposed;
Paste Link – will paste a reference to the original copied cell;

Filtering and sorting
Excel provides two very simple solutions for
organizing large amounts of data: Filters and
Custom Sorting.
Filters – a built-in function of Excel that allows for
an easy filtering or sorting of large chunks of data
based off:
 List Values – For example, sorting all data on a
column in Alphabetical order;
 Format – Filtering all data on a column based
on cell formatting options such as cell colors;
 Criteria – Filtering all information on a column
based on a specific criteria (different for data
containing Text or Numbers);
While sorting information will only re-arrange it
based on a given criteria, filtering data will “hide” the
rows that do not comply with the given criteria.
In order to apply a filter on specific data, all we have
to do is to select the table and go to “Sort & Filters”
and select “Filter”.
This can also be achieved by selecting the “DATA”
mail tab and clicking on the “Filter” button.

• Once the filter is applied, we can sort information using the options mentioned earlier. This can be done by
clicking on the “Sort” button
1. By default, “Sort Smallest to Largest” and “Sort Largest to Smallest”
will be the first two options in any filter applier. This function works for both
numerical and string (text) data. For text data it will arrange the information
in an Alphabetical or Un-alphabetical order (and the name will change to “A-
>Z” or “Z->A”);
2. “Sort by color” will present several options based on the different colors
found throughout the column;
3. “Clear Filter From COLUMN NAME” will remove any filter from the
table. However, it will not return the data to it’s previous state if we are to
sort the information;
4. “Filter by color” will hide all other data that does not have the same
color as the option chosen;
5. “Number/Text Filters” has several default options such as Greater
Than, Top 10, Contains (specific text) and so on;
6. “Search” option allows for us to search for a specific number/text inside
the filtered information.

A few examples of how sorting and filtering can
help us manipulate data:
1. Sort Smallest to Largest (Alphabetical order
if the data is text)
2. Sort by color
3. Filter by color
4.Clear Filter From COLUMN NAME
5.Number filter (on column C) – Greater Than
5
SORTING EXAMPLES

Using the “Search” function to sort data based on a specific criteria.
If we were to type the letters “Ja” in the “Search” bar for column B (Name) the details below will already showcase all
the results that contain those two letters.
After applying the filter
Applying a similar filter on the “Sales” column – all
values that contain the digit 4

Lastly, there is a simple difference we must understand between sorting data and filtering. Filtering occurs through hiding the data
that does not meet the input criteria, while sorting will keep all data and sort it in a specific way.
Although filtering will hide the rows not matching the information desired, trying to unhide that data will not work. Hidden data
through filters will look similar to that of normally hidden rows, but the color of the row numbers will be blue.
Filtering vs Hiding rows
The easiest way of identifying data which was hidden is a double line which occurs between lines which were hidden.
HIDDEN
ROWS:
1012->1013
1015->1021
FILTERED
ROWS
249->258
267->320
322->337

Custom Sorting – this function allows for a fast sorting of large amounts of data by applying several sorting
options on different columns of a table.
In the example below we will apply 3 sorts on a table in order to have them ordered properly.
The “Custom Sort” option can be selected from the same menu as the “Filter” option, after selecting our table.

Custom Sorting allows for an unlimited amount of sorting levels. This can be done by using the “Add Level” button.
• “Sort By” – will display a list of the available columns in the table;
• “Sort On” – will allow for 4 sorting options: “Values” / “Cell Color” / “Font Color” / “Cell Icon”
• “Order” – will display sorting options based on the “Sort On”; For example:
• If we select “Values” in the “Sort On” category, we can choose “A-Z” in the “Order” category.
• If we select “Cell Color” in the “Sort On” category, we can choose colors from the ones available in that specific
table column;
In our example we will have the information ordered by 3 criteria:
• First Level – Queue sorted A-Z
• Second Level – Number of days sorted Smallest to Largest
• Third Level – Norm sorted Largest to Smallest

You can see the Queue is ordered in an alphabetical order.
Each Queue section has the number of days sorted from low to high
If two agents from the same Queue have the same number of days, they will be
separated by the third level – Norm – from high to low
Finished sorted table

Rows/Columns
rows/columns.
Charts
-Charts are one of the most common ways to present data visually. This
module walks you through creating and modifying charts in Excel-

Charts
Excel has several different types of charts, allowing you
to choose the one that best fits your data. In order to use
charts effectively, you'll need to understand how different
charts are used.
For the following examples, we are going to use a
standard dataset representing the number of sales for 5
different departments for 5 different years.
Department 2013 2014 2015 2016 2017
UK 657 879 605 717 562
US 512 793 517 833 799
SP 988 720 596 785 637
DE 588 519 605 676 893
FR 649 897 621 597 951

Let’s start by inserting a chart. We are first going to use a
simple “vertical bars” chart and try all of the common
options to see how they can all enhance or decrease the
amount of clarity provided to the data.
To insert a chart, we first need to select the Raw Data
which will be showcased in the chart and then go to the
“Insert” Main Tab and select either the wanted chart or the
“Recommended Charts” option.

The following window will come up if
we click on “Recommended Charts”
allowing us to choose from a preset
which Excel will find most useful. We
can also click on “All Charts” to get the
full range or data charts available.
• Column Charts – uses vertical bars
to compare data
• Line Charts – ideal for showcasing
trends
• Pie Chart – proportion comparison
• Bar Charts – same as column
charts but horizontal
• Area Chart – similar to line charts
but the areas under the lines are
filled
• Surface Charts – a 3D landscape
ideal for large data sets

In the first example we are going to
chose a Column Chart. The following
image will appear in our document,
showcasing the differences between
department sales for each individual
year. Next off, Excel provides a wide
range of customization tools in order to
modify the data so that we can see
exactly what we want from it.
Customization can be done through the
“Design” and “Format” Main Tabs.
There is enough customization to
modify the data in any way shape or
form we need. The most basic options
include:
• Data Labels, chart titles, legends, gridlines
and trends (all which can be done from the
“Add Chart Element” option
• Colors and dimensions which can be
primarily done from the “Format” Main Tab

We can also edit the data for our chart,
swap the axis and even change the
chart type with ease using the options
under the “Design” Main Tab
Charts also provide a few handy side
options to further filter the data as well
as quickly adding chart elements and
changing chart colors.
These will pop-up in the upper-right
corner of any chart after you click on it

A few examples of changing chart types as well as adding elements:

Rows/Columns
rows/columns.
Working with Multiple Worksheets & Workbook
-This module is all about working with multiple worksheets and
workbooks. Learn how you can combine data, manage datasets and
perform calculations across multiple sources-

RANDOM FACT #101
You cannot name a Sheet
“History”
The reason for this is a special and not so well known Excel
feature which keeps a tracking record of changes made in a
document. In order to check a file History, you need to press
the following keyboard combination (in succession, not
simultaneously):
ALT+T+T+H
This will open a window which allows for a few filters
regarding the changes we want to see. Upon confirming, a
sheet will be created named “History” which will contain a
list of the changes made in the current file:

As stated previously, Excel offers the option of creating multiple worksheets which can all contain data or
formulas, as well as opening separate Excel instances (or workbooks) and they can all be referenced
together.
Let’s start with referencing between worksheets – or simply “sheets”. Just like we have learned, the
physical address of a cell of given by the Column & Row name/number. However, when working
between sheets, in order to differentiate a cell – say, A1 – we must also add the Sheet Name in front of
the cell address.
Thus, A1 from Sheet1 will be come Sheet1!A1 and A2 from Sheet 2 will be Sheet2!A1. Luckily, we don’t
have to write the actual address of the sheet if we want to reference something from another part of the
workbook. By switching workbooks while working on a formula, Excel will keep the formula active in the
formula bar and continue to take references of whatever we click on. There are formulas that will require
typing the Sheet Name manually, but we’ll get to that later.

A cross-workbooks reference will look like this:
=[Book1]Sheet1!$H$7
Book1 being the name of the document being referenced while Sheet1 being the actual sheet where
the cell is located.
Referring between workbooks is just as easy, but instead of having only the Sheet Name, the
address will also acquire the document name – as long as it is still open! If by any change we
close the document, Excel will even keep a physical address of the document to keep the
reference alive. If by any chance the document gets erased, an error will be returned instead
of the reference name – “REF!” – and if the formula in which the reference was used can
continue without it, it will simply ignore the “REF!” alert.

A very common problem which you may encounter with Excel which can be extremely stressful and annoying is the double-
Excel-instance. Basically, when referencing another workbook in a formula, Excel will simply not recognize other workbook.
When switching to the other workbook, you will be able to navigate and edit the date while the formula you were creating in
the original workbook will be in a “pending” status.
This is caused by Excel opening two instances in your computer, two individual “Excels” which are completely individual from
each other. Unfortunately, the only to resolve this matter is to shutdown the workbook that did not open under the same Excel
process and re-open it.
Status can be “Source is open” or
“Error: Source not found”
The Location section will display the
actual PC address of the referenced
workbook – if the workbook was not
saved, it will be blank.
We can easily check all connections to
other excel workbooks by using the
“Edit Links” option under the “Data”
Main Tab – “Queries & Connections”
section. Upon using the option, the
following will be displayed:

Rows/Columns
rows/columns.
Text and Date Functions
-By the end of this module, you will be an expert in Date and Text
functions. This module discusses ways you can extract information and
manipulate data to fulfil specific business requirements-

RANDOM FACT#31
FLIGHT SIMULATOR
Very few people know, but
Microsoft Excel 1997 had an
amazing Easter Egg – The
program included a flight
simulator in which you could
explore the environment with the
mouse. you can move it back and
forth to vary the pitch of your
‘craft’ and sideways to roll it

Text and Date Functions
Every now and then, the working data will not be in a proper formatting for interpreting, thus
leaving us with no option to manipulate the information. Luckily, instead of trying to change the
source data, we can use various TEXT and DATE functions to modify the data in our advantage.
Lets start with the basic formulas for this module:
• =TEXT(value,format_text) – this formula will take a cell and modify it’s value in a specific
format;
• =LEFT(text,[num_chars]) – will return x characters (num_chars) from a cell starting from the left
side.;
• =RIGHT(text,[num_chars]) – same as LEFT but from the right side;
• =MID(text,start_num,num_chars) – will return x characters (num_chars) from a cell starting
from the y number (start_num)
• =DATE(year,month,day) – will compose a date based off the imput values (numerical only)
And here’s how they work, on a few simple examples:
• =TEXT(43022,"dd-mmm-yyyy") – will return 14-Oct-2017;
• =LEFT(“Hello”,2) – will return “He”;
• =RIGHT(“Hello”,2) – will return “lo”;
• =MID("Hello",2,3) – will return “ell”;
• =DATE(2017,10,14) – will return 10/14/2017

Let’s take a practical example. The following data is a report which is exported as Text, which
means that it cannot be properly filtered or interpreted as Dates. We are going to try to
compilate this data in two different columns, each representing the start and end date of these
periods:
Data
21-25.03.2018
21-23.03.2018
20-24.03.2018
20-23.02.2018
19-21.03.2018
19-28.02.2018
19-23.02.2018
19-25.03.2018
18-19.03.2018
18-19.03.2018
16-23.02.2018
15-16.03.2018
15-18.02.2018
15-16.03.2018
If we look through the information, we can identify a few
simple trends which will hep will separating the
information before we create our dates:
• The first two characters are always the DAY of the first
date;
• The 4th and 5th characters are always the DAY of the
second date;
• The 7th and 8th characters are the MONTH of each
date;
• The last 4 characters are the year of each date;
We are going to separate these details on individual
columns before we use the DATE function to create our
new dates;

And this is how it all looks like in Excel:

Now let’s include the concatenate and text functions into example. Concatenate is a simple
excel function which can be achieved either by typing =CONCATENATE or by using the “&”
symbol between different “text” characters.
For example, if we want the output of a cell to display the data from two different cells (say, A1
and B1) but combined, we could use:
• =CONCATATENATE(A1,B1); or =A1&B1; in both cases, the output will be the same;
Let’s try to further complicate our example. We are going to use the H column to type the
following standard message for each of our dates:
• Started with “FIRST DATE” and ended with “SECOND DATE”
Upon typing =“Started with “&F2&” and ended with “&G2 in H2 we should get the wanted
result.
However, the following value will be displayed in the cell
“Started with 43180 and ended with 43184”
The reason for this is the formatting of the cells – Both F2 and G2 contain DATE values, which
mean that, when called in the CONCATENATE function, they will be displayed as their real
values instead of our chosen formatting. And here’s where the TEXT function comes in.

To save our formula, we will use the TEXT function as if we were formatting a cell in the DD-MMM
formatting:
The TEXT formula works with all basic formatting's available in the “Format” section of excel

Before we wrap up, here are a few examples of the more popular format_text options
for the TEXT function:
• DATES – “D” is short for DAY, “M” short for MONTH and “Y” short for year. These can
be combined in a various number of ways, to showcase the exact value we desired:
• DD/MM/YYYY – 04/12/2013
• DDD-MM – WED-12
• DD-MMMM-YY – 04-December-12
• HOURS – “H” is for HOUR, “M” for MINUTE and “S” for SECOND – additionally we
can use “PM” or “AM”:
• H:MM AM/PM – 1:29 PM
• HH:MM:SS – 01:29:35
• PERCENTAGE - 0.00% will translate in a number with 2 decimals and the “%” sign
• FRACTION - # ?/? will translate in a fraction such as 4 1/3
• CURRENCY - $#,##0.00 will translate in a sum of dollars with 2 decimals - $1,234.57

Rows/Columns
rows/columns.
Data Validation & Name Ranges
- The basics of creating a data list as well as interacting with defined
names-

You can use data validation to restrict the type of data or the values that users enter into a cell.
One of the most common data validation uses is to create a drop-down list.
Data validation provides various options to restrict data inside a cell, starting from data
formatting up to specific values. It can be accessed through the “DATA” Main Tab under the
“Data Tools”
Data validation has 3 main menus:
• Settings – chose the type of data as well
as the restrictions;
• Input Message – design the message
that will appear when the cell containing
data validation is selected;
• Error Alert – design the error message
that will appear if the data inserted
differs from the one we want;
Data Validation

We want to fill the table below with details
for each agent. However, in order to make
sure everyone uses the same format, we
will restrict each column to a specific type
of data.
We will also add specific input/error
messages to let the user know more
details regarding the input data
• LOCATION  we will restrict this to
“Brasov”, “Sibiu” or “Bucuresti”
• DEPARTMENT  we will restrict this
data based off the Location:
• Brasov  UK or US
• Sibiu  FR
• Bucuresti  SP or DE or RO

Before going forward with our example
we need to discuss Defined Names.
Data validation will allow us to restrict
data in the “Department” field based
off the location but we will need to
create specific list with each
department. In order to avoid typing in
the range of the list (G3:G5 for BV, G7
for SIB and G9:G11 for BUC) we can
create a “Name” for each range so
that we can use them with ease.
To do this, we need to access
“FORMULAS” Main Tab and select
“Define Name” under the “Defined
Names” section
DEFINED NAMES

Name  the Name by which the range
will be known;
Scope  if multiple excel workbooks
are selected, we can choose which
ones will have access to this range;
Comment  an explanation about the
contents of a defined name;
Refers to  the range which will be
converted to a Defined Name
After creating 3 defined names for
each of our departments, we can easily
view them in the upper-left side of the
screen under the drop-down menu for
the cell we have currently selected.
Another way to view or manage them
is by clicking on “Apply Names” or “Use
in Formula” under the “Defined Names”
sectionC

We can then proceed to apply our data validation on both fields and customize the messages.

Rows/Columns
rows/columns.
Conditional Logic
-Excel has several logical functions and this module explores some of
them. We will be learning the concept of conditional logic in formulas,
followed by how to conduct logic tests and use conditional operations, to
our benefit-

Conditional logic functions are the cornerstone of Excel Functions. Mastering nested basic functions is the first
step towards mastering complicated formulas, and the most basic of nested functions is “IF”.
We have discussed the IF function earlier, so let’s get into nested functions. A nested function is simply a function
repeated multiple times in order to achieve a needed effect.
Let’s take a practical example. We are going to build a function that will determine what the proper bonusing is for
an agent, considering that we have 3 different bonuses for each type of working hours (Full time – 1; Extended
Part-Time – 0.75; Part time – 0.5;)
For the formula to work, we are going to need to test the
amount of sales made by each agent versus the number of
sales in the bonusing system.
The first IF will have to test the working hours – Type column
– while the other 3 will be related to the amount of sales,
resulting in a bonus based on those targets.
Sales Type Bonus
15-25 1 $50.00
25-40 1 $75.00
>=40 1 $100.00
15-25 0.75 $35.00
25-40 0.75 $65.00
>=40 0.75 $80.00
15-25 0.5 $20.00
25-40 0.5 $40.00
>=40 0.5 $60.00

=IF(B2=1,IF(C2>=40,100,IF(C2>25,75,IF(C2>15,50,0))),IF(B2=0.75,IF(C2>=40,80,IF(C2>25,65,IF(C2>15,35,0))),IF(B2=0.5,IF(
C2>=40,60,IF(C2>25,40,IF(C2>15,20,0))),0)))
With each IF function, we will test if the Working hours (B2) are:
• Equal to :
• 1
• 0.75
• 0.5
• IF one of the values turns to be true, then we will be test if:
• The sales Value is larger or equal to 40;
• If false, the sales value larger than 25 (and automatically lower than 40 since the previous statement was false);
• If false, the sales value larger than 15 (and automatically lower than 25 since the previous statement was false);
• If false, the value will be 0;
The same result can be achieved in an easier way using the “IFS” function – IFS is a nested IF function which allows for
multiple tests and values if TRUE only – which means that we have to accompany it with an IFERROR function, in the
eventuality that the test will not be true.
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)
The final formula will look like this:

And this is how the IFS function will look:
=IFERROR(IFS(B2=1,IFS(C2>=40,100,C2>25,75,C2>15,50),B2=0.75,IFS(C2>=40,80,C2>25,65,C2>15,35),B2=0.5,I
FS(C2>=40,60,C2>25,40,C2>15,20)),0)
An additional part of using conditional formulas are adjacent testing functions such as:
• AND(logical1,logical2,…) – will test each logical test and return “TRUE” only if ALL tests are TRUE;
• OR(logical1,logical2,…) - will test each logical test and return “TRUE” only if AT LEAST ONE test is TRUE;
• NOT(logical) – will return TRUE if the test is FALSE and FALSE if the test is TRUE;
• IFNA(value,value_if_NA) – will return a value if a logical test returns #N/A;
All these logical tests can amount to very complex formulas, so we need to be able to properly follow the
logical line when things get complicated – luckily Excel offers a handful of tools that can help us evaluate our
formulas.

The “Formula Auditing” section under the “Formulas” Main tab has a few functions designed to help with
evaluating formulas:
• Trace Precedents – will draw arrows indicating all cells which affect the value of the cell currently selected;
• Trace Dependents – will draw arrows indicating all cells which are affected by the value of the cell
currently selected;
• Remove Arrows – will remove all arrows from the previous two options;
• Show Formulas – will temporarily eliminated the “Cell Displayed Value”, showing only the actual values of
all cells;
• Error Checking – will launch a new menu with more options on evaluating or checking an error (only if
there is at least one formula in our Excel Workbook which contains or results in an error);
• Evaluate Function – will provide a step-by-step evaluation of the formula from the cell we have currently
selected;
Let’s break these options one by one, since they are vital identifying problems with complex formulas.

Trace Precedents Trace Dependents
Each of the above cells impact the value of
E9, since it contains a SUM formula. Thus,
the arrows will point from each cell towards
the E9 cell we have selected.
Each of the cells form above (B5, D3, G3,
etc.) contain the product between the value
of E9 and the numerical value of each cell’s
ROW. Thus, the arrows will point from E9 to
all cells affected by it’s value.

Show Formulas – the cells blow (A1:C10) all contain a formula to calculate the ROW value + the COLUMN value of each
cell. Upon pressing the “Show Formulas” button, these cells will contain the actual formulas and not the results of the
formulas.

To showcase Evaluate Function let us return to our original example:

Rows/Columns
rows/columns.
Pivot Tables, Charts and Slicers
-This module deep dives into the popular (and very useful) pivot tables.
Learn how you can create and modify them to solve a variety of
business problems. Then gain skills to create interactive dashboards
with pivot charts and slicers-

PIVOT TABLES
Being able to quickly analyze data can help you make better business decisions. But sometimes it’s
hard to know where to start, especially when you have a lot of data. PivotTables are a great way to
summarize, analyze, explore, and present your data, and you can create them with just a few clicks.
PivotTables are highly flexible and can be quickly adjusted depending on how you need to display
your results. You can also create PivotCharts based on PivotTables that will automatically update
when your PivotTables do.
Let us consider the following example:
We have a list of raw data containing 100 agents as well as details about their location, sales, skills and the month of
when the sales were registered. A pivot table will help us to quickly analyze a few stats this data.

First, we need to select the data which will provide details for the pivot table. We will then go to the
“Insert” Main Tab and Select “Pivot Table”.
The following pop-up window will appear:
The first section will ask you for details as to
where the RAW data is located. By default it’s
set to take the range you had selected, prior to
pressing the “Pivot Table” button
The second part will ask for details as to where
we want our pivot table to be created. By
default it’s set to create a new sheet.

Pressing “OK” will bring up the following tab in a new sheet. This section will help us
customize the Pivot Table fields so it reflects the information desired.
This section will display the name of each column in our RAW data.
This information can be dragged & dropped in the area below.
This is where the magic happens. These 4 sections will help the pivot table
understand what we want to do:
• Filters  will act like a filter outside of the table, allowing us to switch
between specific types of data;
• Columns  Information in this area will act like columns in our pivot;
• Rows  Information in this area will act like rows in our pivot;
• Values  the actual data which will be generated in the table;

So, what do we want from our RAW data? Let’s try these two analysis:
1. Total number of sales per location for skill 1 and 2;
2. Average number of sales per month by location for skill 3;
1. First, we want the skills to act as filters so we can select 1 and 2. Thus, we will move the column “Skill” to the
“Filters” area. Next, we want the location to act as Rows so we can see all 5 locations in separated rows – thus
we will move “Location” to “Rows”. Lastly, we need the sales to be summed up per location, so we will move
“Sales” to the “Values” area . The result will give us a simple, elegant table showcasing what we wanted with
minimum effort!

By default, moving information to the “Values” field will generate a SUM of that data. However, this, can be modified
to do all sorts of functions. Let’s see example 2:

Another very beneficial tools
of Pivot Tables are Slicers
(these can also be used for
regular tables). By using the
“Insert Slicer” option under
the “Analyze’ Main Tab, we
can select a column of the
RAW data we want filtered
and this will generate a
“control panel” which allows
for fast & easy filtering

PIVOT CHARTS
Just as PivotTables behave similar to
regular tables but providing extensive
options for customizing the data, Pivot
Charts will allow for multiple level filtering
of information and real-time chart
modifications.
Let’s add a PivotChart to the previous
example. To do this, all we have to do is go
to the “Analyze” Main Tab and select
“PivotChart”. What this will do is to open a
template box from where we can choose
the type of chart we want.

Manipulating data in a pivot table is extremely easy and to make it even better, Excel offers a handful of options to
customize Pivot Tables as well as insert Pivot Charts.
Let’s see how a chart will help us manipulate and understand data better on the previous example. How hard is it to
see which Location had the highest average of sales in a month for skill 3? Or who had more sales in the month of
April between London and Bucharest?

Rows/Columns
rows/columns.
Automating Lookups
-How do find information from different parts of a workbook? This module
introduces you to one of the most important excel functions: VLOOKUP-

VLOOKUP
VLOOKUP is an essential excel formula that can be found in many of our daily files. The formula is used to
look a value inside a table and extract a corresponding value from the same row.
On one side (Columns B
and C) we have an
exported table containing
the number of sales for
each agent.
On the other side we need
to extract the information
for each one of our agents
which are split in 4 teams

We’re going to start writing our VLOOKUP
formula in F3 for the first agent.
VLOOKUP uses a very simple functioning
principle – you provide a value and a table
which has that value on it’s 1st column – once
the formula finds the value, it will return a
result which is on the same row as the one
you provided, but on a different column
VLOOKUP has 4 major elements
• Lookup_Value – the actual value we are trying to find in a table;
• Table_Array – the table where our values are located(both the LookupValue and the value we want in
return);
• Col_Index_Num – the number of columns (to the right) where the wanted data is (count starts with 1)
• [Range_Lookup] – 0 for an Exact Match or 1 if you want the lookup to be an approximate value;

B3 - Lookup_Value
B:C (or B2:C22) -
Table_Array
2 - Col_Index_Num since the
value we want back is on the
2nd column of the table
0 - [Range_Lookup]
because we want an
exact match
=VLOOKUP(E3,B:C,2,0)

We can then apply the formula to the other fields
of TEAM 1. Next we will apply it to the other
team tables but beware, copying the formula
from one table to another will move all
unblocked references!
The first example showcases the situation of
copying the formula without blocking the
Table_Array reference.
When copying the formula to the TEAM 2 table,
both the Lookup_Value and the Table Array will
be moved 3 columns to the right. While we want
that to happen to the Lookup_Value (since we
don’t want to look for the same agents we did in
the TEAM 1 Table) this will result in an error
since the Table_Array will be completely out of
place.

Excel training

Recommended

Recommended

More Related Content

What's hot

What's hot (20)

Similar to Excel training

Similar to Excel training (20)

Recently uploaded

Recently uploaded (20)

Excel training

Editor's Notes