Formulas in Excel - cell references

Spreadsheets are used to store data, they are also used to make calculations. When you enter a formula into a spreadsheet, the best way to make a formula is to use a cell reference. Using a cell reference allows you to change the data and Excel will recalculate for you without having to change the formula.

Let me know if you need exact information on how to write a formula, but using a cell reference in a formula will look like this

=A1+A2+A3+A4 instead of =2+5+7+9

Excel has a Sigma button - which simplifies making a formula for working with numbers. With OpenOffice, the autosum is still the Sigma sign, but you don't see it until you are in the formula bar.

Auto Complete - Excel and Open Office

Excel and Open Office have very similar features in the AutoComplete. This is to help you enter data quickly. This is different from the Appleworks Spreadsheet program. When you are entering data in a column, if AutoComplete is turned on, if you start typing something that looks as if you have entered it before, it will come up in a black box. Click on the black box and it will enter it for you.

This only seems to work for information in the columns, not not rows.

If you want to AutoFill in Open Office, say you are making a list of predictable titles, like the days of the week, you would type in the first one. Then click out of that cell, click again in that cell and a black box will show up around that cell. There is a small black box that shows on the lower right side of the box. Pull that box down as far as you want and it will "magically" fill in with a series.

The Microsoft Office has a similar feature, but the fill box doesn't show as clearly as in Open Office. When you hold down near the lower right side of the cell, it does change to a fill box.

Sorting in Tables and Spreadsheets

As I was making a spreadsheet with dates, I realized that I had not put them in the correct order. Fortunately, you can sort in a spreadsheet by date, alphabetical order or numerical order. After your data is in the spreadsheet, you select the column that you want to sort. If there are numbers or information that need to stay together, then you need to select both columns before you do the sort. In Excel, you find the sort under the Data heading.

In Open Office, you will find the same exact sorting ability under tools.

It turns out that you can also sort lists on the word processing side of these programs. In Open Office, you can sort just a list in a document. If you are in Microsoft Word, your data would need to be in a table. If you find another way to sort in Word, please let me know. Thanks.

Gridlines

This has come up more than once in the lab. Today, a visitor to the lab wanted to just drop in and print a grid to use. He knew to open the spreadsheet and then was surprised that when he printed, there were only blank sheets.

Well, I remembered from other teachers having this need to print a grid - that there is one thing you need to know.

On Microsoft Office -

Go to File - Page Setup - and then check what you do want to print.

On Open Office - the setup is slightly different. You go to Format - Page - Sheet and then check what you want to have printed.

This is a very simple tip, but one that I get asked often. If there is nothing on the page, you need to put a period at the very end cell to get the gridlines to print for the whole page.

Correct the dictionary

If your dictionary in Word or Open Office has been corrupted by well meaning students that while checking their document add the incorrectly mispelled word to the dictionary.

Go to Tools - Choose spelling and grammar. Click the Dictionaries button and click edit. Edit the list of words, deleting the incorrectly spelled words and then close the file. Then select options from the tools, click the spelling and grammar tab and then put a check next to check spelling automatically. Otherwise, you will have turned off the automatic spell check as you type. If you want it turned off, this is where you would turn it off.

Trust me, this is something you could do almost anytime in our computer lab as I have people who love to click before reading!

Fractions in Microsoft Office

Judy E. came and found the next problem we need to solve in Microsoft. She was typing a paper that required fractions and she wanted to type the fractions so the children could read them the way they are used to seeing them. Some fractions automatically format correctly in Microsoft Office - 1/2, 1/4 and 3/4 - but if you want anything else, they type just the way these fractions appear in this text.
If you want them to look more professional, here is the way to do it in Word.

Method 1: Use an Equation (EQ) Field
To insert a fraction as an equation field, follow these steps:
1. On the Insert menu, click Field.
2. In the Categories list, click to select Equations and Formulas.
3. In the Field Names list, click to select Eq and then click Options.
4. In the Switches list, select \F(,) and then click Add to Field.
5. In the box with the EQ \F(,) text, type the numerator of the fraction in before the comma and the denominator for the fraction after the comma within the parenthesis.
For example, for a fraction of 1/2, change the EQ \F(,) field to the following:
EQ \F(1,2)
6. Click OK to close the Field Options dialog box.
7. Click OK to close the Field dialog box.
Your fraction field should be inserted into your document. If you do not see the fraction, but see {EQ \F(1,2)} instead, then press ALT+F9 (Windows) or OPTION+F9 (Macintosh) to turn the field codes off.
If you are trying to do this in Open Office - you are in luck. They have an equation editor on it. You go to insert - object -formula - and the fraction is made. I have a graphic of what it will look like. In this case, in my humble opinion, Open office wins. I know there is a plug -in you can get for Microsoft Office to do this, but it comes in Open Office.