INTRODUCTION TO SPREAD SHEET

Spreadsheets are computer applications used to store, analyze, organize

and manipulate data in the rows and columns of a grid. The program operates
by taking in data, which can be numbers or text, into the cells of tables.
Microsoft Excel is currently the industry standard for spreadsheets and
worksheets. .

Creating New Worksheet

Three new blank sheets always open when you start Microsoft Excel. Below steps
explain you how to create a new worksheet if you want to start another new worksheet
while you are working on a worksheet, or you closed an already opened worksheet and
want to start a new worksheet.
Step 1 − Right Click the Sheet Name and select Insert option.
Step 2 − Now you'll see the Insert dialog with select Worksheet option as selected from
the general tab. Click the Ok button.
Now you should have your blank sheet as shown below ready to start typing your text.
You can use a short cut to create a blank sheet anytime. Try using the Shift+F11 keys
and you will see a new blank sheet similar to the above sheet is opened.

EDITING WORKSHEET

Modifying or adding text or using cut, copy, paste operations to an existing document is
known as editing.
• To edit data in a worksheet, first open the worksheet by clicking on File → Open.
• Next, move cursor to the cell, which you want to edit.
 • Note that content of the cell is displayed in formula bar as well.
• As you perform any operation, it is visible in the formula bar.
Cut
• Deleting unnecessary data from the cell is called cutting.
• In Microsoft Excel, you can cut text from one area of a worksheet and save or
paste that text anywhere. When you cut the text, it is stored on clipboard.
• If you want to cut any text or content from worksheet, first select the text or content
which you want to cut.
• To cut, Right Click → Cut.
• The shortcut key is "Ctrl + X".
Copy
Instead of retyping the same text, Copy function is used which reduces time and effort.
By using copy option, you can copy the text from one location to another. Information
stored on clipboard stays there until new information is either cut or copied. When you
execute cut or copy, you replace old information on the clipboard with whatever you have
just cut or copied.
• To copy the content, Right Click → Copy.
• Shortcut key is "Ctrl + C".
Paste
• Select your text to highlight it.
• First copy the text.
• Use mouse to move the cursor to desired position to paste the copied text.
• Click paste to insert the copied text in its new place.
• You can paste clipboard information as often as you like.
• To paste, Right Click & Paste.
• Shortcut key is "Ctrl + V".
Inserting and Deleting Rows, and Column
• To insert and delete rows and columns in an existing table, position the
cursor either before/after/above the spot where you want the insertion/deletion
to be.
• Right Click → Insert/Delete.
Insert column to the left
After finishing our table, in case we want to insert a column on the left hand side of a
particular column. We may use the following steps.
• Place the cursor where you want to insert a column in the table.
• Right click → Insert column to left.
Insert column to the right
After finishing our table, in case, we want to insert a column on the right hand side of a
particular column, follow below steps −
• Place the cursor where you want to insert a column into the table.
• Right click → Insert column to right.
Rows above
After finishing the table, in case we want to insert a row on top of a particular row, follow
below steps −
• Place cursor where you want to insert a row into the table.
• Right click → Insert row above.
Delete Rows
If you want to delete particular rows in a table, use the following steps −
• Select cells or rows you want to delete.
• Right click → Delete → Table Rows.
Delete column
If you want to delete a particular column in a table, use the following steps −
• Select column or cells you want to delete.
• Right click → Delete → Table Columns.
Changing Cell Height and Width
When we create a table, all the columns may have equal column width or different widths.
If you want to change the row height, simply select the row and right click. Then select
the Row Height option.

Some Common Operations in Microsoft Excel

Deleting a Column or a Row
Click on the column or row header to highlight the entire column or row to be
deleted. Right-click on any cell in the highlighted column or row. Click on Delete from
the menu.
Inserting a Column
Click on the column header directly to the right of where you want to insert a new
column. Right-click on any cell in the highlighted column. Click on Insert from the
menu.
Inserting a Row
Click on the row header directly below where you want to insert a new row. Right-click
on any cell in the highlighted row. Click on Insert from the menu.
Sorting
Click on the grey rectangle between the "A" column header and the "1" row header
in the upper left corner of the worksheet to select the entire worksheet. Click
on Data on the menu bar, and then Sort... Under Sort by, select the column to sort by
and select either Ascending or Descending order.
Displaying Formulas in the Worksheet
Hold down the CTRL key, and press the left single quote key (this key also has the
"tilde" (~) symbol). Repeat this operation to return to numerical display. Note:
spreadsheet data can be printed in either the formula display or the numerical display.
Entering Formulas
Click on the cell in which you want to enter a formula, and type the formula. Note: all
formulas must begin with an equal sign (=). Additional information about functions can be
obtained by clicking on the equal sign (=) next to the formula bar.
Adjusting Column Width
Click on the column header to highlight the entire column. Right-click on any cell in
the highlighted column. Click on Column Width... from the menu and enter a value for
the column width. Note: the column width of several columns can be adjusted simultaneously by
selecting multiple columns.

Adjusting Row Height

Click on the row header to highlight the entire row. Right-click on any cell in the
highlighted row. Click on Row Height... from the menu and enter a value for the row
height. Note: the row height of several rows can be adjusted simultaneously by selecting multiple rows.
Adjusting the Format of Numerical Data
Click and drag over the cells containing the numerical data. Right-click on any cell in
the highlighted area. Click on Format Cells... Click on the Number tab, and select
the Category: for the type of data in the cells. Note: to adjust the number of decimal places in
numerical data, select Number and enter the number of Decimal places: to use.

Justification of Cell Contents

Click on the cell. Click on the justification button (i.e., either left justify, center, or
right justify) on the Formatting Toolbar. Note: these three buttons are found to the right of the
bold, italics and underline buttons.

Justification of an Entire Column or Row

Click on the column or row header to highlight the entire column or row. Click on the
justification button (i.e., either left justify, center, or right justify) on the Formatting
Toolbar. Note: these three buttons are found to the right of the bold, italics and underline buttons.
Copying Cells, Columns or Rows
Select the cell(s), column(s) or row(s) by highlighting them. Right-click on any cell in
the highlighted area. Click on Copy. The selected cells will be copied (transparently) to
the Windows clipboard. Note: the Windows clipboard holds only the last copied data.
Pasting Cells, Columns or Rows
Select the cell(s), column(s) or row(s) into which you want to paste previously copied
data (i.e., from the Windows clipboard). Note: the selected area must be the same size as the
data you will be pasting. Right-click on any cell in the highlighted area. Click on Paste.
 Arithmetic Precedence
Microsoft Excel follows the rules of arithmetic precedence when evaluating formulas.

operations enclosed in parentheses are evaluated first; nested

()
parentheses are evaluated from the inside out

^ exponentiation

*
multiplication and division, evaluated from left to right
and /

+
addition and subtraction, evaluated from left to right
and -

Example

• the formula =5*3-4^2 evaluates as -1

• the formula =(5*(3-4)^2) evaluates as 5

Excel
Easy
#1 Excel tutorial on the net
Formulas and Functions

Enter a Formula | Edit a Formula | Operator Precedence | Copy/Paste a Formula | Insert

Function | Top 10 Functions
A formula is an expression which calculates the value of a cell. Functions are predefined formulas
and are already available in Excel.
For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.
For example, cell A3 below contains the SUM function which calculates the sum of the range A1:A2.

Enter a Formula
To enter a formula, execute the following steps.

1. Select a cell.

2. To let Excel know that you want to enter a formula, type an equal sign (=).

3. For example, type the formula A1+A2.

Tip: instead of typing A1 and A2, simply select cell A1 and cell A2.

4. Change the value of cell A1 to 3.

Excel automatically recalculates the value of cell A3. This is one of Excel's most powerful features!

Edit a Formula
When you select a cell, Excel shows the value or formula of the cell in the formula bar.

1. To edit a formula, click in the formula bar and change the formula.

2. Press Enter.
Operator Precedence
Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that
part will be calculated first. It then performs multiplication or division calculations. Once this is
complete, Excel will add and subtract the remainder of your formula. See the example below.

First, Excel performs multiplication (A1 * A2). Next, Excel adds the value of cell A3 to this result.

Another example,

First, Excel calculates the part in parentheses (A2+A3). Next, it multiplies this result by the value of
cell A1.

Copy/Paste a Formula
When you copy a formula, Excel automatically adjusts the cell references for each new cell the
formula is copied to. To understand this, execute the following steps.

1. Enter the formula shown below into cell A4.

2a. Select cell A4, right click, and then click Copy (or press CTRL + c)...

...next, select cell B4, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).

2b. You can also drag the formula to cell B4. Select cell A4, click on the lower right corner of cell A4
and drag it across to cell B4. This is much easier and gives the exact same result!

Result. The formula in cell B4 references the values in column B.

Insert Function
Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM.
The part between the brackets (arguments) means we give Excel the range A1:A4 as input. This
function adds the values in cells A1, A2, A3 and A4. It's not easy to remember which function and
which arguments to use for each task. Fortunately, the Insert Function feature in Excel helps you
with this.

To insert a function, execute the following steps.

1. Select a cell.

2. Click the Insert Function button.

The 'Insert Function' dialog box appears.

3. Search for a function or select a function from a category. For example, choose COUNTIF from the
Statistical category.
4. Click OK.

The 'Function Arguments' dialog box appears.

5. Click in the Range box and select the range A1:C2.

6. Click in the Criteria box and type >5.

7. Click OK.
Result. The COUNTIF function counts the number of cells that are greater than 5.

Note: instead of using the Insert Function feature, simply type =COUNTIF(A1:C2,">5"). When you
arrive at: =COUNTIF( instead of typing A1:C2, simply select the range A1:C2.

Top 10 Functions
Microsoft Excel is all about formulas and functions. Below you can find an overview of the 10 most
used Excel functions (+ extra tricks when creating formulas in Excel).

1. COUNT
To count the number of cells that contain numbers, use the COUNT function.

Note: use COUNTBLANK and COUNTA to count blank/nonblank cells in Excel. COUNTA stands for
count all.
2. SUM
To sum a range of cells, use the SUM function (see second image on this page). You can also use the
SUM function in Excel to sum an entire column.
Note: =SUM(5:5) sums all values in the 5th row. To create awesome SUM formulas, combine the
SUM function with other Excel functions.
3. IF
The IF function checks whether a condition is met, and returns one value if true and another value if
false.

Explanation: if the score is greater than or equal to 60, the IF function returns Pass, else it returns
Fail. Remember, to quickly copy this formula to the other cells, click on the lower right corner of cell
C2 and drag it down to cell C6.
4. AVERAGE
To calculate the average of a group of numbers, use the AVERAGE function (no rocket science here).
The formula below calculates the average of the top 3 numbers in the range A1:A6.
Explanation: the LARGE function returns the array constant {20,15,10}. This array constant is used
as an argument for the AVERAGE function, giving a result of 15.
5. COUNTIF
The COUNTIF function below counts the number of cells that contain exactly star + a series of zero
or more characters.

Explanation: an asterisk (*) matches a series of zero or more characters. Visit our page about
the COUNTIF function for more information and examples.
6. SUMIF
The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range
A1:A5 contain exactly circle + 1 character.
Explanation: a question mark (?) matches exactly one character. Visit our page about the SUMIF
function for more information and examples.
7. VLOOKUP
The VLOOKUP function below looks up the value 53 (first argument) in the leftmost column of the
red table (second argument). The value 4 (third argument) tells the VLOOKUP function to return the
value in the same row from the fourth column of the red table.

Note: visit our page about the VLOOKUP function to learn more about this powerful Excel function.
8. INDEX and MATCH
Use INDEX and MATCH in Excel to perform advanced lookups (if you're new to Excel, you can skip
this formula). For example, use INDEX and MATCH to perform a two-column lookup.
Note: the array formula above looks up the salary of James Clark, not James Smith, not James
Anderson. Learn how to use INDEX and MATCH in Excel and impress your boss.
9. MIN and MAX
To find the minimum value, use the MIN function. To find the maximum value, use the MAX function.
It's as simple as it sounds.

Note: visit our chapter about statistical functions to learn much more about Excel and Statistics.
10. SUMPRODUCT
To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel's
powerful SUMPRODUCT function.
 Explanation: the SUMPRODUCT function performs this calculation: (2 * 1000) + (4 * 250) + (4 * 100)
+ (2 * 50) = 3500. The SUMPRODUCT function is an extremely versatile function and can produce
the same result as many built-in functions in Excel and even array formulas!

MS Excel: Charts
In Microsoft Excel, a chart is often called a graph. It is a visual representation of data from a worksheet that
can bring more understanding to the data than just looking at the numbers.

A chart is a powerful tool that allows you to visually display data in a variety of different chart formats such
as Bar, Column, Pie, Line, Area, Doughnut, Scatter, Surface, or Radar charts. With Excel, it is easy to
create a chart.

Here are some of the types of charts that you can create in Excel.
 Bar Chart

• How to create a bar chart in Excel 2016 | 2010 | 2007

Column Chart

• How to create a column chart in Excel 2016 | 2010 | 2007

 Pie Chart

• How to create a pie chart in Excel 2016 | Excel 2007

Line Chart

• How to create a line chart in Excel 2016 | Excel 2007

 Advanced Charting
• Create a column/line chart with 8 columns and 1 line in Excel 2003
• Create a chart with two Y-axes and one shared X-axis in Excel 2007

Pivot Chart
Insert Pivot Chart | Filter Pivot Chart | Change Pivot Chart Type
A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are
connected with each other.
Below you can find a two-dimensional pivot table. Go back to Pivot Tables to learn how to create this
pivot table.

Insert Pivot Chart

To insert a pivot chart, execute the following steps.

1. Click any cell inside the pivot table.

2. On the Analyze tab, in the Tools group, click PivotChart.

The Insert Chart dialog box appears.

3. Click OK.

Below you can find the pivot chart. This pivot chart will amaze and impress your boss.

Note: any changes you make to the pivot chart are immediately reflected in the pivot table and vice
versa.

Filter Pivot Chart

To filter this pivot chart, execute the following steps.

1. Use the standard filters (triangles next to Product and Country). For example, use the Country filter
to only show the total amount of each product exported to the United States.
2. Remove the Country filter.

3. Because we added the Category field to the Filters area, we can filter this pivot chart (and pivot
table) by Category. For example, use the Category filter to only show the vegetables exported to each
country.

Change Pivot Chart Type

You can change to a different type of pivot chart at any time.

1. Select the chart.

2. On the Design tab, in the Type group, click Change Chart Type.
3. Choose Pie.

4. Click OK.

Result:
Note: pie charts always use one data series (in this case, Beans). To get a pivot chart of a country,
swap the data over the axis. First, select the chart. Next, on the Design tab, in the Data group, click
Switch Row/Column.

What is Excel consolidate?

The consolidate function in Excel allows an analyst to combine information from multiple workbooks into
one place. The Excel consolidate function lets you select data from its various locations and creates a
table to summarize the information for you.

How to consolidate data in Excel

We’ve created a step-by-step guide to help you create your own consolidation by combining similarly
organized data across multiple worksheets and workbooks.
The screenshots below will help you see an example of how to use the Excel consolidate function:
Step 1: Open all files (workbooks) that contain the data you want to consolidate.
Step 2: Ensure the data is organized in the same way (see example below).
Step 3: On the Data ribbons, select Data Tools and then Consolidate.
Step 4: Select the method of consolidation (in our example, it’s Sum).
Step 5: Select the data, including the labels, and click Add
Step 6: Repeat step 5 for each worksheet or workbook that contains the data you need included
Step 7: Check boxes “top row”, “left column”, and “create links to data source” (note you don’t have to tick
these boxes if you don’t want labels or don’t want live links) and click the OK button.
Why use the data consolidation function?
There are many reasons a financial analyst may want to use the Excel consolidate function. One example
would be combining budgets from various departments into one company-wide budget. This may be
common for an analyst or manager working in financial planning and analysis (FP&A) or
other accounting functions.