Prevent Excel from turning fractions into dates
Thursday, June 18, 2009 03:01 PM
Excel can recognize cell entries as fractions if the appropriate fraction format is applied to the cell before data is entered.
Microsoft Excel
Prevent Excel from turning fractions into dates
If you don't apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date.
For example, if you enter 1/50 into a cell, Excel will convert it to Jan-50. If you then try to reformat the same cell to a fraction, Excel will convert it to 18264, the internal number that represents Jan-50.
In that case, if you want Excel to recognize your cell entries as fractions, you must apply the appropriate fraction format to the cell before you enter the data.
For example, say you are importing a column of data from an Access database table that contains fractions such as 1/50, 2/70, and 30/65.
You will need to format the column of cells containing the data to an appropriate fraction format before importing.
Follow these steps:
- Select the column that will contain the fractional data.
- Right-click the selection.
- Select Format Cells.
- In the Number tab, under Category, select Fraction.
- Under Type, select Up To Two Digits (21/25), then click OK.
Now, when you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted to display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.
If you won't be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list and then click OK.
Microsoft PowerPoint
Printing multiple slides on a single page
Cam Howey, one of our Canadian members, recently reminded me that it's easy to print multiple slides on a single piece of paper. The default is one slide per one sheet of paper, but as Cam reminded me, doing so isn't economical or efficient, and it certainly isn't green.
Instead, unless you have a specific reason for doing otherwise, print multiple slides on a single sheet of paper as follows:
- From the File menu, choose Print.
- Click the Properties button (for the printer).
- Every printer is different, but you should find a Pages Per Sheet option on a Layout tab (or something similar).
- Choose the appropriate number of pages per sheet. Within this context, one page equals one PowerPoint slide. Most likely, your printer's properties page will update a sample page, so you can get an idea of how well the multiple slides will work on a single sheet.
- Click OK twice to print the slides, accordingly.
Be quick to cancel the print task if the first page doesn't suit your needs. You might need to experiment a bit before you get just the right mix. As you can imagine, the more slides you print to one sheet, the smaller the images will be. You can use these multiple slide pages to document or review a presentation, and you'll save paper.
All of the Office applications offer this printing capability. Knowing it exists is the key--and that the printer, not Office, determines where you'll find the option.
Microsoft Access
Number report records in Access
Numbering records in a report can be helpful to the reader. You could add an AutoNumber field to the underlying recordset, but that's unnecessary and could have some unexpected results.
If the report is based on a single table that already has an AutoNumber field, don't depend on it either. When you depend on how the underlying query or report filters records, there's no guarantee the AutoNumber values will consecutively number your report's records correctly. In fact, it's almost a sure bet that they won't!
To consecutively number records in a report, do the following:
- Open the report in Design view.
- Add a text box to the Detail section, where you want the number to appear--usually, that's just to the left of the record.
- With the text box selected, click Properties to open the control's properties sheet.
- Enter the expression =1 as the Control Source property.
- Change the Running Sum property to Over All. If you're numbering records in a grouped report and you want to number each group separately, choose Over Group.
There are currently no comments for this post.