Spreadsheet tips
Sorting spreadsheet data
- I am trying to sort a column of my spreadsheet but the order doesn't change. What am I doing wrong?
This usually happens when the column you are trying to sort contains formulas rather than raw data. If you attempt to sort this "data" independently from data that it is related to, it won't happen unless you select the whole data set.
Another cause may be that the formulas relating to the data you are trying to sort may not refer to the correct information. For more in-depth information about this and similar problems check out the articles on Sorting Problems.
Formatting
- Someone told me that decreasing the number of decimals showing for a value is NOT the same as rounding that value. Are they correct?
Yes they are. This is a very common mistake people make thinking that rounding and formatting are one in the same - unfortunately this misunderstanding can produce incorrect or surprising results.
Simply put, when you decrease or increase the number of decimal places of a value all you are doing is changing the number displayed to the user. Any equation is calculated using the actual amount. So lets say that we have a figure of 2.873 - if this were displayed to show zero decimal places the number would display as 3 BUT any equation would still be done on the amount of 2.873. In some situations it is very important to keep a number to a significant number of decimal places.
In other cases it is more sensible to have it as a whole number. In these cases you can Round the number using one of the Round functions. Remember that when using the Round function to round a value or result of a formula you are actually changing the value of the number. The Round functions give you options to round a number to any desired number of decimal places. You can choose to use RoundUp which will round to a number away from zero - so our 2.873, rounded up to zero decimal places would round it to 3.
RoundDown will round to a number closer to zero - for our example this would mean that 2.873 rounded down to zero places would become 2. The Round function is a combination of the previous two - it will round down toward zero for numbers 5 and lower and away from zero for numbers greater than 5.
Note: take care when using the round functions with negative values as you may not achieve the results you were expecting. See our note on using the INT function as this may be more of a sensible option.
- How do I display/hide the actual formulas in my spreadsheet?
There are several ways you can achieve this:
The short cut key combination [Ctrl + ~] (~is called a tilde) toggles displaying formulas, (not their values), on and off.
In Excel 2003 from the Tools menu select either Formula auditing| Formula Auditing Mode OR Options | View | Formulas.
In Excel 2007 turn on (or off) the Show Formulas button in the Formula Auditing group on the Formulas tab.
- Sometimes, after I have entered a formula in a column the result shows as a series of #########'s - why does this occur?A common reason for this is
that the column width is too narrow to display the result of the formula – Resize the column to remedy this problem.
- What is conditional formatting and how and why would I use it?
Conditional formatting allows you to change the appearance of cells depending on their contents. Suppose you have a column with Yes, No or Maybe – you may want to highlight the Yes values in Red. The illustration below uses Excel 2003. In Excel 2007 Conditional Formatting is accessed from the Styles group on the Home tab. It has enhanced features.
-
I want to set up conditional formatting for a sheet that will continue to have data added. How do I specify formatting for a whole column where there is a label at the beginning that does not want this formatting?
First select the entire column by clicking in the Top of the column (i.e. the letter ). Apply the formatting you require then select the cells that you do not want to have this formatting apply to and delete the formatting for just these cells.
-
What is the difference between deleting cell contents/formatting and clearing cell contents/formatting?
Believe it or not if you use the [Delete] key to remove the contents of a selection of cells what actually happens is that the contents are cleared. Any formatting still remains. This is equivalent to Edit, Clear, Contents. It is not the same as Edit, Delete. An Edit/Delete gives the user the option of shifting cells with their formatting or deleting, rows or columns.
-
I started a spreadsheet to keep a check of how much I spend each month. I have now decided that it would be better to have these Month headings in a row not in a column, is there any way I can do this?
Sure is. You can do this really easily using copy and paste (special).
Just a quick note about using this feature. Excel won't let you paste over any of the existing data - in our example above we would have got an error message had we tried to paste January where it already was!
Times and dates
- How can I enter a formula for today’s date?
In the cell that you wish to have the date, enter the formula =Today(). Note that this is a volatile function. This doesn't mean that it loses its temper but rather that its value will change if the workbook containing it is opened on different days!
- Are there quick keys I can use to easily enter the value for today’s date?
Yes there are. In the cell you wish to display the date press [Ctrl+;].
- How can I enter a formula for today’s time?
If you use =Now() then this will give you the date and time formatting using a custom format (d/mm/yyyy h:mm in our part of the world). For example 3/07/2008 10:44.
- Are there quick keys I can use to easily enter the value for the current time?
Yes there are. In the cell you wish to display the time press [Ctrl+Shift+:].
- I have a spreadsheet where I have some dates in column A. In column B I have entered a formula to calculate the difference between two dates. I was surprised to see that this returned a date as the answer. How do I get a number?
This most likely occurred because Excel picked up the date formatting from column A. If you right click in column B and choose format cells you can choose a number format which should give you a more sensible result.
Financial functions
- What is the best way for me to calculate how much my nest-egg will be worth in ten years time?
Use the FV function to determine the future value of your savings.
To view a worked example check out the Ready Made Examples.
Using Statistical functions
- MAX(...) finds the largest value in a list of items. How can you find the second largest value?
Investigate the LARGE(...,...) function. This lets you find the 2nd or 3rd , etc value in a list. In a similar way SMALL(...,...) function can be used to find the 2nd or 3rd.
Printing Issues
- How can I get my column headings to print on all pages of my spreadsheet document?
In Excel 2003 from the File menu choose Page Setup and then select the Sheet tab. From here you can choose to print column and / or row headings.
Excel 2007 works the same except that the Sheet tab is activated from the Print Titles button available from Print Layout | Page Setup.
- I have a lot of data in a worksheet but I only wish to print some of it.
There are two easy ways you can do this in Excel 2003:
1. Select the area you wish to print, then from the Print
dialog check the Selection button.
2. Select the area you wish to print, go to the File menu,
choose Print Area and select Set Print Area. (An
existing Print Area can be cleared using this option).
In Excel 2007, the first method works exactly the same and the Print Area can be set and cleared using Print Layout | Page Setup | Print Area.
Page last updated on: 13/07/2010