Read these 179 Excel 2000 Tips tips to make your life smarter, better, faster and wiser. Each tip is approved by our Editors and created by expert writers so great we call them Gurus. LifeTips is the place to go when you need to know about Software tips and hundreds of other topics.
There is a way to convert time expressed in hours and minutes into minutes only, even though Excel doesn't have a minutes-only format. Enter a measure of time in hours and minutes, such as 2:25, into any cell, such as A1. In another cell, enter the formula =Hour(A1)*60+minute(A1). Excel will then convert the time into minutes.
If the result is still in time format (00:00 or similar), then click the Comma Style button (to the right of the percentage button) on the toolbar or select the General format from Format, Cells, Number.
To convert a number of seconds into a more conventional hours, minutes and seconds time format in Excel, follow these two simple steps:
1) Divide the number by (24*60*60), so if the number is in cell A1, put in a calculation which says =A1/(24*60*60).
2) Then choose the required time format from Format, Cells, Number.
To convert a number of minutes into a more conventional hours, minutes and seconds format in Excel, just follow these two simple steps:
1) Divide the number by (24*60), so if the number is in cell A1, put a formula in any other cell which reads =A1/(24*60)
2) Then choose the required time format from Format, Cells, Number.
Why waste time looking for cells which meet certain criteria? Excel can be set up to format something in a particular way only if the cell meets certain conditions.
To apply a conditional format, select the cells that you want to apply the formatting to and choose conditional formatting from the format menu.
Set the criteria (as in B6 is greater than 15) and create a format to apply when that condition is met.
By selecting add from the conditional formatting dialog box, you can create up to three conditions for each cell or group of cells.
The change history in Excel is set by default to be maintained for 30 days, however it can be set to a maximum of 32,767 days.
The maximum number of page fields which can be used in an Excel pivot table report is 256, however this may be limited by the available memory.
The number of windows you can use to display Excel workbook is limited only by the available system resources.
When you need to copy cells in Excel, an alternative to the traditional copy and paste route is to use drag and drop. Select the cells that you want to copy and release the mouse button. Then move the cursor to any edge of the selected region (the cursor will change to a white arrow), hold down the control key and click and hold the mouse button. Drag the cells to the desired location and release the mouse button.
While cut and paste can be used in Excel to move cells from one place to another, major time savings can be had from using drag and drop.
Select the cells you want to move and release the mouse button. Move the cursor to any edge of the selected cells (the cursor will change to a white arrow), click and hold the mouse button down and move to the required place. Release the mouse button and the cells will be moved to the new position.
To convert a number of hours into a more common hours, minutes and seconds format in Excel, follow these two simple steps:
1) Divide the figure by 24, so if the figure is in cell A1, put a formula in any other cell which says =A1/24
2) Then choose the required time format from Format, Cells, Number.
Constantly switching between worksheets can be frustrating and can really damage your productivity. To show more than one worksheet on screen at the same time, follow these simple steps:
1) From the Window menu, select New Window (do this for each additional worksheet which you want to view)
2) Select Arrange from the Window menu
3) Put a tick in the 'Windows of Active Workbook' box
4) Choose the way you want your window arranged (tiled, vertical etc) and press OK
Each window will now be displayed separately and you can show a different worksheet in each one.
To select all the data in an Excel column (this won't work if you have completely empty cells), just do the following:
Click in the first cell you want to select and then hold down the control and shift keys and press the down arrow.
The maximum number of calculated item formulas in an Excel pivot table report is limited only by the available memory.
The Excel function FIND searches for text you specify inside another text string and returns the position at which the text string starts.
One standard use for this function is to look for a the position of a space when separating text strings (generally in conjunction with either MID or LEFT).
The maximum data points for all data series in a single chart is 256,000.
The maximum number of data points which can be plotted in an Excel 2D chart is 32,000.
Gridlines can make it easier for people to estimate the values represented in your chart. To add them in, select the chart and select Chart Options from the Chart menu. Select the Gridlines tab at the top.
From this screen you can add both minor and major gridlines to the X and/or Y axis.
If you regularly create charts of a particular type, then you can save any number of charts as User-Defined Custom Charts.
Create your chart and then select Chart Type from the Chart menu. Click on the Custom Types tab at the top of the screen and click User Defined.
Click Add and give the chart a name and description.
To choose your new chart type, select Custom Types, User Defined and select it from the list that appears.
When reviewing or troubleshooting an Excel spreadsheet, it can be useful to show the formulas within each cell, rather than the formula results. To toggle between the formula and the formula results, hold down control and press ` (single left quotation mark - above tab). This also allows you to print a worksheet with all the formulas visible.
Most people are aware that the Excel Fill Handle (the black square in the bottom right hand corner of a selected cell) can be dragged to the right and down to extend a formula. What most people aren't aware of is that you can also drag the Fill Handle to the left and up (just be careful to drag far enough, otherwise you'll just delete the contents of the cell).
When selecting the type of chart to use, it can be difficult to visualise what the chart will look like with your data in it.
To see your chosen chart type with your data, click and hold the button at the bottom of the Chart-Type dialog box which says Press and hold to view sample.
The number of custom number formats you can use in an Excel workbook is limited only by the available memory.
The maximum number of cells which can be adjusted using Excel's solver tool is 200.
The maximum number of changing cells in any Excel scenario is 32.
Ever struggled to remember why you did something in a certain way, or worse, had to try and work out why someone else did something in a particular way?
Add comments to leave yourself and others explanations of why you used that figure (or that calculation or whatever).
To insert a comment, either right click on the cell or select the cell and choose comment from the insert menu.
If you need to enter numerical sequences that follow a logical order (such as 1, 2, 3 or 5, 10, 15), Excel can save you a great deal of time and trouble.
If you enter the first two numbers, Excel can complete the series for you.
If you enter 1 in cell A1 and 2 in cell B1 and select both cells with the mouse. Then click and drag the fill handle to the right. Excel will continue the series as far as you drag.
The largest amount of time which can be entered in Excel is 9999:99:99.
The number of range names you can use in an Excel workbook is limited only by available memory.
If your chart is on a sheet of its own, it's easy to move it back to be part of another worksheet.
Select the chart and select Location from the Chart menu and choose As Object In and select the worksheet you want to put the chart into. Click OK.
It's common to want to produce a chart that shows only some of your data, but you don't need to rearrange or manipulate your data to achieve this.
To select non-adjacent data (for example rows 2, 4 and 6 of your data), select the first required row, hold down the Ctrl key and select the second, then the third etc.
Then create the chart in the usual manner.
The maximum number of unique items which AutoFilter can display in Excel 97/2000 is 1000.
Excel's MID function returns a number of characters that you specify from a text string, starting from a position which you specify.
If cell A1 contains Michael Gurner, then:
returns hael Gu (seven characters, staring from character number 4).
To save continually recreating the same formats each time you create a spreadhseet, use Excel's built in Style function.
Apply the formatting you require to a cell or group of cells, then select those cells. Click in the Style Box on the toolbar next to the font selector (which will generally say normal), type a name for your style and press enter. The style can now be applied just by selecting the cells where you want to apply it and selecting your style name from the drop down list.
Note: Style names can't contain spaces or hyphens - use underscores or capital letters instead.
The Excel DOLLAR function converts any number to text using the currency format.
If cell A1 contains 123,
will return £123.00 (as text, not as a number).
The default number of decimal places is 2, but the number of decimal places can be modified by adding a comma and a number after the cell reference.
In the above example,
Would return £123.00000
The most common cause of problems when producing charts is caused by people attempting to create pie charts from multiple data series.
A pie chart will allow to show one series of data broken down into its separate sections, but can not show more than one data series.
Consider using a doughnut chart instead.
To copy cells in Excel using drag and drop without overwriting cells in the desired location, hold down the control and shift keys whilst dragging. The cells will be copied and inserted into their new location without deleting the existing contents of the new location.
Most default chart types include a legend and most position that legend at the right hand side of the chart. If that suits you, then fine but if you want to either remove it or reposition it, then you can do so easily.
Select the chart and select Chart Options from the Chart menu. Click the Legend tab at the top.
From this screen you can remove the legend by clearing the Show Legend check box or reposition the legend by choosing any of the Placement options.
There is no limit on the number of worksheets you can have in an Excel 2000 workbook. The default is three and the most you can set as the default is 256, however the only limiter is the memory and system resources of your PC.
To modify the number of default worksheets in a workbook, select Tools, Options, choose the General tab and select the number you require from the Sheets in new workbook section.
If you find yourself typing the same information a number of times in the same column, Excel has a really useful 'pick from list' feature which can save you loads of time.
If you right click the cell where you want to enter data, there is an option called Pick From List. This will bring up a list of all entries above the cell (in the same column) and you can select the relevant one.
The same thing can be achieved by holding down the Alt key and pressing the down arrow. Please note - neither of the above will function correctly if there are blank rows in your data.
Excel's T function returns a text value if the cell contains text and a "" (empty text) if the cell does not contain text.
If cell A1 contains Michael, then:
=T(A1) returns Michael
If cell A1 contains 247 (formatted as a number) then:
=T(A1) returns "".
The maximum number of data points which can be plotted in a data series in a 3D Excel chart is 4000.
The number of workbooks you can have open at any one time is limited only by the available memory and system resources.
If you regularly have to type or copy in the same list of information, whether it's a list of people, clients or whatever, Excel has a handy Custom Lists feature which can save time and improve consistency.
Type your list once (with one entry per cell) and then select the cells. Select Options from the Tools menu and choose the Custom Lists tab.
Click on the Import button and click OK.
Now whenever you type one entry and drag the fill handle down, Excel will complete the list for you.
If you find yourself regularly creating the same kind of chart, you might want to consider setting that chart type as your default.
Creat the chart and then select Chart Type from the Chart menu. Click on the Set As Default button.
That chart type will now appear as the default when you use the chart wizard or when you press F11.
To move to the last cell in a column of Excel data using the keyboard, click anywhere inside the column, hold down the Ctrl key and press the Down arrow key.
When you need to insert a new worksheet in Excel, most people are aware that they can select Worksheet from the Insert menu. There are also keyboard shortcuts which will do the same thing quicker.
Two shortcuts to insert a new worksheet are:
Hold down Shift and press F11
Hold down Alt and Shift and press F1
While the default charts look professional in an OK sort of way, you can add a bit of life and colour to charts using Fill Effects.
Double click an area of your chart (the background for example) and select Fill Effects from the Patterns tab.
Experiment with some of the built in settings or create your own using your own pictures.
Charts are sometimes easier to read if the reader has access to the data the chart represents. This can be added into the chart itself by following these simple steps.
Select the chart and select Chart Options from the Chart menu. Click the Data Table tab at the top and click in the Show Data Table box. Click OK.
While some of the formats provided with AutoFormat are useful, they might not be quite what you need. A good example of this is where you want to apply all of the formatting, but leave the font as it is.
To modify this, click on Options from the autoformat dialog box (Format, AutoFormat) and deselect the font option. Any of the other aspects of AutoFormat can be selected and deselected in this manner.
The maximum number of data fields you can use in an Excel pivot table report is 256.
Excel's autofill feature is really useful if you want to enter one date, drag the fill handle and have it extend the series. If you just want to copy the same value into a number of cells, then dragging normally isn't an option.
One method of doing achieveing this is to select all the cells (including the one with the date in), hold down the Ctrl key and press the D key. If the cells are horizontal rather than vertical, hold down the Ctrl key and press the R key.
The Excel EXACT function compares two text values and returns TRUE if they are identical and FALSE if they are not identical.
If cell A1 contains Hello and cell B1 contains hello,
=EXACT(A1,B1) will return FALSE because the Hello in A1 has a capital letter.
When working with any logical series (Days of the week, months of the year, Q1, Q2 etc), let Excel do the hard work for you.
Enter the first entry and click and drag the fill handle (found at the bottom right hand corner of the cell). Excel will complete the series for you.
If you create a chart as a new sheet, then the sheet will have been named something like Chart 1 (or 2 or whatever).
To rename the sheet, simply double click on the tab containing the name (at the bottom of the workbook) to highlight it and type the name you require.
If you know the Excel function that you need and remember some of the details, you can get a very helpful reminder by typing =function (where function is the name of the function that you want to use, e.g. Sum or If or whatever), holding down Ctrl and Shift and pressing A.
The arguments for the function are inserted into the cell and you can type over them (remember that you can double click on each part to select it and then just type to replace it).
If you have the styles you want to use in a different workbook, then there is no need to create them again. Open both workbooks and select the one you want to copy the styles to from the Window menu. Choose Style from the Format menu and press merge. Select the workbook which you want to copy styles from and press OK. All styles from the source workbook will be copied into the destination workbook.
Please note: Please use carefully - any style which shares the same name as one from the source workbook will be overwritten.
You can remove the Chart Legend and still make your chart readable. To do this, select your chart and select Chart Options from the Chart Menu. Select the Legend tab from the top and uncheck the box that says Show Legend.
Now select the Data Labels tab from the top and select Show Label, then click OK.
Charts can be great at giving people a general picture of what data represents, but they can also be used to show very specific information.
A value can be shown on each row/column so that the reader can see exactly what the chart is showing. To activate this, select your chart and select Chart Options from the Chart menu. Click on the Data Labels at the top and choose the Show Value option.
To move to the next worksheet in your Excel workbook, hold down the Ctrl key and press the Page Down key. Each press will move you one sheet to the right. To move back in the other direction, hold down the Ctrl key and press the Page Up key.
If you're not sure of which funtion to use in Excel, click in the cell where you want the answer, hold down the Shift key and press F3. The Paste Function tool will be activated.
To display Range names directly in a worksheet, zoom out to 39 percent or less. Excel puts a border around each range and shows the name of that range in blue (this feature is for display purposes only and will not print).
Once you've created a chart, the four steps of the Chart Wizard can be accessed from the Chart Menu (the chart menu only appears when you select the chart, if teh chart isn't selected, you will see the Data menu instead).
The first four options under the Chart menu are the four steps - Chart Type, Source Data, Chart Options and Location. Any of these can be edited at any time.
Want a quick way to quickly move to a particular sheet, even if the sheet names won't all fit along the bottom of the sheet?
Right click on the sheet navigation buttons (to the left of the sheet tabs) and select the desired worksheet from the list that appears.
If you've spent time creating a chart, it can be really frustrating to have to recreate the chart every time you want to add a column or row.
To change the selection that a chart is based on, select the chart and select Data Source from the Chart menu. From the Data Source dialog box, simply drag over the complete selection that you want to include in your chart and press OK.
If you know the name of the function you need in Excel but aren't sure of the correct syntax, type =function (where function is the name of the function you want to use, eg SUM, or IF or whatever), hold down Ctrl and press A.
F1 will activate Microsoft's Help tool.
Alt - F1 will create a default chart from the selected data.
Shift - F1 will activate the 'What's This' section of Microsoft Help. Click on any part of the screen to see what it is.
Alt - Shift - F1 will insert a new worksheet.
If you find that the worksheets in a workbook are not in the most convenient order (particularly if there are a large number of worksheets), don't worry - you can reorder them very easily.
If you click and drag the worksheet name to the left or right, you can reposition the worksheets exactly where you want them.
The Excel Concatenate function is used to join several text items into one item.
If cell A1 contains Michael and cell B1 contains Gurner
Would return MichaelGurner.
To add spaces or other characters between entries, simply enclose them between speech marks (and separate each entry with commas).
In the same example,
=concatenate (A1," ",B1)
would return Michael Gurner
The maximum number of users who can open and share a shared workbook at the same time is 256.
To make an Excel cell reference absolute rather than relative, you need to add a dollar sign before the column and/or row reference.
To do this automatically, click into the cell reference in the formula bar and press F4. If you select more than one cell reference in a formula, all selected parts will become absolute.
If you press F4 repeatedly, you will see that Excel toggles through the four stages of absolute/relative references.
To reliably go to the last cell in your table of data in Excel, click anywhere inside your data, hold down the Ctrl key and then press the Down arrow key, then the Right arrow key.
To paste a range name into an Excel formula (and therefore remove any opportunity for error and save a lot of time), simply press F3 when creating the formula.
The paste name dialog box appears and allows you to select the relevant range name and paste it straight into the formula.
To add a selection of numbers, just select them with the mouse. The sum of the numbers selected will be displayed in the status bar at the bottom of the screen.
By right clicking on the word SUM in the status bar, you can change the formula used to Count, Average and a number of others.
Excel sometimes gets confused as to whether a cell contains text or numbers. The default alignment for text is left and for numbers it is right - this makes it very easy to see if a cell or cells are not formatted as the correct type. I'd suggest that wherever possible you leave the defaults as they are.
The maximum number of row and column fields in an Excel pivot table report is only limited by the available memory.
|Jennifer Mathes, Ph.D.|