Spreadsheets
- Using Access 2002 or Excel 2002 to Manage Your Data Microsoft article offering advice
Use AutoFill to Quickly Copy Formulas and Formatting in Excel
Would you like an easy way to extend a series of numbers in Microsoft Excel without typing each one individually? With AutoFill, you can quickly copy data, formulas, or formatting to adjacent cells.
-
Select the cells that you would like to copy.
-
Move the cursor to the bottom right corner of the highlighted cells.
-
Your cursor will then turn into a black plus sign.
-
Click and hold down the right mouse button and drag across the cells you want to fill. Release the mouse button, and when the shortcut menu appears, click Fill Series.
Test this out using the following examples:
|
A |
B |
C |
D |
E |
F |
G |
1 |
25 |
25 |
1 |
=B1+C1 |
Monday |
1-Apr-01 |
Your home town name |
2 |
25 |
35 |
2 |
||||
3 |
25 |
45 |
3 |
For columns A-C select all three rows of cells then grab the autofill handle
For Columns D-G select the cells in row 1, then grab the autofill handle.
*Note the formula in Cell D1 and how it updates. You will need to click in each cell and view the formula in the Formula bar.
There are a couple ways to have excel recalculate formulas.
-
From the Tools menu select Options
-
Click on the Calculations tab
-
Select the Automatic update if you wish to have Excel always update your data
-
Select Manual update if you wish to have Excel update formulas only when you tell it to
-
F9 - Calculate all worksheets in all open workbooks.
When a portion of a formula is selected, calculate the selected portion. You can then press ENTER or CTRL+SHIFT+ENTER (for array formulas) to replace the selected portion with the calculated value. -
SHIFT+F9 - Calculate the active worksheet.
-
CTRL+ALT+F9 - Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
-
CTRL+ALT+SHIFT+F9 - Rechecks dependent formulas and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
-
Freeze Column or Row Headings in Excel
Would you like to see the column headings on your Microsoft Excel spreadsheets no matter how far down you scroll?
- Select the row just below your column headings.
- On the Window menu, click Freeze Panes.
Every Row above and/or Column to the left of the cell you clicked in will remain visible as you move through the rest of the worksheet.
Have you ever wondered how to create a heading for a table that runs vertically along the side of a table instead of above it?
- Select the cell that contains your text as well as the surrounding cells that you want your title to span.
- On the Format menu, click Cells, and then click the Alignment tab.
- In the degrees text box, enter 90.
- Select the Merge cells text box and click OK.
Make Your Excel 97/2000 Printouts Fit the Page Width
When you print a worksheet, do you want it to fit the width of the paper, and take as many sheets of paper as required to print all the data? When you click Page Setup on the File menu, click the Page tab under Scaling, click Fit to, and select 1 page wide. In the second Fit to box for how tall you want the data, delete the number so the box is blank.
Adding (sum of) Time values in Excel
Add Time fields the same as you would any other numbers. For example, if you have a time of 2:10 in cell A1 and 2:15 in A2, you'll see 4:25 as the total when you add the times together.
You won't see what you expect if the values add up to more than 24 hours, If you added 9:00, 8:00, and 8:00, you'd see 1:00. This is because Excel is showing you the result in 24-hour notation ( 9 + 8 + 8 = 25 hours). To see a result in total hours, you need to do reformat the result. Instead of the h:mm format, use [h]:mm. The brackets around the "h" prevent the result from displaying in 24-hour notation. The same will work for minutes [m] 25 hours formatted as [m] will show a result of 1,500, (25× 60 = 1,500).
