Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical calculations. Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical data. Microsoft Excel provides multiple features to perform various operations like calculations, pivot tables, graph tools, macro programming, etc. It is compatible with multiple OS like Windows, macOS, Android and iOS. An Excel spreadsheet can be understood as a collection of columns and rows that form a table. Alphabetical letters are usually assigned to columns, and numbers are usually assigned to rows. The point where a column and a row meet is called a cell. The address of a cell is given by the letter representing the column and the number representing a row.
1 Why Should I Learn Microsoft Excel? We all deal with numbers in one way or the other. We all have daily expenses which we pay for from the monthly income that we earn. For one to spend wisely, they will need to know their income vs. expenditure. Microsoft Excel comes in handy when we want to record, analyze and store such numeric data. Let's illustrate this using the following image.
Understanding the worksheet (Rows and Columns, Sheets,
Workbooks) A worksheet is a collection of rows and columns. When a row and a column meet, they form a cell. Cells are used to record data. Each cell is uniquely identified using a cell address. Columns are usually labelled with letters while rows are usually numbers.
2 A workbook is a collection of worksheets. By default, a workbook has three sheets in Excel. You can delete or add more sheets to suit your requirements. By default, the sheets are named Sheet1, Sheet2 and so on and so forth. You can rename the sheet names to more meaningful names i.e. Daily Expenses, Monthly Budget, etc.
A range in Excel is a collection of two or more cells. This chapter gives an
overview of some very important range operations.
Cell, Row, Column
Let's start by selecting a cell, row and column.
1. To select cell C3, click on the box at the intersection of column C and row 3.
2. To select column C, click on the column C header.
3 3. To select row 3, click on the row 3 header.
Range Examples A range is a collection of two or more cells. 1. To select the range B2:C4, click on cell B2 and drag it to cell C4.
2. To select a range of individual cells, hold down CTRL and click on each cell that you want to include in the range.
4 Fill a Range To fill a range, execute the following steps. 1a. Enter the value 2 into cell B2.
1b. Select cell B2, click on the lower right corner of cell B2 and drag it down to cell B8.
This dragging technique is very important and you will use it very often in Excel. Here's another example. 5 2a. Enter the value 2 into cell B2 and the value 4 into cell B3.
2b. Select cell B2 and cell B3, click on the lower right corner of this range and drag it down.
Excel automatically fills the range based on the pattern of the first two values. That's pretty cool huh!? Here's another example.
3a. Enter the date 6/13/2016 into cell B2 and the date 6/16/2016 into cell B3.
3b. Select cell B2 and cell B3, click on the lower right corner of this range and drag it down.
6 Move a Range To move a range, execute the following steps. 1. Select a range and click on the border of the range.
2. Drag the range to its new location.
7 Copy/Paste a Range To copy and paste a range, execute the following steps. 1. Select the range, right click, and then click Copy (or press CTRL + c).
2. Select the cell where you want the first cell of the range to appear, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).
Insert Row, Column
To insert a row between the values 20 and 40 below, execute the following steps. 1. Select row 3.
8 2. Right click, and then click Insert.
The rows below the new row are shifted down. In a similar way, you can insert a column.
9 Formulas and Functions
Enter a Formula Edit a Formula Operator Precedence Copy/Paste a Formula Insert Function
The definition of a formula
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.
10 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.
11 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.
12 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. 13 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!
14 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.
15 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.
16 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.
Important Excel shortcuts
Ctrl + P used to open the print dialogue window
Ctrl + N creates a new workbook
Ctrl + S saves the current workbook
Ctrl + C copy contents of current select
Ctrl + V paste data from the clipboard
17 SHIFT + F3 displays the function insert dialog window
