Excel Made Easy - A Beginner's Guide
Author: Patricia Lynn
Are spreadsheets - or worksheets - a bit of a mystery to you? Well, you CAN learn Microsoft Excel! Below we discuss what a worksheet is and how to work with worksheets, cells, rows, columns, and workbooks.
For more information, please visit our tutorial on Excel Math Basics. Also, discover how easy it is to use Excel functions with our tutorials listed at right ... begin with the SUM Function.
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, formulas, 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: Before discussing the options on this tab, let's talk about the "Protection" feature of Excel. This feature allows the user to define certain cells as uneditable, or locked. Then, when the worksheet is in "Protect" status, the contents of these cells cannot be changed. This is a great feature if you want to prevent the accidental erasure of formulas and functions in a worksheet!
Protecting a cell's contents from accidental erasure requires two steps. First, the cell must be locked - which is specified with a checkbox on the Protection tab. Secondly, the worksheet (or workbook) must be "Protected." (We explain how to do that below.) If a worksheet has not been protected, a cell can be edited regardless of its locked or unlocked status on the Protection tab.
When a worksheet is protected, Excel will display a small error window if a user even clicks in a Locked cell. But data can be changed or deleted in unlocked cells. If you need to edit a locked cell, simply "Unprotect" the worksheet as described below. Just don't forget to Protect the worksheet again when you are finished!
All cells are locked by default, so there are two ways you can proceed. If you only need to unlock a few cells, unlock them individually: Click in the cell, right-click and choose "Format cells," and remove the checkmark by Locked on the Protection tab. Alternately, you can press and hold the Ctrl key while clicking in multiple cells before following the steps above.
If you only need a few cells locked for protection, the fastest way to accomplish this is to unlock all of the cells in one step, and then lock the individual cells that need protection. To unlock all of the cells, click in the top left cell, which highlights every cell in the worksheet. Right-click "Format cells," and and uncheck "Locked" on the Protection tab. Then lock the individual cells that need to be locked.
Once your worksheet's cells are locked to your satisfaction, protect the worksheet for the locking to take effect. When you protect a worksheet, you may enter a password if you wish. But if you forget the password, you won't be able to unprotect your Excel worksheet! Therefore, if you are the only one using the worksheet, just press OK without entering a password. Only consider entering a password if other folks will be using your worksheet.
To protect a worksheet or workbook in Excel 2007, click the Review tab, and then click Protect Worksheet or Protect Workbook. Click OK (entering a password first, if necessary). When a worksheet or workbook is already protected, the icons in the Review tab will say Unprotect Worksheet and Unprotect Workbook.
In earlier versions of Excel, click "Tools | Protection" and then click Protect Sheet or Protect Workbook. Click OK (entering a password first, if necessary). When a worksheet or workbook is already protected, the menu items will read Unprotect Sheet and Unprotect Workbook.
If you unprotect a sheet or book to make a change, don't forget to protect it again before you end your Excel session.
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.
We hope this article has been helpful. Cheers!
Please link to this tutorial using the following HTML:
<a href="http://www.keynotesupport.com/excel-for-beginners-guide.shtml">Excel Made Easy - a Beginner's Guide</a>
► Check out our featured article All About Your Credit Score!. Don't be tricked by websites selling products! Get the facts with our comprehensive article.
