SPREADSHEETS

A spreadsheet is an interactive computer application program for organization, analysis and

storage of data in tabular form. Spreadsheets developed as computerized simulations of

paper accounting worksheets.

The program operates on data represented as cells of an array, organized in rows and

columns. Spreadsheets allow you to organize, edit, and analyze different types of information

using spreadsheets.

Examples of spreadsheet programs

Today, Microsoft Excel is the most popular and widely used spreadsheet program, but there
are also many alternatives. Below is a list of spreadsheet programs that can be used to
create a spreadsheet.

 Google Sheets - (online and free).

 iWork Numbers - Apple Office Suite.
 LibreOffice -> Calc (free).
 Lotus 1-2-3 (discontinued).
 Lotus Symphony - Spreadsheets.
 Microsoft Excel – Most widely used a product of Microsoft.
 OpenOffice -> Calc (free).
 VisiCalc (discontinued).

Uses of spreadsheet:
The three most common general uses for spreadsheet software are to create budgets,
produce graphs and charts, and for storing and sorting data. Within business spreadsheet
software is used to forecast future performance, calculate tax, completing basic payroll,
producing charts and calculating revenues.

Modelling and Planning

Household Finance Planning

Data Manipulation & Analysis Notes

 Business Accounts and Budgeting
Invoices
Forecasting -Predictions / Simulations
Calculations e.g. Adding, Subtracting, etc.
Break even analysis
Statistical analysis
Creating Graphs e.g. bar chart, pie chart.
Collect data from different sources e.g. phone number, prices.
Explore and interpret data in order to draw conclusions for business

Advantages of spreadsheets

 Accuracy- Calculations are correct

 Automation- Calculations are completed automatically
 Organization- Information is organized and easy to access
 Easy to modify- Information is easy to edit if a mistake has been made by retyping or
using ‘undo’
 Data can be easily sorted and filtered
 Data can be quickly analyzed
 Data visualization- Reports can be made more visual by using charts and graphs

Data Manipulation & Analysis Notes

Parts of Excel Window

 Quick Access Toolbar – Collection of buttons that provide one click access to
commonly used commands such as Save, Undo or Redo.
 Title Bar – A bar the display the name of active workbook
 Ribbon – The main set of commands and controls organized task in Tabs and
groups, you can also customize the ribbon according to your preference,
 Column Headings – The letters that appear along the top of the worksheet to identify
the different columns in the worksheet.
 Worksheet Window – A window that displays an Excel worksheet, basically this is
where you work all the tasks.
 Vertical Scroll Bar – Scroll bar to use when you want to scroll vertically through the
worksheet window.
 Horizontal Scroll Bar – Scroll bar to use when you want to scroll horizontally through
the worksheet window.

Data Manipulation & Analysis Notes

  Zoom Controls – Used for magnifying and shrinking of the active worksheet.
 View Shortcuts – Buttons used to change how the worksheet content is displayed.
Normal, Page Layout or Page Break Preview.
 Sheet Tabs – Tabs the display the name of the worksheet in the workbook, by default
its name sheet 1, sheet 2, etc. You can rename this to any name the best represent
to your sheet.
 Sheet Tab Scrolling Buttons – Buttons to scroll the sheet tabs in the workbook
 Row Headings – The number that appears on the left of the worksheet window to
identify the different rows.
 Select All Button – A button that selects all the cells in the active worksheet
 Active Cell – The cell selected in the active worksheet
 Name Box – A box that displays the cell reference of the active cell
 Formula Bar – A bar that displays the value or formula entered in the active cell
 Office Button/File Tab – It provides access to workbook level features and program
settings. You will notice that in Excel 2007 there is a circle Office button while on the
Excel 2010 it replace with File Tab. If you click the circle office button you will see
this.

Features of Microsoft Excel

 Add Header and Footer- MS Excel allows us to keep header and footer in our

spreadsheet document.

 Find and Replace Command- MS Excel allows us to find the needed data (text and

numbers) in the workbook and also replace the existing data with a new one.

 Password Protection- It allows user to protect their workbooks by using password

from unauthorized access to their information.

 Data Filtering- Filtering is a quick and easy way to find and work with a subset of data

in a range. A filtered range displays only the rows that meet the criteria you specify

for a column. MS Excel provides two commands for filtering ranges:

Data Manipulation & Analysis Notes

 AutoFilter; which includes filter by selection, for simple criteria

Advanced Filter; for more complex criteria

 Data Sorting- Data sorting is the process of arranging data in some logical order. MS

Excel allows us to sort data either in ascending or descending order.

 Built in formulae- MS Excel has got many built- in formulae for sum, average,

minimum, etc. We can use those formulae as per our needs.

 Create different charts (Pivot Table Report)- MS Excel allows us to create different

charts such as bar graph, pie- charts, line graphs, etc. This helps us to analyze and

compare data very easily.

 Automatically edits/recalculation - MS Excel automatically edits the result if any

changes are made in any of the cell.

 Formula Auditing- Using formula auditing we can graphically display or trace the

relationships between cells and formulas with blue arrows. We can trace the

precedents (the cells that provide data to a specific cell) or the dependents (the cells

that depend on the value in a specific cell).

THE WORKSHEET LAYOUT

 Worksheet- A worksheet is a collection of cells where you keep and manipulate the
data. Each Excel workbook can contain multiple worksheets.
 A worksheet is made of lines (numbered with numbers) and of columns (numbered
with letters). The intersection of a line and a column is called a cell. A cell is therefore
represented by a number and a letter.
 A worksheet can contain up to 65,536 lines and 256 columns, more that 17 million
cells.
 Workbook - A workbook is a collection of one or more spreadsheets, also called
worksheets, in a single file.
 Spreadsheets are made up of rows and columns, which form a table or grid.

Data Manipulation & Analysis Notes

  A cell is a specific location within a spreadsheet and is defined by the intersection of a
row and column. Since most spreadsheets use numbers to define rows and letters to
define columns, cells are often referenced by a letter and number combination.
 A column is a vertical series of cells in a spreadsheet. Below is an example of
a Microsoft Excel spreadsheet with column headers (column letter) A, B, C, D, E, F, G,
and H. As you can see in the image, the last column H is the highlighted column in red
and the selected cell D8 is in the D column.
 Row- A row is the range of cells that go across (horizontal) the spreadsheet/worksheet.

Data Manipulation & Analysis Notes

Inserting rows on a worksheet

1. Do one of the following:

 To insert a single row, select either the whole row or a cell in the row above
which you want to insert the new row. For example, to insert a new row above
row 5, click a cell in row 5.
 To insert multiple rows, select the rows above which you want to insert rows.
Select the same number of rows as you want to insert. For example, to insert
three new rows, you select three rows.
 To insert nonadjacent rows, hold down CTRL while you select nonadjacent
rows.

How to select cells, ranges, rows, or columns

2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert
Sheet Rows.

Tip You can also right-click the selected rows and then click Insert.

Note When you insert rows on your worksheet, all references that are affected by the
insertion adjust accordingly, whether they are relative or absolute cell references. The same
behavior applies to deleting rows, except when a deleted cell is directly referenced by a
formula. If you want references to adjust automatically, it's a good idea to use range
references whenever appropriate in your formulas, rather than specifying individual cells.
Tips

 To quickly repeat the action of inserting a row, click the location where you want to
insert the row, and then press CTRL+Y.

Data Manipulation & Analysis Notes

  If there is formatting, you can use Insert Options to choose how to set the formatting
of the inserted rows.

Insert columns on a worksheet

1. Do one of the following:

 To insert a single column, select the column or a cell in the column immediately
to the right of where you want to insert the new column. For example, to insert
a new column to the left of column B, click a cell in column B.
 To insert multiple columns, select the columns immediately to the right of where
you want to insert columns. Select the same number of columns as you want to
insert. For example, to insert three new columns, you select three columns.
 To insert nonadjacent columns, hold down CTRL while you select nonadjacent
columns.

How to select cells, ranges, rows, or columns

2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert
Sheet Columns.

Tip You can also right-click the selected cells and then click Insert.

Note When you insert columns on your worksheet, all references that are affected by the
insertion adjust accordingly, whether they are relative or absolute cell references. The same
behavior applies to deleting columns, except when a deleted cell is directly referenced by a
formula. If you want references to adjust automatically, it's a good idea to use range
references whenever appropriate in your formulas, rather than specifying individual cells.
Tips

Data Manipulation & Analysis Notes

  To quickly repeat the action of inserting a column, click the location where you want to
insert the column, and then press CTRL+Y.
 If there is formatting, you can use Insert Options to choose how to set the formatting
of the inserted columns.

Delete cells, rows, or columns

1. Select the cells, rows, or columns that you want to delete.

How to select cells, ranges, rows, or columns

2. On the Home tab, in the Cells group, do one of the following:

 To delete selected cells, click the arrow next to Delete, and then click Delete Cells.
 To delete selected rows, click the arrow next to Delete, and then click Delete Sheet
Rows.
 To delete selected columns, click the arrow next to Delete, and then click Delete Sheet
Columns.

Tip You can right-click a selection of cells, click Delete, and then click the option that you
want. You can also right-click a selection of rows or columns and then click Delete.

3. If you are deleting a cell or a range of cells, in the Delete dialog box, click Shift cells
left, Shift cells up, Entire row, or Entire column.

If you are deleting rows or columns, other rows or columns automatically shift up or to the left.

Tips

Data Manipulation & Analysis Notes

  To quickly repeat deleting cells, rows, or columns, select the next cells, rows, or
columns, and then press CTRL+Y.
 If needed, you can restore deleted data immediately after you delete it. On the Quick
Access Toolbar, click Undo Delete, or press CTRL+Z.

Notes

 Pressing DELETE deletes the contents of the selected cells only, not the cells
themselves.
 Excel keeps formulas up to date by adjusting references to the shifted cells to reflect
their new locations. However, a formula that refers to a deleted cell displays the #REF!
error value.
Worksheet formatting

 Fonts
 Alignments
 Column width and row height
 Borders
 Enhancing data appearance
 Page formatting

Types of references
Relative references

 Relative and absolute references behave differently when copied and filled to other
cells. Relative references change when a formula is copied to another cell.

 By default, all cell references are relative references. When copied across multiple
cells, they change based on the relative position of rows and columns. For example,
if you copy the formula =A1+B1 from row 1 to row 2, the formula will
become =A2+B2.

Data Manipulation & Analysis Notes

  Relative references are especially convenient whenever you need to repeat the same
calculation across multiple rows or columns.

Absolute references

There may be a time when you don't want a cell reference to change when copied to other
cells. Unlike relative references, absolute references do not change when copied or filled.
You can use an absolute reference to keep a row and/or column constant.
An absolute reference is designated in a formula by the addition of a dollar sign ($). It can
precede the column reference, the row reference, or both.

You will generally use the $A$2 format when creating formulas that contain absolute
references. The other two formats are used much less frequently.

Mixed cell references

 A mixed reference is a reference that refers to a specific row or column. For

example, $A1 or A$1.
 If you want to create a mixed reference- press the F4 key on the formula bar two or
three times depending on whether you want to refer to row or column. Press F4 one
more time to go back to the relative cell reference.

Data Manipulation & Analysis Notes

Automatically Link data in another sheet in Excel

We can link worksheets and update data automatically. A link is a dynamic formula that pulls
data from a cell of one worksheet and automatically updates that data to another worksheet.
These linking worksheets can be in the same workbook or in another workbook.

One worksheet is called the source worksheet, from where this link pulls the data
automatically, and the other worksheet is called the destination worksheet that contains that
link formula and where data is updated automatically.

Two methods of linking data in different worksheets

We can link these two worksheets using two different methods.

1. Copy and Paste Link

o From source worksheet, select the cell that contains data or that you want to
link to another worksheet, and copy it.
o Go to the destination worksheet and click the cell where you want to link the cell
from the source worksheet. On the Home tab, click on the drop-down arrow
button of Paste, and select Paste Link from “Other Paste Options.”
o Save the work or return to the source workbook and press ESC button on
the keyboard to remove the border around the copied cell and save the work.

Data Manipulation & Analysis Notes

 2. Enter formula manually

 In the destination worksheet, click on the cell that will contain link formula and enter
an equal sign (=)
 Go to the source sheet and click on the cell that contains data and press Enter on the
keyboard. Save your work.

Using these two methods, we can link a worksheet and update data automatically depending
upon your requirements.

Update cell on one worksheet based on a cell on another sheet

Suppose we have a value of 200 in cell A1 on Sheet1 and want to update cell A1 on Sheet2
using the linking formula. We can do that by using the same two methods we’ve covered.

Using Copy and Paste Link method

Copy the cell value of 200 from cell A1 on Sheet1.

Data Manipulation & Analysis Notes

Go to Sheet2, click in cell A1 and click on the drop-down arrow of Paste button on
the Home tab and select Paste Link button. It will generate a link by automatically entering
the formula =Sheet1!A1.

Or right-click in the cell on the destination worksheet, Sheet2, and choose Paste
Link from Paste Options: It will generate linking formula automatically.

Data Manipulation & Analysis Notes

Data Manipulation & Analysis Notes
Entering formula manually

We can enter the linking formula manually in cell A1 on the destination worksheet Sheet2 to
update data by pulling it from cell A1 of Sheet1.

In cell A1 on Sheet2, manually enter an equal sign (=) and go to Sheet1 and click on cell A1
and press ENTER key on your keyboard. The following linking formula will be updated in
destination sheet that will link cell A1 of both sheets.
=Sheet1!A1

CELL DATA TYPES

Number data, also called values, is used in calculations. By default, numbers are right
aligned in a cell. In addition to actual numbers, Excel also stores dates and times as
numbers. Other spreadsheet programs treat dates and times as a separate data category.
Problems arise when numbers are formatted as text data. This prevents them from being
used in calculations.
Labels

In spreadsheet programs, a label is any descriptive text placed in a cell. A label most often
refers to a text entry such as a heading used to identify a column of data. The term is also
used to refer to the headings and titles in charts - such as the horizontal and vertical axes
titles. Labels are used to identify other data in a spreadsheet. A label is not associated with a
particular value. Instead, it defines the column, spreadsheet or chart to which it is relevant.

Data Manipulation & Analysis Notes

 Formula: is an expression that calculates the value of a cell. For
example, =A2+A2+A3+A4 is a formula that adds up the values in cells A2 to A4.
All formulas start with an = sign.
Function is a predefined formula already available in Excel. Functions perform specific
calculations in a particular order based on the specified values, called arguments, or
parameters.
Calculations

The basics of Excel formulas

Before providing the basic Excel formulas list, let's define the key terms just to make sure
we are on the same page. So, what do we call an Excel formula and Excel function?

 Formula is an expression that calculates the value of a cell.

For example, =A2+A2+A3+A4 is a formula that adds up the values in cells A2 to A4.

 Function is a predefined formula already available in Excel. Functions perform specific

calculations in a particular order based on the specified values, called arguments, or
parameters.

For example, instead of specifying each value to be summed like in the above formula, you
can use the SUM function to add up a range of cells: =SUM(A2:A4)
You can find all available Excel functions in the Function Library on the Formulas tab:

There exist 400+ functions in Excel, and the number is growing by version to version. Of
course, it's next to impossible to memorize all of them, and you actually don't need to.
The Function Wizard will help you find the function best suited for a particular task, while

Data Manipulation & Analysis Notes

the Excel Formula Intellisense will prompt the function's syntax and arguments as soon as
you type the function's name preceded by an equal sign in a cell:

Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic
for that function.

Tip. You don't necessarily have to type a function name in all caps, Microsoft Excel will

automatically capitalize it once you finish typing the formula and press the Enter key to

complete it.

10 Excel basic functions you should definitely know

What follows below is a list of 10 simple yet really helpful functions that are a necessary skill
for everyone who wishes to turn from an Excel novice to an Excel professional.

SUM

The first Excel function you should be familiar with is the one that performs the basic
arithmetic operation of addition:

SUM(number1, [number2], …)

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional,
other arguments are required. Meaning, your Sum formula should include at least 1 number,
reference to a cell or a range of cells. For example:
=SUM(A2:A6) - adds up values in cells A2 through A6.
=SUM(A2, A6) - adds up values in cells A2 and A6.
=SUM(A2:A6)/5 - adds up values in cells A2 through A6, and then divides the sum by 5.

Data Manipulation & Analysis Notes

 In your Excel worksheets, the formulas may look something similar to this:

Tip. The fastest way to sum a column or row of numbers is to select a cell next to the

numbers you want to sum (the cell immediately below the last value in the column or to the

right of the last number in the row), and click the AutoSum button on the Home tab, in

the Editing group. Excel will insert a SUM formula for you automatically.

Useful resources:

 Excel Sum formula examples - formulas to total a column, rows, only filtered (visible) cells,
or sum across sheets.
 Excel AutoSum - the fastest way to sum a column or row of numbers.
 SUMIF in Excel - formula examples to conditionally sum cells.
 SUMIFS in Excel - formula examples to sum cells based on multiple criteria.

AVERAGE

The Excel AVERAGE function does exactly what its name suggests, i.e. finds an average,
or arithmetic mean, of numbers. Its syntax is similar to SUM's:

AVERAGE(number1, [number2], …)

Having a closer look at the last formula from the previous section (=SUM(A2:A6)/5), what
does it actually do? Sums values in cells A2 through A6, and then divides the result by 5.
And what do you call adding up a group of numbers and then dividing the sum by the count
of those numbers? Yep, an average!

Data Manipulation & Analysis Notes

 So, instead of typing =SUM(A2:A6)/5, you can simply put =AVERAGE(A2:A6)

Useful resources:

 Excel AVERAGE - average cells with numbers.

 Excel AVERAGEA - find an average of cells with any data (numbers, Boolean and text
values).
 Excel AVERAGEIF - average cells based on one criterion.
 Excel AVERAGEIFS - average cells based on multiple criteria.
 How to calculate weighted average in Excel
 How to find moving average in Excel

MAX & MIN

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers,
respectively. For our sample data set, the formulas will be as simple as:
=MAX(A2:A6)
=MIN(A2:A6)

Data Manipulation & Analysis Notes

 COUNT & COUNTA

If you are curious to know how many cells in a given range contain numeric
values (numbers or dates), don't waste your time counting them by hand. The Excel COUNT
function will bring you the count in a heartbeat:

COUNT(value1, [value2], …)

While the COUNT function deals only with those cells that contain numbers, the Excel
COUNTA function counts all cells that are not blank, whether they contain numbers, dates,
times, text, logical values of TRUE and FALSE, errors or empty text strings (""):

COUNTA (value1, [value2], …)

For example, to find out how many cells in column A contain numbers, use this formula:
=COUNT(A:A)
To count all non-empty cells in column A, go with this one:
=COUNTA(A:A)
In both formulas, you use the so-called "whole column reference" (A:A) that refers to all of
the cells within column A.
The following screenshot shows the difference:

Useful resources:

 Excel COUNT function - a quick way to count cells with numbers.

 Excel COUNTA function - count cells with any values (non-empty cells).

Data Manipulation & Analysis Notes

 Excel COUNTIF function - count cells that meet one condition.
 Excel COUNTIFS function - count cells with several criteria.

IF

Judging by the number of IF-related comments on our blog, it's the most popular function in
Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and
return one value or perform one calculation if the condition is met, and another value or
calculation if the condition is not met:

IF(logical_test, [value_if_true], [value_if_false])

For example, the following IF statement instructs Excel to check the value in A2 and return
"OK" if it's greater than or equal to 3, "Not OK" if it's less than 3:
=IF(A2>=3, "OK", "Not OK")

Testing more than one condition

If you need to test for more than one condition, then take one of several actions, depending
on the result of the tests, you can nest multiple IF statements together in one formula. You'll
often hear this referred to as "nested IFs".
The idea of nesting comes from embedding or "nesting" one IF function inside another
In the example shown, we are using nested IF functions to assign grades based on a score.
The logic for assigning a grade goes like this:

Data Manipulation & Analysis Notes

 Score Grade

0-63 F

64-72 D

73-84 C

85-94 B

95-100 A

To build up a nested IF formula that reflects this logic, we can start by testing to see if the
score is below 64. If TRUE, we return "F". If FALSE, we move into the next IF function. This
time, we test to see if the score is less than 73. If TRUE, we return "D". If FALSE, we move
into yet another IF function. And so on.
Eventually, the formula we have in cell D5 looks like this:

=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))
You can see that it's important in this case to move in one direction, either low to high, or
high to low. This allows us to return a result whenever a test returns TRUE, because
we know that the previous tests have returned FALSE.

COUNTIF function in Excel - syntax and usage

Excel COUNTIF function is used for counting cells within a specified range that meet a
certain criterion, or condition.
For example, you can write a COUNTIF formula to find out how many cells in your
worksheet contain a number greater than or less than the number you specify. Another
typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a
particular letter(s).
The syntax of the COUNTIF function is very simple:

COUNTIF(range, criteria)

Data Manipulation & Analysis Notes

 As you see, there are only 2 arguments, both of which are required:

 range - defines one or several cells to count. You put the range in a formula like you usually
do in Excel, e.g. A1:A20.
 criteria - defines the condition that tells the function which cells to count. It can be
a number, text string, cell reference or expression. For instance, you can use the criteria like
these: "10", A2, ">=10", "some text".

And here is the simplest example of Excel COUNTIF function. What you see in the image
below is the list of the best tennis players for the last 14 years. The
formula =COUNTIF(C2:C15,"Roger Federer") counts how many times Roger Federer's
name is on the list:

Note. A criterion is case insensitive, meaning that if you type "roger federer" as the criteria in

the above formula, this will produce the same result.

Data Manipulation & Analysis Notes

 Excel COUNTIF function examples

As you have just seen, the syntax of the COUNTIF function is very simple. However, it
allows for many possible variations of the criteria, including wildcard characters, the
values of other cells, and even other Excel functions.
This diversity makes the COUNTIF function really powerful and fit for many tasks, as
you will see in the examples that follow.

COUNTIF formula for text and numbers (exact match)

In fact, we discussed the COUNTIF function that counts text values matching a specified
criterion exactly a moment ago. Let me remind you that formula for cells containing an exact
string of text: =COUNTIF(C2:C15,"Roger Federer"). So, you enter:

 A range as the first parameter;

 A comma as the delimiter;
 A word or several words enclosed in quotes as the criteria.

Instead of typing text, you can use a reference to any cell containing that word or words and
get absolutely the same results, e.g. =COUNTIF(C1:C9,C7).
Similarly, COUNTIF formulas work for numbers. As shown in the screenshot below, the
formula =COUNTIF(D2:D9,5) perfectly counts cells with quantity 5 in Column D.

Data Manipulation & Analysis Notes

 LEN

Whenever you want to know the number of characters in a certain cell, LEN is the function
to use:

LEN(text)

Need to find out how many characters are in cell A2? Just type =LEN(A2) into another cell.
Please keep in mind that the Excel LEN function counts absolutely all characters including
spaces:

AND & OR

These are the two most popular logical functions to check multiple criteria. The difference is
how they do this:

 AND returns TRUE if all of the conditions are met, FALSE otherwise.
 OR returns TRUE if any of the conditions is met, FALSE otherwise.

While rarely used on their own, these functions come in very handy as part of bigger
formulas.
For example, to check the quantity in 2 columns and return "Good" if both values are greater
than zero, you use the following IF formula with an embedded AND statement:

Data Manipulation & Analysis Notes

=IF(AND(A2>0, B2>0), "Good", "")
If you are happy with just one value being greater than 0 (either A2 or B2), then use the OR
statement:
=IF(OR(A2>0, B2>0), "Good", "")

TODAY & NOW

To see the current date and time whenever you open your worksheet without having to
manually update it on a daily basis, use either:
=TODAY() to insert the today's date in a cell.
=NOW() to insert the current date and time in a cell.
The beauty of these functions is that they don't require any arguments at all, you type the
formulas exactly as written above.

EXCEL ERROR TYPES

When working in Excel and creating formulas, Excel can return the result as an ERROR.
Errors in Excel described in this tutorial shows the following:

Data Manipulation & Analysis Notes

Error Message #### in Excel

If the cell you see an error message in the form of the string scale (sharp/hash) then you
notice that Excel can not display the data or the result of a formula, and also perhaps the
result of a negative date. This Error Message in Excel sometimes referred to as the 'Railroad
tracks'.

To eliminate this error, increase the width of a column or a cell or if it is a result of a formula
then make arguments formula.

ERROR message #DIV/0!

 Error message #DIV/0! Occurs when the set of formulas in which a number is divided
by zero (0).
 To avoid this error, check the formula in the cell where the error occurs and possibly
change the divisor.

Data Manipulation & Analysis Notes

ERROR message #NAME?

Error #NAME? occurs when the set of formulas that Excel can not calculate because no
defined name or the name of the particular information. In this case the image below notice
that I did not set separator colons (:) in the formula.

 To resolve this error, check the formula's spelling, or use the Formula Builder to have
Excel build the function for you.

ERROR message #NULL!

Error #NULL! appears when you specify an intersection of two areas that do not intersect.
The intersection operator is a space between the reference. In this case the image below
notice that the operator used an incorrect range (for reference consisting of a continuous
group of cells using the colon (:) as an operator (for example A1:B2) and to reference ranges
that are continuously used semicolon (;) operator (e.g. A1, A3). So semicolon (;) is a separator
or divider for two ranges of cells in this formula.

Data Manipulation & Analysis Notes

SOLUTION

 Check to make sure that you are using the correct syntax in your formula. You should
be using a colon to separate the first cell from the last cell when you refer to a
continuous range of cells in a formula.
 On the other hand, you should be using a comma should when you refer to two cells
that don't intersect.

ERROR message #NUM!

Error #NUM! Appears when you are not listed numbers or values that are important to the
formula. Also this error occurs when Excel can not calculate an operation with the values or
numbers. In the picture below you notice that Excel returns a #NUM! because the result is too
big and excel in it can not be displayed.

How to Resolve the Error

Check to see if you have entered any formatted currency, dates, or special symbols. Then,
make sure to remove those characters from the formula, only keeping the numbers
themselves.

Data Manipulation & Analysis Notes

ERROR message #REF!

Error #REF! Occurs when a missing cell in which the formula references. The frequent
occurrence of this error is when we delete a cell from a formula that pulls data or if we delete
the worksheet that contains the data that uses formulas or functions.

How To Resolve This Error

 Before you paste over a set of cells, make sure there are no formulas that refer

(hence, "REF") to the cells you're deleting.

 Also, when deleting cells, it’s important to double check what formulas are being

referred in those cells.

Data Manipulation & Analysis Notes

ERROR message #VALUE!

Error #VALUE! Occurs when using a defective argument type of function or formula.

Excel displays the #VALUE! error when it finds spaces, characters, or text in a formula
where it is expecting a number.

How To Resolve This Error

 An easy solution to this error is to double check your formula to make sure that you
only used numbers.
 If you're still seeing an error, check for blank cells, missing formulas linking to cells or
any special characters you may be using.

Notice in the picture below that the first formula pulls data from cell A6 which lists the text in
the second formula the second argument is not properly written.

Data Manipulation & Analysis Notes

#N/A Error

 When you see #N/A, this typically means that the numbers you are referring to in
your formula cannot be found.
 You may have accidentally deleted a number or row that's being used in your
formula, or are referring to a sheet that was deleted or not saved.

How to Resolve This Error

 Triple check all your formulas and be sure to look closely at which sheets or rows may
have been deleted or incorrectly referenced.

 If you have a few formulas linked together, check to see that everything in every formula
has a value.

Data Manipulation & Analysis Notes

Charts

Introduction

It can often be difficult to interpret Excel workbooks that contain a lot of data. Charts allow you
to illustrate your workbook data graphically, which makes it easy to
visualize comparisons and trends.

Charts are used to display series of numeric data in a graphical format to make it easier to
understand large quantities of data and the relationship between different series of data.

Types of charts

 Line Chart: The line chart is one of the most frequently used chart types, typically
used to show trends over a period of time.

 Column Chart: Column charts are typically used to compare several items in a specific
range of values. Column charts are ideal if you need to compare a single category of
data between individual sub-items.

Data Manipulation & Analysis Notes

  Clustered Column Chart: A clustered column chart can be used if you need to
compare multiple categories of data within individual sub-items as well as between
sub-items.

 Stacked Column Chart: A stacked column chart allows you to compare items in a
specific range of values as well as show the relationship of the individual sub-items
with the whole.

Data Manipulation & Analysis Notes

  Pie Chart: A pie chart represents the distribution or proportion of each data item over
a total value (represented by the overall pie). A pie chart is most effective when
plotting no more than three categories of data.

 Bar Chart: Bar charts are typically used to compare several categories of data. Bar
charts are ideal for visualizing the distribution or proportion of data items when there
are more than three categories.

Data Manipulation & Analysis Notes

  Area Chart: Area charts are ideal for clearly illustrating the magnitude of change
between two or more data points.

 Combination Chart: A combination chart is a visualization that combines two or more

chart types into a single chart. Combination charts are an ideal choice when you want
to compare two categories of each individual sub-item. They are commonly used to
create visualizations that show the difference between targets versus actual results.

Data Manipulation & Analysis Notes

  XY Scatter Plot Chart: Scatter charts in Excel (also known as XY scatter plot charts)
are excellent for showing correlations between two sets of values. For example an
XY scatter plot can be used to illustrate the correlation between employee
performance and competency, demonstrating that employee performance rises as
competency improves.

 Bubble Chart: A bubble chart is a variation of an XY scatter plot. Just like the XY
scatter plot, bubble charts show the correlation between two sets of data. The
difference is the addition of a third dimension that is represented by the size of each
bubble in the chart. This third dimension is typically used to show the relative impact
of a quantitative data item. For instance, in addition to showing employee

Data Manipulation & Analysis Notes

 performance versus competency, you can have the size of each bubble represent
years of service, allowing your audience to quickly get a sense of how years of
service may affect the relationship between competency and performance.

To insert a chart:
1. Select the cells you want to chart, including the column titles and row labels. These
cells will be the source data for the chart. In our example, we'll select cells A1:F6.

2. From the Insert tab, click the desired Chart command. In our example, we'll
select Column.

Data Manipulation & Analysis Notes

 3. Choose the desired chart type from the drop-down menu.

4. The selected chart will be inserted in the worksheet.

Data Manipulation & Analysis Notes

If you're not sure which type of chart to use, the Recommended Charts command will suggest
several different charts based on the source data.

Chart layout and style

After inserting a chart, there are several things you may want to change about the way your
data is displayed. It's easy to edit a chart's layout and style from the Design tab.
 Excel allows you to add chart elements—such as chart titles, legends,
and data labels—to make your chart easier to read. To add a chart element, click
the Add Chart Element command on the Design tab, then choose
the desired element from the drop-down menu.

 To edit a chart element, like a chart title, simply double-click the placeholder and begin
typing.

Data Manipulation & Analysis Notes

  If you don't want to add chart elements individually, you can use one of Excel's
predefined layouts. Simply click the Quick Layout command, then choose
the desired layout from the drop-down menu.

 Excel also includes several different chart styles, which allow you to quickly modify the
look and feel of your chart. To change the chart style, select the desired style from
the Chart styles group.

Data Manipulation & Analysis Notes

You can also use the chart formatting shortcut buttons to quickly add chart elements, change
the chart style, and filter the chart data.

Other chart options

There are many other ways to customize and organize your charts. For example, Excel allows
you to rearrange a chart's data, change the chart type, and even move the chart to a different
location in the workbook.
To switch row and column data:
Sometimes you may want to change the way charts group your data. For example, in the
chart below, the Book Sales data are grouped by year, with columns for each genre. However,

Data Manipulation & Analysis Notes

we could switch the rows and columns so the chart will group the data by genre, with columns
for each year. In both cases, the chart contains the same data—it's just organized differently.

1. Select the chart you want to modify.

2. From the Design tab, select the Switch Row/Column command.

3. The rows and columns will be switched. In our example, the data is now grouped by
genre, with columns for each year.

To change the chart type:

Data Manipulation & Analysis Notes

If you find that your data isn't well suited to a certain chart, it's easy to switch to a
new chart type. In our example, we'll change our chart from a Column chart to a Line chart.
1. From the Design tab, click the Change Chart Type command.

2. The Change Chart Type dialog box will appear. Select a new chart type and layout,
then click OK. In our example, we'll choose a Line chart.

3. The selected chart type will appear. In our example, the line chart makes it easier to
see trends in the sales data over time.

Data Manipulation & Analysis Notes

To move a chart:
Whenever you insert a new chart, it will appear as an object on the same worksheet that
contains its source data. Alternatively, you can move the chart to a new worksheet to help
keep your data organized.
1. Select the chart you want to move.
2. Click the Design tab, then select the Move Chart command.

3. The Move Chart dialog box will appear. Select the desired location for the chart. In our
example, we'll choose to move it to a New sheet, which will create a new worksheet.
4. Click OK.

Data Manipulation & Analysis Notes

 5. The chart will appear in the selected location. In our example, the chart now appears
on a new worksheet.

Keeping charts up to date

By default, when you add more data to your spreadsheet, the chart may not include the new
data. To fix this, you can adjust the data range. Simply click the chart, and it will highlight the
data range in your spreadsheet. You can then click and drag the handle in the lower-right
corner to change the data range.

Data Manipulation & Analysis Notes

If you frequently add more data to your spreadsheet, it may become tedious to update the
data range. Luckily, there is an easier way. Simply format your source data as a table, then
create a chart based on that table. When you add more data below the table, it will
automatically be included in both the table and the chart, keeping everything consistent and
up to date.

Pivot tables and Pivot Charts

 You can use a PivotTable to summarize, analyze, explore, and present summary data.
 Pivot-Charts complement PivotTables by adding visualizations to the summary data in
a PivotTable, and allow you to easily see comparisons, patterns, and trends.
 You can also connect to external data sources such as SQL Server tables, SQL Server
Analysis Services cubes, Azure Marketplace, Office Data Connection (.odc) files, XML
files, Access databases, and text files to create PivotTables, or use existing
PivotTables to create new tables.
About PivotTables

 A PivotTable is an interactive way to quickly summarize large amounts of data. You

can use a PivotTable to analyze numerical data in detail, and answer unanticipated

questions about your data.

Data Manipulation & Analysis Notes

  A PivotTable is especially designed for:

 Querying large amounts of data in many user-friendly ways.

 Subtotaling and aggregating numeric data, summarizing data by categories and

subcategories, and creating custom calculations and formulas.

 Expanding and collapsing levels of data to focus your results, and drilling down to

details from the summary data for areas of interest to you.

 Moving rows to columns or columns to rows (or "pivoting") to see different

summaries of the source data.

 Filtering, sorting, grouping, and conditionally formatting the most useful and
interesting subset of data enabling you to focus on just the information you want.

 Presenting concise, attractive, and annotated online or printed reports.

Creating a Pivot Table

 We want a pivot table showing us how many phone numbers are on file for each
employee.
 To do this, select the table, then go to the Insert tab and click the arrow associated
with the Pivot Chart button.

Data Manipulation & Analysis Notes

Select the table, then go to the Insert tab and click the arrow associated with the Pivot Chart

button.

 Select Pivot Chart & Pivot Table from the dropdown:

 The Create PivotTable dialog opens, as shown below.

 The Table/Range is selected for you. Select New Worksheet, then click OK.
 A new worksheet will open and you will see a pivot table (seen on the left in the
example below), a pivot chart (center) and the PivotTable Fields list as a sidebar on
the right side of the screen in. The latter is a list of all fields in your table.

Data Manipulation & Analysis Notes

There are three kinds of fields:

 Category fields are fields that you can group.

 Data fields are fields that contain data that you can add, subtract, multiply, or divide.

 Arbitrary fields are fields that are neither data nor category. The name field in our
table would be arbitrary.
 If you look at your Pivot Table Field list on the right, you can drag and drop from the
"Choose Fields to Add to Report" section down to the "Drag Fields between Areas
Below" section. Just drag and drop from the top part of the field list to the bottom part
and place it in a category: Filters, Columns, Rows, and Values.
 Your pivot table will appear in your spreadsheet as you do this
 Here's what we've done in the Field list on the right:

Data Manipulation & Analysis Notes

Introduction to Freezing Panes

Excel includes several tools that make it easier to view content from different parts of your
workbook at the same time, such as the ability to freeze panes and split your worksheet

To freeze rows:

You may want to see certain rows or columns all the time in your worksheet, especially header
cells. By freezing rows or columns in place, you'll be able to scroll through your content while
continuing to view the frozen cells.

1. Select the row below the row(s) you want to freeze. In our example, we want
to freeze rows 1 and 2, so we'll select row 3.

Data Manipulation & Analysis Notes

 2. Click the View tab on the Ribbon.

3. Select the Freeze Panes command, then choose Freeze Panes from the drop-
down menu.

4. The rows will be frozen in place, as indicated by the gray line. You can scroll
down the worksheet while continuing to view the frozen rows at the top. In our
example, we've scrolled down to row 18.

Data Manipulation & Analysis Notes

To freeze columns:

1. Select the column to the right of the column(s) you want to freeze. In our
example, we want to freeze column A, so we'll select column B.

2. Click the View tab on the Ribbon.

3. Select the Freeze Panes command, then choose Freeze Panes from the drop-
down menu.

Data Manipulation & Analysis Notes

 4. The column will be frozen in place, as indicated by the gray line. You
can scroll across the worksheet while continuing to view the frozen column on
the left. In our example, we've scrolled across to column E.

To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze
Panes from the drop-down menu.

If you only need to freeze the top row (row 1) or first column (column A) in the worksheet,
you can simply select Freeze Top Row or Freeze First Column from the drop-down menu.

Data Manipulation & Analysis Notes

Other view options

If your workbook contains a lot of content, it can sometimes be difficult to compare different
sections. Excel includes additional options to make your workbooks easier to view and
compare. For example, you can choose to open a new window for your workbook or split a
worksheet into separate panes.

To open a new window for the current workbook:

Excel allows you to open multiple windows for a single workbook at the same time. In our
example, we'll use this feature to compare two different worksheets from the same
workbook.

1. Click the View tab on the Ribbon, then select the New Window command.

Data Manipulation & Analysis Notes

 2. A new window for the workbook will appear.

3. You can now compare different worksheets from the same workbook across
windows. In our example, we'll select the 2013 Sales Detailed View worksheet
to compare the 2012 and 2013 sales.

Data Manipulation & Analysis Notes

If you have several windows open at the same time, you can use the Arrange All command
to rearrange them quickly.

To split a worksheet:

Sometimes you may want to compare different sections of the same workbook without
creating a new window. The Split command allows you to divide the worksheet into multiple
panes that scroll separately.

1. Select the cell where you want to split the worksheet. In our example, we'll
select cell C7.

Data Manipulation & Analysis Notes

 2. Click the View tab on the Ribbon, then select the Split command.

3. The workbook will be split into different panes. You can scroll through each
pane separately using the scroll bars, allowing you to compare different
sections of the workbook.

Data Manipulation & Analysis Notes

  After creating a split, you can click and drag the vertical and horizontal dividers to
change the size of each section. To remove the split, click the Split command again.

Data Manipulation & Analysis Notes