CRITICAL CORE OF EXCEL

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™

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.

ROWS, COLUMNS AND REFFERENCES

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/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 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

Speaker notes: Writing information inside a cell will also show it in the formula bar. There are two ways of editing the information inside a cell: Double-click (or F12) on the cell to edit the content Selecting the cell and editing the formula bar

THE STATUS BAR

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. STATUS BAR

CUSTOMIZING THE 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

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

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

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

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 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

Speaker notes: All excel formulas start with “=“ and they can be auto-filled by pressing “TAB” prior to selecting the one we want.

Excel functions can also be used to solve mathematical equations.

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

For example, if we were to try to find the total SUM for the values in the A1:A25 range

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)

Erasing and modifying parts of the formula

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

Applying formulas on multiple cells will also lead to changes in the reference of that formula.

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.

Speaker notes: The formula apply will stop on any blanks found in the column

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.

Speaker notes: The formula apply will stop on any blanks found in the column

Blocking References

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.

Types or references:

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 Reference

Altering references:

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;