Excel step by step basis tutorial

 Excel is an electronic spreadsheet program that is used for storing, organizing, and manipulating data. Data is stored in individual cells that are usually organized in a series of columns and rows in a worksheet; this collection of columns and rows is referred to as a table.

Spreadsheets programs can also perform calculations on the data using formulas. To help make it easier to find and read the information in a worksheet, Excel has a number of formatting features that can be applied to individual cells, rows, columns, and entire tables of data.

Since each worksheet in recent versions of Excel contains billions of cells per worksheet, each cell has an address known as a cell reference so that it can be referenced in formulas, charts, and other features of the program.

Topics included in this tutorial are:

• Entering the data into the table
• Widening individual worksheet columns
• Adding the current date and a named range to the worksheet
• Adding the deduction formula
• Adding the net salary formula
• Copying formulas with the Fill Handle
• Adding number formatting to data
• Adding cell formatting

01

of 08

Entering Data Into Your Worksheet

Entering data into worksheet cells is always a three-step process; these steps are as follows:

1. Click on the cell where you want the data to go.
2. Type the data into the cell.
3. Press the Enter key on the keyboard or click on another cell with the mouse.

As mentioned, each cell in a worksheet is identified by an address or cell reference, which consists of the column letter and number of the row that intersect at a cell's location. When writing a cell reference, the column letter is always written first followed by the row number – such as A5, C3, or D9.

When entering the data for this tutorial, it is important to enter the data into the correct worksheet cells. Formulas entered in subsequent steps make use of the cell references of the data entered now.

To follow this tutorial, use the cell references of the data seen in the image above to enter all the data into a blank Excel worksheet.

02

of 08

Widening Columns in Excel

By default, the width of a cell permits only eight characters of any data entry to be displayed before that data spills over into the next cell to the right. If the cell or cells to the right are blank, the entered data is displayed in the worksheet, as seen with the worksheet title Deduction Calculations for Employees entered into cell A1.

If the cell to the right contains data, however, the contents of the first cell are truncated to the first eight characters. Several cells of data entered in the previous step, such as the label Deduction Rate: entered into cell B3 and Thompson A. entered into cell A8 are truncated because the cells to the right contain data.

To correct this problem so that the data is fully visible, the columns containing that data need to be widened. As with all Microsoft programs, there are multiple ways of widening columns. The steps below cover how to widen columns using the mouse.

Widening Individual Worksheet Columns

1. Place the mouse pointer on the line between columns A and B in the column header.
2. The pointer will change to a double-headed arrow.
3. Click and hold down the left mouse button and drag the double-headed arrow to the right to widen column A until the entire entry Thompson A. is visible.
4. Widen other columns to show data as needed.

Column Widths and Worksheet Titles

Since the worksheet title is so long compared to the other labels in column A, if that column was widened to display the entire title in cell A1, the worksheet would not only look odd, but it would make it difficult to use the worksheet because of the gaps between the labels on the left and the other columns of data.

As there are no other entries in row 1, it is not incorrect to just leave the title as it – spilling over into the cells to the right. Alternatively, Excel has a feature called merge and center which will be used in a later step to quickly center the title over the data table.

03

of 08

Adding the Date and a Named Range

It is normal to add the date to a spreadsheet - quite often to indicate when the sheet was last updated. Excel has a number of date functions that make it easy to enter the date into a worksheet. Functions are just built-in formulas in Excel to make it easy to complete commonly performed tasks – such as adding the date to a worksheet.

The TODAY function is easy to use because it has no arguments – which is data that needs to be supplied to the function in order for it to work. The TODAY function is also one of Excel's volatile functions, which means it updates itself every time the recalculates – which is usually every time the worksheet is opened.

Adding the Date with the TODAY function

The steps below will add the TODAY function to cell C2 of the worksheet.

1. Click on cell C2 to make it the active cell.
2. Click on the Formulas tab of the ribbon.
3. Click on the Date & Time option on the ribbon to open the list of date functions.
4. Click on the Today function to bring up the Formula Builder.
5. Click Done in the box to enter the function and return to the worksheet.
6. The current date should be added to cell C2.

Seeing ###### Symbols instead of the Date

If a row of hashtag symbols appears in cell C2 instead of the date after adding the TODAY function to that cell, it is because the cell is not wide enough to display the formatted data.

As mentioned previously, unformatted numbers or text data spill over to empty cells to the right if it is too wide for the cell. Data that has been formatted as a specific type of numbers – such as currency, dates, or time, however, do not spill over to the next cell if they are wider than the cell where they are located. Instead, they display the ###### error.

To correct the problem, widen column C using the method described in the preceding step of the tutorial.

Adding a Named Range

A named range is created when one or more cells are given a name to make the range easier to identify. Named ranges can be used as a substitute for cell reference when used in functions, formulas, and charts. The easiest way to create named ranges is to use the name box located in the top left corner of the worksheet above the row numbers.

In this tutorial, the name rate will be given to cell C6 to identify the deduction rate applied to employee salaries. The named range will be used in the deduction formula that will be added to cells C6 to C9 of the worksheet.

1. Select cell C6 in the worksheet.
2. Type rated in the Name Box and press the Enter key on the keyboard
3. Cell C6 now has the name of rate.

This name will be used to simplify creating the Deductions formulas in the next step of the tutorial.

04

of 08

Entering the Employee Deductions Formula

Excel formulas allow you to perform calculations on number data entered into a worksheet. Excel formulas can be used for basic number crunchings, such as addition or subtraction, as well as more complex calculations, such as finding a student's average on test results and calculating mortgage payments.

• Formulas in Excel always begin with an equal sign ( = ).
• The equal sign is always typed into the cell where you want the answer to appear.
• The formula is completed by pressing the Enter key on the keyboard.

Using Cell References in Formulas

A common way of creating formulas in Excel involves entering the formula data into worksheet cells and then using the cell references for the data in the formula, instead of the data itself.

The main advantage of this approach is that if later it becomes necessary to change the data, it is a simple matter of replacing the data in the cells rather than rewriting the formula. The results of the formula will update automatically once the data changes.

Using Named Ranges in Formulas

An alternative to cell references is to used named ranges – such as the named range rate created in the previous step.

In a formula, a named range function the same as a cell reference but it is normally used for values that are used a number of times in different formulas – such as a deduction rate for pensions or health benefits, a tax rate, or a scientific constant – whereas cell references are more practical in formulas that refer to specific data only once.

Entering the Employee Deductions Formula

The first formula created in cell C6 will multiply the Gross Salary of employee B. Smith by the deduction rate in cell C3.

The finished formula in cell C6 will be:

= B6 * rate

Using Pointing to Enter the Formula

Although it is possible to just type the above formula into cell C6 and have the correct answer appear, it is better to use pointing to add the cell references to formulas in order to minimize the possibility of errors created by typing in the wrong cell reference.

Pointing involves clicking on the cell containing the data with the mouse pointer to add the cell reference or named range to the formula.

1. Click on cell C6 to make it the active cell.
2. Type the equal sign ( = ) into cell C6 to begin the formula.
3. Click on cell B6 with the mouse pointer to add that cell reference to the formula after the equal sign.
4. Type the multiplication symbol (*) in cell C6 after the cell reference.
5. Click on cell C3 with the mouse pointer to add the named range rate to the formula.
6. Press the Enter key on the keyboard to complete the formula.
7. The answer 2747.34 should be present in cell C6.
8. Even though the answer to the formula is shown in cell C6, clicking on that cell will display the formula, = B6 * rate, in the formula bar above the worksheet

05

of 08

Entering the Net Salary Formula

This formula is created in cell D6 and calculates an employee's net salary by subtracting the deduction amount calculated in the first formula from the Gross Salary. The finished formula in cell D6 will be:

= B6 - C6

1. Click on cell D6 to make it the active cell.
2. Type the equal sign ( = ) into cell D6.
3. Click on cell B6 with the mouse pointer to add that cell reference to the formula after the equal sign.
4. Type a minus sign( - ) in cell D6 after the cell reference.
5. Click on cell C6 with the mouse pointer to that cell reference to the formula.
6. Press the Enter key on the keyboard to complete the formula.
7. The answer 43,041.66 should be present in cell D6.

Relative Cell References and Copying Formulas

So far, the Deductions and Net Salary formulas have been added to only one cell each in the worksheet – C6 and D6 respectively. As a result, the worksheet is currently complete for only one employee - B. Smith.

Rather than going through the time-consuming task of recreating each formula for the other employees, Excel permits, in certain circumstances, formulas to be copied to other cells. These circumstances most often involve the use of a specific type of cell reference – known as a relative cell reference – in the formulas.

The cell references that have been entered into the formulas in the preceding steps have been relative cell references, and they are the default type of cell reference in Excel, in order to make copying formulas as straightforward as possible.

The next step in the tutorial uses the Fill Handle to copy the two formulas to the rows below in order to complete the data table for all employees.

06

of 08

Copying Formulas with the Fill Handle

The filehandle is a small black dot or square in the bottom right corner of the active cell. The fill handle has a number of uses including copying a cell’s contents to adjacent cells. filling cells with a series of numbers or text labels, and copying formulas.

In this step of the tutorial, the file handle will be used to copy both the Deduction and Net Salary formulas from cells C6 and D6 down to cells C9 and D9.

Copying Formulas with the Fill Handle

1. Highlight cells B6 and C6 in the worksheet.
2. Place the mouse pointer over the black square in the bottom right corner of cell D6 – the pointer will change to a plus sign (+).
3. Click and hold down the left mouse button and drag the fill handle down to cell C9.
4. Release the mouse button – cells C7 to C9 should contain the results of the Deduction formula and cells D7 to D9 the Net Salary formula.

07

of 08

Applying Number Formatting in Excel

Number formatting refers to the addition of currency symbols, decimal markers, percent signs, and other symbols that help to identify the type of data present in a cell and to make it easier to read.

Adding the Percent Symbol

1. Select cell C3 to highlight it.
2. Click on the Home tab of the ribbon.
3. Click on the General option to open the Number Format drop-down menu.
4. In the menu, click on the Percentage option to change the format of value in cell C3 from 0.06 to 6%.

Adding the Currency Symbol

1. Select cells D6 to D9 to highlight them.
2. On the Home tab of the ribbon, click on the General option to open the Number Format drop-down menu.
3. Click on the Currency in the menu to change the formatting of the values in cells D6 to D9 to a currency with two decimal places.

08

of 08

Applying Cell Formatting in Excel

Cell formatting refers to formatting options – such as applying bold formatting to text or numbers, changing data alignment, adding borders to cells, or using the merge and center feature to change the appearance of the data in a cell.

In this tutorial, the above-mentioned cell formats will be applied to specific cells in the worksheet so that they will match the finished worksheet.

Adding Bold Formatting

1. Select cell A1 to highlight it.
2. Click on the Home tab of the ribbon.
3. Click on the Bold formatting option as identified in the image above to bold the data in cell A1.
4. Repeat the above sequence of steps to bold the data in cells A5 to D5.

Changing Data Alignment

This step will change the default left alignment of several cells to center alignment.

1. Select cell C3 to highlight it.
2. Click on the Home tab of the ribbon.
3. Click on the Center alignment option as identified in the image above to center the data in cell C3.
4. Repeat the above sequence of steps to center align the data in cells A5 to D5.

Merge and Center Cells

The Merge and Center option combines a number of selected into one cell and centers the data entry in the leftmost cell across the new merged cell. This step will merge and center the worksheet title - Deduction Calculations for Employees.

1. Select cells A1 to D1 to highlight them.
2. Click on the Home tab of the ribbon.
3. Click on the Merge & Center option as identified in the image above to merge cells A1 to D1 and center the title across these cells.

Adding Bottom Borders to Cells

This step will add bottom borders to the cells containing data in rows 1, 5, and 9

1. Select the merged cell A1 to D1 to highlight it.
2. Click on the Home tab of the ribbon.
3. Click on the down arrow next to the Border option as identified in the image above to open the borders drop-down menu.
4. Click on the Bottom Border option in the menu to add a border to the bottom of the merged cell.
5. Repeat the above sequence of steps to add a bottom border to cells A5 to D5 and to cells A9 to D9.

Microsoft Excel beginners guide

 

What is Microsoft Excel?

Microsoft Excel is a program that provides worksheets comprised of rows and columns. Data can be stored in the worksheet, also called a spreadsheet, similarly to a Microsoft Word table, but the power of Excel is its ability to perform simple to complex mathematical calculations, and other functions. 

The Excel Worksheet (Spreadsheet) and Workbook

An Excel worksheet, or spreadsheet, is a two-dimensional grid with columns and rows. Look at the spreadsheet below. The column names are letters of the alphabet starting with A, and the rows are numbered chronologically starting with the number one. The cells in the first row are A1, B1, C1, and so on. And the cells in the first column are A1, A2, A3, and so on. These are called cell names or cell references.

We use cell references when creating math formulas or functions. For example, the formula to add the contents of cells B2 and B3 together is: =B2+B3.

Structure of a Microsoft Excel Worksheet (Spreadsheet)

The beginning of the Formula Bar can be seen in the area above Column D on our worksheet. The Formula Bar displays the contents of the selected cell. The Name Box is located in the area above Column A and displays the cell reference of the selected cell - the cell where the cursor is resting. In our spreadsheet above, the selected cell is C2. Notice that the column letter (C) and the row number (2) change color.

A workbook is a collection of worksheets or spreadsheets. When the Excel program is opened, a workbook opens with three blank worksheets. The names of the worksheets are displayed on tabs at the bottom of the Excel window. 

How to Move From Cell to Cell

The arrow keys can be used to move left, right, up, and down from the current cell. Press the Enter key to move to the cell immediately below the current cell, and press the Tab key to move one cell to the right.

How to Select Cells

There are a variety of ways to select cells in an Excel spreadsheet:

• To select one cell, click on the cell.
• To select one or more rows of cells, click on the row number(s).
• To select one or more columns of cells, click on the column letter(s).
• 
  To select a group of contiguous cells, click in one corner cell and drag the mouse to the opposite corner. In the image at right, we have selected cells A1 through B5 (written A1:B5 in formulas).
• To select multiple cells that are not contiguous, press and hold the Ctrl key while clicking in the desired cells.
• To select every cell in the worksheet, click in the upper right corner of the worksheet to the left of "A."

How to Enter Data into Cells

To enter data into a cell, just click in the cell and begin typing. What you type also displays in the Formula Bar. When entering dates, Excel defaults to the current year if the year portion of the date is not entered.

Each cell has a specific format. In Excel, a cell's format tells Excel how to display the contents of the cell. A cell's format may be different than the cell contents. You may edit cell contents from the Formula bar, or from directly inside the cell. To edit from the Formula Bar, select the cell and click inside the Formula Bar. When done typing, either press the Enter key or click inside another cell. To edit directly inside a cell, either double click inside the cell, or select the cell and press the F2 key.

For example, you may have entered 8.9521 in a cell. But if the cell was formatted to display only two decimal places, Excel will display 8.95 in that worksheet cell. However, Excel will still use the real cell value that you entered, 8.9521 when performing calculations that involve that cell.

How to Propagate Cell Contents

There are multiple ways to propagate or fill data from one cell to adjacent cells. Let's begin with two popular keyboard shortcuts that allow us to fill down, or fill to the right:

• To fill adjacent cells with the contents of the cell above, select the cell with the data and the cells to be filled and press Ctrl + D (the Ctrl key and the D key) to fill down.
• To fill adjacent cells with the contents of the cell to the left, select the cell with the data and cells to be filled and press Ctrl + R (the Ctrl key and the R key) to fill to the right.

To propagate in any direction, use the Fill Handle. Click in a cell with data to be copied, hover the cursor over the cell's lower right corner until the cursor changes to a thin plus sign (+) or a dark square, and drag up, down, left, or right to fill the cells.

If the data to be copied is a date, number, time period, or a custom-made series, the data will be incremented by one instead of just copied when the Fill Handle is used. 

How to Move and Copy Cell Contents

To move cell contents, right-click in the selected cell and click Cut; then right-click in the new location and click Paste. Similarly, to copy cell contents, right-click in the selected cell and select Copy, and paste in the new cell.

You can copy the contents of a cell as described above but paste and fill multiple adjacent cells. Just highlight the block of cells you want to paste in: click and hold the left mouse button in one corner of the cell range. Still holding the mouse button down, swipe the cursor over to the opposite corner until just the cells you want to be filled are highlighted. Then right-click and click Paste.

To remove the animated border around the original cell, press the ESC key, or start typing in a new cell.

How to Add and Delete Rows and Columns

To insert a new row in a spreadsheet, right-click on a row number, and click Insert. Excel always inserts the row ABOVE the row that was clicked on. If you want to continue inserting rows, press the F4 key to insert each additional row.

To delete a row, right-click on the row number, and click Delete. Contiguous rows can be deleted by highlighting them before clicking Delete. And non-contiguous rows can be selected by pressing and holding the CTRL key before clicking Delete. Don't press the Delete key on the keyboard unless you just want to delete the cell contents and not the actual row.

To insert a new column, right-click on a column letter and click Insert. Excel always inserts the column to the LEFT of the column that was clicked on. As with rows, if you want to add additional columns after inserting the first column, press the F4 key.

To delete a column, right-click on the column letter, and click Delete. Contiguous columns can be deleted by highlighting them before clicking Delete. And non-contiguous columns can be selected by pressing and holding the CTRL-key. Don't press the Delete key on the keyboard unless you just want to delete the cell contents and not the actual column.

How to Lock Cells and Protect a Spreadsheet

There are two steps to preventing important cell content from being accidentally overwritten or deleted. First, the cell must be locked. Second, the worksheet must be protected. If you have any valuable data or complex formulas that you do not want to lose, and data DOES get accidentally erased(!), then you owe it to yourself to learn which cells to lock and which worksheet protection options to take.