Automatically Update Data

Let’s say you update the data on one of your worksheets a lot. You can actually make that updated data appear on a second worksheet without having to type it twice! Here’s how on either a PC or Mac:

The First Steps

1. Name your first worksheet and type the data in it if you haven’t already. To name a worksheet, double-click on its tab at the bottom of your screen and type the name you want. The press the Enter key on a PC or Return if you have a Mac.
2. Click on the worksheet in the same workbook where you want another copy of the data to appear. Name that worksheet as well.
3. Go back to the first worksheet, and write down the name of the first cell (perhaps in the upper left) you want to copy. A cell’s name is taken from the column’s letter at the top and the row number on the left. For instance, the upper leftmost cell is called A1.

1. Now go to your second worksheet. Click in the cell where you want the data to appear, say B2.
2. Click in the Formula Bar at the top of your screen. The Formula Bar is the long white field at that has an fx to the left of it.
3. Type an equals sign (=).
4. Type the name of the first worksheet.
5. Type an exclamation point (!) after it. If you want to copy cell A1 and your first worksheet is called Worksheet1, your formula would look like this so far: =Worksheet1!
6. Next type the name of the original cell, the one you might have written down. If that cell was A1 you would now have: =Worksheet1!A1
7. Hit the Enter key on a PC or Return if you have a Mac. The data from the original cell should appear.

1. Now let’s test the updating part. Go back to your first worksheet, and type something different in that original cell.
2. Click on the second worksheet and see if the other cell was updated with the new data too. If so you did it right!

Copying and Updating Multiple Cells

But say you have multiple cells that need updating. No worries. If the cells are next to each other, you can use the Fill Handle to copy your original formula to them. Then they’ll update too! Here’s how to do this:

1. Make sure you’re on the second worksheet. Click on that single cell that has your data in it.
2. You should see a teeny tiny square on the lower right-hand side of the cell. On a PC it’s black, and on a Mac it’s probably blue (just like the color of the cell outline). Move your mouse on top of that square but don’t click. You should see a small black cross.
3. Now press and drag your mouse, either to the right or down, across the cells to copy the original formula. The data from the first worksheet should appear in them and update just like the first!

If these instructions helped you, I love little donations. They help me keep writing these tips. Also, I tutor remotely all over the world. If you’re interested click here for more information.

Three Ways to Easily Add Numbers

Adding a column or row of numbers couldn’t be easier in Excel. All you need to do is use the AutoSum feature. There are three ways to complete this very simple formula. To illustrate I’m going to use the following spreadsheet representing the number of BMWs sold by three employees at a dealership during a given year.

 

Here’s the first way:

1. Type a column of numbers, such as B4 through B15 under the name Juanita.
2. Click in cell B16.
3. Click on the AutoSum button on Excel’s Home tab. It’s located near the right-hand side in the Editing group. Be sure to actually click on the ∑ rather than the black arrow next to it.
4. Press the Enter key. You would see the total (85).

Here’s the second way:

1. Type another column of numbers, such as the numbers in cells C4 through C15 shown above under Josephine.
2. Click in cell C16.
3. This time click on the other AutoSum button. It’s on the Formulas tab near the left-hand side in the Function Library group.
4. Press the Enter key again.

The third way:

You could use a keyboard shortcut instead. Type another column of numbers and click in cell D16. Then hold down the Alt key and press the “=” key. And of course, press Enter afterward.

Also, did you know you can look at your formula at any time? Just click on the cell, and look up in the Formula Bar. This is the long white bar located above the spreadsheet and below the Ribbon.

If you get an inkling to see your formula in the actual cell again, press the F2 key on your keyboard. Then press Enter to go back to your total.

If you’d like to learn more about Excel or the other Microsoft Office programs, I teach remotely all over the world. For more information please click here.

 

How to Easily Locate Formulas

Did you know you can find the formula you’re looking in Excel for by typing only a few characters? I’ll use the spreadsheet below as an example that might help you follow along. Let’s find out which employee at a car dealership sold the most BMWs each month and the number they sold. Here’s a sample spreadsheet we can work with:

1. You’d click in cell E4 and type an “=”.
2. Then you’d type the letter “m”. A box would pop up listing all the formulas starting with that letter.
3. You’d double-click on the word “MAX.”
4. Next you’d select cells B4 through D4. You’d see a dotted line around them.
5. Last, you’d hit the Enter key. The number 7 would appear.

I hope you were able to follow that! Maybe you’ll now be able to apply it with other formulas in your own spreadsheets.

If you’d like to learn more about Excel or the other Microsoft Office programs, I teach remotely all over the world. For more information please click here.

Beginning Excel – Versions 2003, 2004, 2007, and 2008

Here is a self-paced tutorial I wrote so you can teach yourself the very very basics of Excel. Unfortunately, though, it doesn’t yet include instructions for Excel 2010.

Excel is a spreadsheet program. A spreadsheet is a rectangular table or grid of information, according to Wikipedia. Excel has the ability to both calculate numbers and depict graphs. The program’s official full name is actually Microsoft Office Excel

Excel consists of small, rectangular boxes, called cells, organized in an orderly fashion into columns (vertically) and rows (horizontally) on a worksheet, the overall grid. Several worksheets can be contained in a single workbook.

In Excel 2003 and 2007 for the PC, as well as 2004 for the Mac, a workbook contains three worksheets by default. However, Excel 2008 for the Mac only contains one worksheet by default. You can always add more worksheets if you need them, as we’ll learn later.

There’s not much chance you’ll run out of space using this program. Each worksheet in Excel 2003 and Excel 2004 (for the Mac) contain 65,536 rows and 256 columns, with a total of 16,777,216 million cells altogether. The newer Excel 2007, for the PC, and 2008, for Macs, exceed even those limits. Excel 2007 and 2008 each have 16,384 columns and more than one million rows. Tell me how many cells there is altogether in each of those?

On each worksheet, the columns are identified by letters of the alphabet, and the rows by number. The location of each cell is defined by a single letter and address combination, called a cell address. A cell’s address could be A10 or G25, for instance. Open up your version of Excel, and try to find these particular cells on the worksheet in front of you. There are only 26 letters in the alphabet. Therefore you can see that, to the right of column Z, the column names start with AA, AB, etc.

Note for 2004: The program will first open to the Project Gallery window. You’ll see the Excel Workbook is highlighted, which is correct. Either click on Open or hit the Return key, and your workbook should open.

 

 

Typing in Cells

If you open a worksheet and click on a cell, you’ll notice a thick black borderaround it. This border, the cell pointer, signifies the active cell, the one you plan to work with.

You can type text, numbers, dates, or formulas in each cell. You can also format them just like you would in Microsoft Word, For instance, you can make the text bold, italicized, or underlined. You can also change the text font type or font size, or even center your entry within the cell. You can also change both the text and cell color.

Note for 2008: You might not be able to see your Formatting Toolbar, which contains the icons allowing you to do many of these things. To show the Formatting Toolbar, click on View at the top of your screen, bring your mouse arrow down to Toolbars, and click on Formatting. You’ll probably also see a Formatting Palette on the right. Ignore it for now.

 

 

 

Sample Worksheet – Part of an Expense Report:

 

It’s time to make your first spreadsheet. Let’s start with a hypothetical one calculating how much you might spend on office supplies. The first step is to title your worksheet.

To do this, click in cell A1 so you can see your cell pointer. Type Office Supply Expenses. Then click on the bold and underline icons near the top of your screen. (In 2008 the cell will pop out at you as you type, and you’ll be able to see all your text while you type and edit.)…

The remainder of this 20-page tutorial costs only 99 cents. By the end you’ll be able to:

• Create a simple spreadsheet
• Format the Spreadsheet – Center its Title, Change the Column Widths,  and more
• Calculate simple formulas
• Insert, Name, Move, and Copy Worksheets
• Insert Rows and Columns
• Format dates
• Use AutoFill to make entering data quicker
• Freeze Panes for more readability
• Sort a list
• Hide Rows and Columns