Excel is an electronic spreadsheet program for storing, organizing and manipulating data. Data goes into individual cells arranged by columns and rows in a worksheet; this collection of columns and rows is called 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 several formatting features that you can apply to individual cells, rows, columns, and entire tables of data.

Since each worksheet in Excel contains billions of cells, each cell has an address known as a cell reference that formulas, functions, charts, and other features use to refer to a specific cell.

## Entering Data into Your Worksheet

Entering data into worksheet cells is always a three-step process:

- Click the
**cell**where you want the data to go. - Type the data into the cell.
- 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 essential to enter the data into the correct worksheet cells. The formulas you'll input in subsequent steps use the cell references of the data entered above.

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.

## Widening Columns in Excel

By default, the width of a cell permits only eight characters to be displayed before that data spills over into the next cell to the right.

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 in the above image, such as the label **Deduction Rate** in **cell B3** and** Thompson A***.* in **cell A8** are truncated because the cells to the right (**B4** and **A9**) contain data.

To correct this problem so that the data is fully visible, you need to widen the columns containing that data (**Column A** and **B**). As with all Microsoft programs, there are multiple ways to resize columns, but using the mouse is probably the easiest.

### Widening Individual Worksheet Columns Using the Mouse

- Place the mouse pointer on the line between
**columns A**and**B**in the column header. - The pointer will change to a double-headed arrow.
**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.- 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 widened to show the entire contents of **cell A1**, the sheet would not only look odd, but it would also make it difficult to work with 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**, you could leave the title as is, spilling over into the cells to the right. Alternatively, Excel has a feature called merge and center which you'll use later in this tutorial to center the title over the data table.

## Adding the Date and a Named Range

It is common to add the date to a spreadsheet, quite often to indicate when it was last updated. Excel has a few **date functions** that make it easy to add a date into a worksheet. Functions formulas built-into Excel to make it easy to complete commonly performed tasks.

The **TODAY** function is easy to use because it has no arguments, which are the values required by most functions. It's also one of Excel's volatile functions, which means it updates itself every time the recalculates, which is usually every time a user opens the worksheet.

### Adding the Date with the TODAY function

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

- Click
**cell C2**to make it the active cell. - Click the
**Formulas**tab of the**ribbon**. - Click the
**Date & Time**option on the**ribbon**to open the list of date functions. - Click the
**Today**function to bring up the**Formula Dialog Box**. - Click
**Done**in the box to enter the function and return to the worksheet. - The current date should appear in
**cell C2.**

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

If a row of hashtag symbols appear in **cell C2** instead of the date after adding the **TODAY** function to that cell, it is because the column is too narrow 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 column. Data that is formatted as a number, such as currency, dates, or time, instead 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

To make a range of data easier to identify you can give it a name. You can use **named ranges **as a substitute for cell reference 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, we will give **cell C3 **the name **rate** to identify the deduction rate applied to employee salaries. Then we'll use the named range in the **deduction formula **that will be input in **cells C6** to **C9** of the worksheet.

- Select
**cell C3**in the worksheet. - Type
**rate**in the**Name Box**and press the**Enter**key on the keyboard. **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.

## Entering the Employee Deductions Formula

Excel formulas allow you to perform calculations on numerical data in a worksheet. You can use formulas 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 goes into the cell where you want the answer to appear.
- To complete the formula press 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 one created in the previous step.

In a formula, a named range function the same as a cell reference but it's helpful when dealing with 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.

## Employee Deductions Formula Syntax

The first formula created in **cell C6** will multiply the **Gross Salary** of the 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 type the above formula into **cell** **C6** and have the correct answer appear, it is better to use pointing to add the cell references which 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.

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

## Entering the Net Salary Formula

In this example, the Net Salary formula goes 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** looks like this:

=B6-C6

- Click
**cell D6**to make it the active cell. - Type the equal sign (
**=**) into**cell D6**. - Click
**cell B6**with the mouse pointer to add that cell reference to the formula after the equal sign. - Type a minus sign (
**-**) in**cell D6**after the cell reference. - Click
**cell C6**with the mouse pointer to that cell reference to the formula. - Press the
**Enter**key on the keyboard to complete the formula. - 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 are in only one cell each in the worksheet – **C6** and **D6** respectively. As a result, the sheet is currently complete for just one employee - **B. Smith**.

Rather than going through the time-consuming task of recreating each formula for the other employees, most of the time, you can copy it 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 we've used in the preceding formulas are relative cell references, which is the default type of cell reference in Excel, and make copying formulas as straightforward as possible.

One of the most common methods for copying cell contents is by using the **Fill Handle. **

## Copying Formulas with the Fill Handle

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

Here's how to use the **fill handle** to copy both the **Deduction** and** Net Salary **formulas from **cells C6** and **D6** down to **cells C9** and **D9**.

- Highlight
**cells C6**and**D6**in the worksheet. - Place the mouse pointer over the black square in the bottom right corner of
**cell D6**; it**+**). - Click and hold down the left mouse button and drag the fill handle down to
**cell D9**. - 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.

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

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

### Adding the Currency Symbol

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

## Applying Cell Formatting in Excel

Cell formatting options including bolding text or numbers, changing data alignment, adding borders, and using the merge and center feature to improve the appearance of the data in a cell.

### Adding Bold Formatting

- Select
**cell A1**to highlight it. - Click the
**Home**tab of the**ribbon**. - Click the
**Bold**formatting option as identified in the image above to bold the data in**cell A1**. - 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.

- Select
**cell C3**to highlight it. - Click the
**Home****tab**of the**ribbon**. - Click the
**Center**alignment option as identified in the image above to center the data in**cell C3**. - 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 multiple cells into one and centers the data across the new merged cell. Here's how to merge and center the worksheet title - **Deduction Calculations for Employees.**

- Select
**cells A1**to**D1**to highlight them. - Click the
**Home****tab**of the**ribbon**. - Click 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.**

- Select the merged
**cell A1**to**D1**to highlight it. - Click the
**Home tab**of the**ribbon**. - Click the down arrow next to the
**Border**icon. - Click the
**Bottom Border**option in the menu to add a border to the bottom of the merged cell. - Repeat the above sequence of steps to add a bottom border to
**cells A5**to**D5**and**cells****A9**to**D9**.