Excel Made Easy
Are spreadsheets - or worksheets - a bit of a mystery to you? Well, you CAN master MS Excel! Below we discuss what a worksheet is and how to work with worksheet cells; we list many examples of the most common math formulae and functions you will use; and we provide information on how to work with multiple worksheets and workbooks. Print this document and use as a tutorial.
If you are looking for information on Excel formulas and functions, please see the separate article Excel Formulas and Functions.
The Worksheet, Cells, and Entering Data
Have you ever played Bingo? Well, a spreadsheet or worksheet is like a Bingo card! The columns are represented by letters and the rows by numbers. The first “cell” is A1. As you move horizontally, the next cell is B1, then C1, etc. These cells form a row. If you move vertically from A1, the next cell is called A2, then A3, etc. These cells form a column. When you want to perform a mathematical function (add, divide, etc.) on the contents or value of a cell, you reference the cell in the formula with this 2-digit “Bingo” name.
When you are in one cell and want to move to another cell, you can use the applicable arrow key on your keyboard. You may also use the Enter key to move to the cell below, and you may use the Tab key to move to the cell to the right. To ‘highlight’ or select an entire row of cells, click on the row number. To ‘highlight’ or select an entire column, click on the column letter.
You can enter text, numbers, formulae, or functions in a cell. You can assign a particular color or patten to a cell. You can insert an image into a cell. You will notice that as you enter data into a cell, the data is also displayed in the “formula bar” at the top of the worksheet above the column letters. If you want to replace the contents of a cell, place your cursor in the cell and type. However, if you want to change or edit the value in a cell, you may do so in two ways. You can click on the cell and then place your cursor in the formula bar and type your changes. Or you can double-click in the cell and edit directly inside the cell - a better choice in my opinion.
Adding and Deleting Rows and Columns
When you insert a new row in a spreadsheet, right-click on a row number, and click Insert. Excel always inserts the row ABOVE the row you click on, so right-click the row below where you want the new row. To delete a row, right-click on the row number, and choose Delete.
When you insert a new column in a spreadsheet, right-click on a column letter and click Insert. Excel always inserts the column to the LEFT of the column you click on, so right-click the column to the right of where you want your new column. To delete a column, click on the column letter, right-click, and choose Delete.
How to Format Cells
Each cell in a worksheet can be individually formatted with many properties. You can also highlight multiple cells and format them to become merged into one large cell. It is important to format the cells properly. If you are entering currency, you need to format the cell for currency and also tell Excel how it should present the value. Should Excel show cents? Or round up/down to the nearest dollar? Before we discuss the formatting options, be aware that you can highlight a group of cells and format them together to save time. For example, if you know one or more columns will contain currency values, highlight the cells or columns, right-click, click Format, and make your formatting choices. If you want text, click the cell or group of cells and specify text. It’s a very good idea to format your cells BEFORE you start entering any data. Excel can do funny things if you change formats midstream. Some numbers won’t display correctly and you’ll have to reenter them. And some functions like =SUM just won’t work.
When you right click in a cell and click Format Cells..., you will see a window with six tabs:
The first tab is Number: Here is where you specify how you want Excel to display your data. If you are entering dates, click Date and then choose mm/dd/yy, or another date format. Once done, if you enter 4/1/8 in the cell, Excel automatically formats it to 04/01/07 if that is the format you chose. Since Excel defaults to the current year, you can just enter 4/1 or 4-1 and Excel will display the full date in the format you selected as soon as you move to another cell!
The second tab is Alignment: This is how you ‘pretty up’ a spreadsheet. Do you want the data in the cell(s) centered? Indented? If you want to merge a group of cells together, here is where you specify that option. You really need to play with these formatting options to see how they work.
The third tab is Font: and the usual options are presented.
The fourth tab is Border: You might want a border around an individual cell. Or if you have merged some cells together, you may want to put a border around this new, large cell for emphasis. You can color and style your border, and you can also choose how many and which sides of the cell or group of cells you want a border on.
The fifth tab is Patterns: Again, this is to ‘pretty up’ your spreadsheet or to make it more readable. Sometimes I will shade every-other row if I have a crowded worksheet. You can do this by choosing a light gray color, or perhaps a very mild pattern.
The last tab is Protection: In Excel, you have an option of protecting a workbook or individual worksheet with a password. Make sure you choose a password that you know you will always remember! You protect a worksheet via the Menu List at the top of the window. (In Excel 2003 and earlier, click Tools. In Excel 2007, click the Review tab.) When you protect a worksheet, you cannot change or delete the contents of any cell that is “locked” and you cannot see a formula in a cell formatted as “hidden.” It is via this tab on the Formatting window that you specify WHICH cells you want locked and which cells you want the formula hidden. By default, all cells are locked and no formulae are hidden.
Why would you want to use this feature? When you are editing a worksheet full of formulae, you don’t “see” the formula in a cell - Excel displays the value of the formula instead. Therefore, it is easy to accidently delete a formula. You can try Cntl-Z to ‘undo’ but you might not realize it right away. After spending 30 minutes recreating a complicated formula you accidently deleted, you may decide to protect your worksheets! If you have many formulae, then highlight the cells you don't want locked (headings, names, dates, blank cells, etc.) and unlock them via this tab. Or if you have just a few important formulae, click the upper left cell which will highlight the entire worksheet, and click Format/Protection/unlock. Then individually lock just those cells containing your formulae.
If you need to change a formula, you will need to unprotect the worksheet or workbook with your password (via the same path you used to protect it). Remember to use a password you know you will always remember or you won't be able to unlock the worksheet.
Copying and Moving Cells
To move or copy a cell or group of cells, click in the individual cell, or highlight the group of cells you wish to move or copy. Then right-click and choose Move or Copy. At this point, Excel will display an animated border around cells that have been cut or copied. Go to the area in the worksheet you want to move/copy the cell to and click in the cell. If moving/copying a group of cells, just click in the cell where you want the upper-left cell of your selection to be placed. Then right-click and choose Paste. To get rid of the animated border around your original selection, press the ESC key, or start typing in a new cell.
Propagating Contents of a Cell
To have the contents in one cell automatically copied to a range of cells either down or across, do the following: Enter the value in the first cell. Then click in an empty cell. Next, go back and highlight the first cell and all the other cells you want filled with this same value. Then click Edit/Fill and choose the direction to fill. A very handy shortcut if you want to fill in the down direction is to just press Cntl-D after highlighting the cells. Note: You can also fill multiple rows or columns in one action as long as the starting cells are aligned.
Deleting, Renaming, Inserting, Moving, and Copying Worksheets
When you click on the Excel icon, you are presented with a new ‘workbook’ which has 3 ‘worksheets’ listed at the bottom. They are labeled Sheet 1, Sheet2, and Sheet3. You can right-click on the worksheet name and rename it or delete it. If you right-click on a worksheet and choose insert, Excel will insert a new worksheet to the left of the current worksheet.
Often I want to make a copy of an an existing worksheet, which I will then modify. This is easy to do. Right-click on the name of the source worksheet and click Move or Copy. In the Move or Copy window, check the “create a copy” box, and then click the name of the worksheet that you want your sheet to be inserted before - or to the left of. Click OK. If you want the worksheet you’ve just made a copy of to be placed in a new workbook (a new Excel file), while you are still in the Move and Copy window, click on the drop-down arrow under “To Book:” and click (new book). Excel will put your worksheet in a new workbook, or file.
Use the same Move and Copy function if you want to change the order of your worksheets. Right-click on the name of the worksheet you want to move, and in the Move or Copy window, specify which worksheet you want your sheet to be before, or click (move to end). Click OK.
If you want to copy a worksheet from one workbook to another existing workbook, do the following: open the worksheet you want to copy, right-click the top left corner cell (which will highlight the entire worksheet), and click Copy. Then open your other workbook or Excel file, find or create an empty worksheet, right-click the top left corner cell (which will highlight that entire worksheet), and click Paste. Then go back to the first worksheet and press ESC to remove the animated border.
To move a worksheet from one existing workbook to another existing workbook, follow the instructions above for copying a worksheet to another workbook. Then when all is well on the receiving end, delete the original worksheet. I don’t recommend choosing Cut on the Edit menu. If you can’t get the Paste to work or something happens to your PC in the meantime, you could lose valuable data. It’s up to you.
If you have a lot of worksheets and get tired of scrolling to find the one you want, here is a shortcut you will love. Right-click on any one of the arrows to the left of the sheet tabs and you will get a menu box listing all of the sheet tabs. Click the one you want!
In Closing...
Excel error messages will display inside a cell and start with the pound sign (#). The most common error you will see will be ##### inside of a cell. Excel is merely telling you that your column is too narrow to display the number. Make your column wider by putting your cursor on the right side of the column heading and slightly dragging to the right.
- - - - - - -
For information and examples of Excel formulas and functions, please see the separate article Excel Formulas and Functions.
