Excel 2000 Tips

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.

Excel 2000 Tips has been rated 3.2 out of 5 based on 5792 ratings and 12 user reviews.
How can I convert hours and minutes into just minutes?

Converting hours and minutes into minutes in Excel

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.

   
How can I convert seconds into Horus, Minutes and Seconds

Converting seconds into hours, minutes and seconds in Excel

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.

   
How can I convert minutes into Hours, Minutes and Seconds

Convert minutes into hours, minutes and seconds in Excel

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.

   
What is the latest date allowed for calculation in Excel?

Latest date allowed for calculation in Excel

The latest date allowed for calculation in Excel is December 31, 9999

   
Can I automatically apply formatting to cells based on their content?

Automatically format based on the value in an Excel cell

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.

   
How can I insert a copy of a worksheet?

Quickly copy an Excel worksheet

Sometimes, it can be really useful to generate a copy of an existing Excel worksheet.

To do this, hold down the control key and drag the worksheet tab to the right.

A new sheet will be created with the same name as the existing one, followed by (2).

   
What does the F4 key do?

Uses for the F4 key in Excel

In Excel the F4 key repeats the last action you carried out.

Shift - F4 repeats the last Find you carried out (Find Next).

Ctrl - F4 closes the current workbook.

Alt - F4 exits the application.

   
What does the F10 key do?

Uses for the F10 key in Excel

The F10 key activates the menu bar.

Shift - F10 displays a shortcut menu.

Ctrl - F10 maximises or restores the workbook window.

   
How can I select all of the data in a column using the mouse?

Select the data in an Excel column with the mouse

To select the data in an Excel column using the mouse, hold down the shift key and double click on the bottom edge of the top cell.

   
What does the TEXT function do?

Excel's TEXT function - What does it do?

Excel's TEXT Function converts a number to text in a format which you specify.

If cell A1 contains 2, then:

=TEXT(2,"dddd")
returns Monday

   
How can I activate AutoSum from the keyboard?

Activate AutoSum from the keyboard in Excel

When using autosum in Excel, it can be frustrating to keep switching between keyboard and mouse all the time. To activate the AutoSum function with the keyboard, select the cell where you want the answer to be, hold down the Alt key and press =.

   
How can I delete a column?

Delete a column in Excel

To delete a column quickly in Excel, select the column, hold down control and press - (minus)

   
How many days can change history be maintained for in Excel?

Maximum days that Excel change history is maintained

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.

   
How can I select non-adjacent columns?

Selecting non-adjacent Excel columns

To select non-adjacent Excel columns (as in columns A, C and G), click on the column header of the first column, hold down the control key and then click on the next header, then the next etc.

   
How many scenarios can I use in a workbook?

Maximum number of scenarios

The maximum number of scenarios in an Excel workbook is limited only by available memory, however only the first 251 scenarios will appear on a scenario summary report.

   
How can I move to the end of a row or column?

Moving to the end of a column of data in Excel

To move to the last cell which contains data in an Excel column, double click on the bottom of the cell. To move to the end of a row, double click on the right hand side of the cell.

   
How many named views can I use in an Excel workbook?

Number of named views in an Excel workbook

The number of named views in an Excel workbook are limited only by available memory.

   
How can I select all the data in my table?

Select all data in an Excel table

To select all data within an Excel table, click anywhere within your table, hold down the Ctrl key and press * (on the numeric keypad). You can also use Ctrl, Shift 8.

   
How many page fields can I use in an Excel pivot table report?

Maximum page fields in an Excel pivot table report

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.

   
How many windows can I use to display an Excel workbook?

Maximum number of windows I can use to display an Excel workbook

The number of windows you can use to display Excel workbook is limited only by the available system resources.

   
How can I select sequential columns?

Selecting seqential Excel columns

To select sequential Excel columns (as in columns A to F), click on the header of the first column, hold down the shift key and click the header of the last column.

Everything between the two points will have been selected.

   
How can I drag, drop and copy in one move?

Drag and drop and copy in Excel

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.

   
How can I move cells with the mouse?

Drag and drop to move cells in Excel

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.

   
How can I convert hours into hours, minutes and seconds

Convert hours into hours, minutes and seconds in Excel

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.

   
How do I display more than one worksheet at the same time?

View more than one Excel worksheet at once

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.

   
How can I select all of the data in a column with the keyboard?

Select the data in an Excel column with the keyboard

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.

   
What does the F3 key do?

Uses for the F3 key in Excel

In Excel the F3 key activates the Paste Name dialog box.

Shift - F3 activates the Paste Function dialog box.

Ctrl - F3 activates the Define Name dialog box.

Ctrl - Shift - F3 activates the Create Name dialog box.

   
How many calculated item formulas can I use in an Excel pivot table?

Maximum number of calculated items in an Excel pivot table report

The maximum number of calculated item formulas in an Excel pivot table report is limited only by the available memory.

   
How many custom functions can I use in have in an Excel workbook#

Maximum custom functions in an Excel workbook

The number of custom functions which you can have in an Excel workbook is limited only by available memory.

   
What does the VALUE function do?

Excel's VALUE function - What does it do?

Excel's VALUE function converts a text string which represents a number to a number.

If cell A1 contains 123 (formatted as text), then

=VALUE(A1)

returns 123 (formatted as a number).

   
What does the FIND function do?

Excel's FIND function - What does it do?

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).

   
What is the maximum data points for all data series in one Excel chart

Maximum data points for all data series in one chart

The maximum data points for all data series in a single chart is 256,000.

   
How many data points per data series can I use in a 2d Excel chart?

Maximum points in a data series for an Excel 2D chart

The maximum number of data points which can be plotted in an Excel 2D chart is 32,000.

   
How many rows are there in an Excel worksheet?

Rows in an Excel worksheet

An Excel 2000 worksheet contains 65,536 rows - this is fixed and cannot be changed.

   
How many characters can I enter into a single Excel cell?

Maximum length of text in a cell

The maximum number of text characters you can enter into a single cell is 32,767.

While all 32,767 will display in the formula bar when the cell is selected, only the first 1024 characters will display in the cell itself.

   
How many Excel worksheets can be referred to by a chart?

Maximum Excel worksheets referred to by a chart

The maximum number of Excel worksheets which can be referred to by a chart is 255.

   
How many fields can I use in a data form?

Maximum fields in a data form

The maximum number of fields which can be used in a data form is 32.

   
How can I add gridlines to my chart?

Adding gridlines to your chart in Excel

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.

   
What does the CHAR function do?

Excel's CHAR function - What does it do?

The Excel CHAR function returns the ASCII character which conforms to a code number which you specify (between 1 and 255).

If A1 contains 116, then:

=CHAR(A1)

Returns t.

   
Can I save my own custom charts?

Creating a user-defined custom chart in Excel

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.

   
How can I move to the next open workbook

Move to the next open Excel workbook

To move to the next open Excel workbook from the keyboard, hold down the Ctrl key and press the Tab key.

   
How can I show the formulas within each cell?

Alternate between showing cell values and cell formulas in Excel

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.

   
Can I use autofill to the left and upwards too?

The Excel fill handle can be dragged up and left too!

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).

   
How many panes can I split the Excel screen into?

Maximum panes in a window in Excel

The maximum number of panes you can split an Excel screen into is 4.

   
How can I add titles to my chart?

Add titles to your chart in Excel

To add titles to your chart, select the chart and select Chart Options from the Chart menu. Choose the Titles tab at the top.

From this page you can add a title for your chart and a separate title or label for each Axis.

   
How do I tell Excel I´m entering a formula?

Let Excel know you're entering a formula

Excel needs some help in differentiating between text, numbers and formulas. To let Excel know that you're entering a formula, always start the formula with an = sign.

   
How can I see what my data will look like in a chart?

See what your Excel data will look like

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.

   
How many line weights can be used in an Excel chart?

Maximum line weights in an Excel chart

The maximum number of line weights which can be used in an Excel chart is 4.

   
How many custom functions can I use in have in an Excel workbook?

Maximum custom functions in an Excel workbook

The number of custom functions which you can have in an Excel workbook is limited only by available memory.

   
How many custom number formats can I use in an Excel workbook?

Maximum number of custom number formats in an Excel workbook

The number of custom number formats you can use in an Excel workbook is limited only by the available memory.

   
How many colours can I use in an Excel workbook?

How many colours can I use in an Excel workbook?

You can use up to 56 different colours in any Excel workbook.

   
What does the SUBSTITUTE function do?

Excel's SUBSTITUTE function - What does it do?

Excel's SUBSTITUTE function substitutes new text for old text within a text string.

If cell A1 contains Michael Gurner, cell B1 contains Michael and cell C1 contains Mike, then:

=SUBSTITUTE(A1,B1,C1)

would return Mike Gurner.

   
What does the LEFT function do?

Excel's LEFT function - What does it do?

The Excel function LEFT returns a number of characters that you specify from a text string, starting from the first character.

If cell A1 contains Hello, then:
=LEFT(A1,3)
would return Hel (the first 3 characters within that cell).

   
What does the CLEAN function do?

Excel's CLEAN function - What does it do?

The Excel CLEAN function will remove all nonprintable characters from a text string. This is particularly useful after importing text from a different application or platform.

   
How can I move to the end of a row of data with the mouse?

Move to the end of a row with the mouse in Excel

Want to move to the end of a row of data in Excel with the mouse?

Double click on the right (or left) hand side of a cell.

   
What does the F12 key do?

Uses for the F12 key in Excel

In Excel, the F12 key activates the Save As dialog box.

Shift - F12 activates the Save command.

Ctrl - F12 activates the Open command.

Ctrl - Shift - F12 activates the Print command.

   
What does the F11 key do?

Uses for the F11 key in Excel

The F11 key creates a chart from the selected data.

Shift - F11 inserts a new worksheet.

Ctrl - F11 inserts a Microsft Excel 4.0 macro sheet.

Alt - F11 displays the Visual Basic Editor.

   
What is the earliest date which can be used for calculations in Excel?

Earliest date allowed for calculation in Excel

The earliest date allowed for calculation in Excel is January 1, 1900 (or January 1, 1904 if the 1904 date system is used).

   
How many worksheet functions are available in Excel?

Number of worksheet functions

The number of available worksheet functions is 329.

   
What is the maximum number of arguments in an Excel function?

Maximum arguments in an Excel function

The maximum number of arguments in an Excel function is 30.

   
How many custom toolbars can I have in an Excel workbook?

Maximum custom toolbars in an Excel workbook

The number of custom toolbars in an Excel workbook is limited only by available memory.

   
What is the maximum number of cells which are adjustable using solver?

Maximum number of adjustable cells in Excel's solver tool

The maximum number of cells which can be adjusted using Excel's solver tool is 200.

   
How can I select an entire worksheet?

Select an entire Excel worksheet

To select an entire Excel worksheet, click the select all button, which can be found in the top left hand corner between the row and column headings.

   
What does the ADDRESS function do?

Excel's ADDRESS function - What does it do?

Excel's ADDRESS function creates a cell reference as text, given specified row and cell numbers.

If cell A1 contains 15 and cell B1 contains 10, then:

=ADDRESS(A1,B1)

would return $J$15.

   
How can I insert a new column?

Insert a new column in Excel

To insert a new column in an Excel worksheet, select the column header where you want the new column to appear, hold down the control key and press + (on the numeric keypad).

   
What does the F6 key do?

Uses for the F6 key in Excel

In Excel the F6 key moves you to the next pane of a split window.

Shift - F6 moves you to the previous pane of a split window.

Ctrl - F6 moves you to the next open workbook.

Ctrl - Shift - F6 moves you to the previous open workbook.

   
What is the maximum formula length in Excel?

Maximum formula length

The maximum formula length in Excel is 1,024 characters.

   
How many cells can I change in an Excel scenario?

Maximum number of changing cells in an Excel scenario

The maximum number of changing cells in any Excel scenario is 32.

   
How can I insert a comment into an Excel cell?

Inserting comments in Excel

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.

   
How can I quickly enter the current time?

Insert the current time quickly in Excel

To quickly insert the current time into an Excel cell, hold down the control and shift keys and press ; (semi-colon).

   
How can I get Excel to extend a numerical sequence?

Extending numerical sequences in Excel

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.

   
What does the REPT function do?

Excel's REPT function - What does it do?

Excel's REPT function repeats a text string you specify a number of times (again that you specify).

If cell A1 contains Michael Gurner, then
=REPT(A1,2)
would return Michael GurnerMichael Gurner

   
How many pivot table reports can I use on a single Excel worksheet?

Maximum number of pivot tables on an Excel sheet

The number of pivot table reports which you can use on a single worksheet is limited only by your available memory.

   
What is the largest amount of time which can be entered in Excel?

Largest amount of time which can be entered in Excel

The largest amount of time which can be entered in Excel is 9999:99:99.

   
What is the smallest allowed negative number?

Smallest allowed negative number in Excel

The smallest allowed negative number in Excel is -9.99999999999999E307

   
What is the largest allowed negative number in Excel?

Largest allowed negative number in Excel

The largest allowed negative number in Excel is -1E-307

   
How many line styles can be used in an Excel chart?

Maximum line styles in an Excel chart

The maximum number of line styles which can be used in an Excel chart is 8.

   
How many range names can I use in an Excel workbook?

Maximum number fo range names in an Excel workbook

The number of range names you can use in an Excel workbook is limited only by available memory.

   
How wide can I make an Excel cell?

Maximum width of an Excel cell

The maximum width of an Excel cell is 255 characters.

   
How can I incorporate my chart into another worksheet?

Incorporate your Excel chart into a standard worksheet

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.

   
How can I chart non-adjacent data?

Chart non-adjacent data in Excel

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.

   
How can I applya standard time format?

Apply a standard time format in Excel

To apply a standard time format to a selected cell, hold down the Ctrl and Shift keys and press @.

   
What number precision does Excel work to?

What number precision does Excel work to

Excel works to a number precision of 15 digits

   
How many unique items can autofilter display in Excel?

Maximum unique items which can be displayed by AutoFilter in Excel

The maximum number of unique items which AutoFilter can display in Excel 97/2000 is 1000.

   
How high can I make an Excel row?

Maximum height of an Excel row

The maximum height of an Excel row is 409 points.

   
What does the MID function do?

Excel's MID function - What does it do?

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:
=MID(A1,4,7)
returns hael Gu (seven characters, staring from character number 4).

   
How do I save time when formatting?

Save time when formatting in Excel

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.

   
What does the UPPER function do?

Excel's UPPER function - What does it do?

Excel's UPPER function converts a text string to Uppercase.

If cell A1 contains MicHAel gurneR, then:

=UPPER(A1)

returns MICHAEL GURNER.

   
What does the RIGHT function do?

Excel's RIGHT function - What does it do?

Excel's RIGHT function returns a number of characters from a text string that you specify starting from the last character.

If cell A1 contains Michael Gurner, then
=RIGHT(A1,4)
returns rner

   
What does the function LOWER do?

Excel's LOWER function - What does it do?

Excel's LOWER function will convert all text (whatever the case) to lowercase text.

If cell A1 contains MiCHaeL, then:
=LOWER(A1)
returns michael

   
What does the LEN function do?

Excel's LEN function - What does it do?

Excel's LEN function returns the number of characters in a text string.

If cell A1 contains Hello. then:
=LEN(A1)
will return 5.

   
What does the DOLLAR function do?

Excel's DOLLAR function - what does it do?

The Excel DOLLAR function converts any number to text using the currency format.

If cell A1 contains 123,
=DOLLAR(A1)
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,
=DOLLAR(A1,5)
Would return £123.00000

   
What does the CODE function do?

Excel's CODE function - What does it do?

The Excel CODE function returns a numeric code for the first character in a text string.

If cell A1 contains Michael Gurner, then:

=CODE(A1)

returns 77.

   
How can I scroll through the worksheets in my workbook?

Scrolling through Excel worksheets

To scroll through the worksheets of the active workbook, hold down the control key and press page down.

To scroll in the other direction, hold down control and press Page up.

   
What does the F5 key do?

Uses for the F5 key in Excel

In Excel the F5 key activates the GoTo box.

Shift - F5 activates the Find dialog box.

Ctrl - F5 Restores the window size.

   
Can I always use pie charts?

Avoid pies with everything in Excel

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.

   
What is the zoom range in Excel?

Zoom range in Excel

An Excel worksheet can be zoomed between 10% and 400%.

   
Can I drag, drop and copy without overwriting existing cells?

Drag and drop and copy in Excel without overwriting existing cells

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.

   
How can I reposition the chart legend?

Positioning the chart legend in Excel

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.

   
What´s the quickest way to create a chart?

Creating a chart with a single click in Excel

What's the quickest way to create a chart?

Select the data that you want to chart and press F11.

That's it.

   
What is the maximum number of items in a pivot table report?

Maximum items in an Excel pivot table report?

The maximum number of items which an Excel pivot table can contain is 8,000.

   
How many worksheets can I have in a workbook?

Worksheets in a workbook

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.

   
How many cells are there in an Excel worksheet

Cells in an Excel worksheet

There are 16,277,216 cells in an Excel 2000 worksheet - this is fixed and cannot be changed.

   
How can I insert a hyperlink?

Insert a hyperlink in Excel

To insert a hyperlink into an Excel cell, click in the cell, hold down Control and press K. The hyperlink dialog box appears and you can choose to link to an external file, a worksheet in the open workbook or even a named range in the current workbook.

   
How can I speed up data entry in Excel?

Save time on data entry in Excel

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.

   
What does the T function do?

Excel's T function - What does it do?

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 "".

   
What does the PROPER function do?

Excel's PROPER function - What does it do?

Excel's PROPER function converts text (of any case) to proper case (where the first letter of every word is capitalised).

If cell A1 contains michaEl gUrner, then
=PROPER(A1)
returns Michael Gurner

   
What does the F9 key do?

Uses for the F9 key in Excel

In Excel the F9 key calculates all the sheets in all open workbooks.

Shift - F9 calculates the active worksheet.

Ctrl - F9 minimises the active workbook.

   
What is the maximum number of iterations in Excel?

Maximum iterations in Excel

The maximum iterations in Excel is 32,767 although the default is set at 100.

   
What does the FIXED function do?

Excel's FIXED function - What does it do?

The Excel FIXED function formats a number as text with a number of decimals which you specify, with or without commas.

If cell A1 contains 123456 (formatted as a number), then:

=FIXED(A1,3,TRUE)

returns 123456.000

   
How can I get back to the start of my worksheet?

Go to the first cell in your Excel worksheet (A1)

To go to cell A1 from wherever you are in the Excel worksheet, hold down Ctrl and press Home.

   
What is the smalles font size I can use in Excel?

Smallest font size in Excel

The smallest font size you can use in Microsoft Excel is 1 point.

   
How can I quickly format a table?

Quickly format a table in Excel

To quickly format a table, select the table and choose AutoFormat from the Format menu.

Select a format from the right hand side (the preview window will show you what it will look like) and press OK.

   
How many data points can be plotted in a data series for a 3D chart?

Maximum data points per data series in a 3D Excel chart

The maximum number of data points which can be plotted in a data series in a 3D Excel chart is 4000.

   
How many charts can I link to a single Excel worksheet?

Maximum charts linked to an Excel worksheet

The maximum number of charts linked to an Excel worksheet is limited only by available memory.

   
What is the largest allowed positive number in Excel?

Largest allowed positive number in Excel

The largest allowed positive number in Excel is 9.99999999999999E307

   
How many workbooks can I have open at one time?

How many Excel workbooks can I open at the same time?

The number of workbooks you can have open at any one time is limited only by the available memory and system resources.

   
How many columns are there in an Excel worksheet?

Columns in an Excel worksheet

An Excel 2000 worksheet has 256 columns - this is fixed and cannot be changed.

   
How can I speed up the entry of my data

Use Excel's Custom Lists feature

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.

   
What does the SEARCH function do?

Excel's SEARCH function - What does it do?

Excel's SEARCH function searches for a text string within another text string and returns the position at which it finds it.

If cell A1 contains Michael Gurner and cell B1 contains Gur, then:

=SEARCH(B1,A1)

Would return 9.

   
How can I adjust the width of a column?

Adjust column widths in Excel

To adjust the width of a column, click on the right hand edge of the column header and drag to the desired width. A tool tip will appear and will tell you the exact width currently selected.

   
How can I show my data in different ways?

Swap between Rows and Columns in Excel

Any set of data can tell a number of different stories when charted. One simple way of showing data in a chart in a different light is to use the Rows/Columns options in the Source Data dialog box (step two of the chart wizard).

   
Can I change the default chart type?

Setting the default chart in Excel

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.

   
What is the smallest allowed positive number in Excel?

Smallest allowed positive number in Excel

The smallest allowed positive number in Excel is 1E-307

   
How many levels of undo are available in Excel?

Levels of undo available in Excel

The maximum levels of undo available in Excel is 16

   
What is the maximum number of linked sheets in an Excel workbook?

Maximum linked sheets

The number of linked sheets in an Excel workbook is limited only by available memory.

   
How can I move to the last cell in a column of data using the keyboard

Move to the end of a column of Excel data with the keyboard

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.

   
How can I move to the end of a row of data?

Moving to the end of a row of data in Excel

Need to move to the end of a row of Excel data using the keyboard?

Hold down the control key and press the right (or left arrow).

   
How can I insert a new task with the keyboard?

Insert a new worksheet in Excel

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

   
How can I format my chart?

Get creative with charts in Excel

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.

Have fun.

   
How many levels of nesting are available in an Excel function?

Maximum levels of nesting in an Excel function

The maximum number of nesting levels in an Excel function is 7.

   
How can I show the data with my chart?

Show the data beneath the Excel chart

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.

   
How can I edit the active cell with the keyboard?

Edit the active cell using the keyboard in Excel

To edit the active cell in Excel using the keyboard, press F2. This can save a lot of time switching between mouse and keyboard.

   
Can I modify the AutoFormat tool?

Modifying AutoFormat in Excel

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.

   
How many data fields can I use in an Excel pivot table report?

Maximum number of data fields in an Excel pivot table report

The maximum number of data fields you can use in an Excel pivot table report is 256.

   
How can I copy a date across a number of cells

Copy a date across a number of cells in Excel

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.

   
How can I select sequential worksheets

Select sequential Excel worksheets

To select sequential Excel worksheets, click on the name tab of the first worksheet, hold down the Shift key and click on the name tab of the last worksheet.

   
How can I make my charts easy to print?

Make a chart easy to print in Excel

To make your chart easy to print, select the chart and select Location from the Chart menu.

Select the As New Sheet option and your chart will be reformatted to a sheet of its own and will be sized perfectly for printing.

   
What is the largest font size I can use in Excel?

Largest font size in Excel

The largest font size which can be used in Excel is 409 points.

   
What does the EXACT function do?

Excel's EXACT function - What does it do?

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.

   
How can I get more than one line of text in a single cell?

Enter more than one line of text in an Excel cell

If you want to spread the contents of a cell over more than one line, type as normal, but when you reach the end of a line, hold down Alt and press Enter. A new line will start within the same cell.

   
How can I automatically extend a series?

Automatically extend a series in Excel

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.

   
How can I rename the chart tab?

Rename your chart tab in Excel

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.

   
How can I get help with the structure of a formula?

Help with Excel formulas

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).

   
How can I quickly enter todays date?

Insert todays date quickly in Excel

To insert todays date into an Excel cell, hold down the control key and press ; (semi-colon).

   
How can I copy Styles from an existing workbook?

Copy styles from another Excel workbook

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.

   
How many data series can I use in one Excel chart?

Maximum data series in one Excel chart

The maximum number of data series in one Excel chart is 255.

   
How can I show chart information without a legend?

Show all Excel chart information without a legend

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.

   
How can I get to the last cell in my table?

Go to the last cell in your Excel table

To move to the last cell in your table in Excel, hold down the control key and press end (this is a little unreliable, but give it a go).

   
How can I apply a standard date format?

Quickly apply a standard date format in Excel

To apply a standard date format, select the cell(s), hold down Ctrl and press #.

   
What does the REPLACE function do?

Excel's REPLACE function - What does it do?

Excel's REPLACE function allows you to replace part of a text string with another text string.

If cell A1 contains Michael Gurner and Cell B1 contains Mike, then:

=REPLACE(A1,1,7,B1)

Would return Mike Gurner.

   
How can I show exactly what each chart section represents?

Show exactly what each Excel chart section represents

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.

   
How do I move to the next worksheet in my workbook

Moving to the next worksheet in your Excel workbook

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.

   
How can I select an entire row?

Selecting an entire Excel row with the keyboard

To select an entire Excel row, click anywhere inside the row, hold down the Shift key and press the Spacebar.

   
How can I activate the paste function tool?

Activate the Paste Function tool from the keyboard in Excel

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.

   
How many cell styles can I use in an Excel workbook?

Maximum styles in an Excel workbook

The maximum number of cell styles you can use in an Excel workbook is 4000.

   
What does the F7 key do?

Uses for the F7 key in Excel

In Excel the F7 key activates the spelling command.

Ctrl - F7 allows you to move the current window.

   
How can I display range names on my worksheet?

Display Range names directly in the Excel worksheet

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).

   
How can I modify the formatting of a chart?

How can I modify an Excel chart once I've created it?

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.

   
How can I format my chart?

Formatting your chart in Excel

Once you've created your chart, you can format almost every part of it simply and quickly. Either double clicking or right clicking (and selecting Format) on the element you wish to format will bring up a dialog box which allows you to do just that.

   
How can I move to a different worksheet quickly?

Move quickly to a specific worksheet in Excel

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.

   
How can I alter a chart after I´ve created it?

Change the data a chart is based on in Excel

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.

   
How can I activate the formula palette?

Activate the formula palette from the keyboard in Excel

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.

   
What does the F1 key do?

Uses for the F1 key in Excel

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.

   
How can I reorder the sheets in my workbook?

Re-order the worksheets in an Excel workbook

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.

   
How can I speed up the use of AutoSum?

Quick AutoSum in Excel

To save Excel asking you which data you want to autosum, simply select the data and press the AutoSum button. Excel will automatically sum the data.

   
What does the Concatenate function do?

Excel's CONCATENATE function - what does it do?

The Excel Concatenate function is used to join several text items into one item.

If cell A1 contains Michael and cell B1 contains Gurner
=concatenate(A1,B1)
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

   
How many users can share a shared Excel workbook?

Maximum number who can share an Excel workbook at once

The maximum number of users who can open and share a shared workbook at the same time is 256.

   
What does the TRIM function do?

Excel's TRIM function - What does it do?

Excel's TRIM function removes all spaces from a text string except single spaces between words.

If cell A1 contains Michael Gurner Is Great, then:

=TRIM(A1)
returns Michael Gurner Is Great.

   
How can I quickly make a cell reference absolute?

Making an Excel cell reference absolute

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.

   
How many custom toolbar buttons can I have in Excel?

Maximum custom toolbar buttons in Excel

The number of custom toolbar buttons in Excel is limited only by the available memory.

   
How canb I reliably go to the last cell in my table

Go to the last cell in your Excel table (reliably)

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.

   
How can I paste a range name into a formula?

Paste a range name into an Excel formula

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.

   
What does the F8 key do?

Uses for the F8 key in Excel

In Excel the F8 key activates the extend selection command.

Shift - F8 allows you to add to your selection.

Ctrl - F8 allows you to resize the window.

Alt - F8 displays teeh Macro dialog box.

   
How can I quickly add up some numbers?

Quickly adding numbers in Excel

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.

   
Should I change the default alignments in Excel?

Don't change the default alignments in Excel

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.

   
How can I select an entire column?

Select an entire Excel column with the keyboard

To select an entire Excel column, place the cursor anywhere in the column, hold down Ctrl and press the Spacebar.

   
What does the F2 key do?

Uses for the F2 Key in Excel

In Excel, the F2 key allows you to edit the active cell.

Shift - F2 allows you to edit a cell comment.

Alt - F2 activates the Save As command.

Alt - Shift - F2 activates the Save command.

   
How many column and row fields can I have in an Excel pivot table?

Maximum row or column fields in an Excel pivot table report

The maximum number of row and column fields in an Excel pivot table report is only limited by the available memory.

   
Not finding the advice and tips you need on this Software Tip Site? Request a Tip Now!


Guru Spotlight
Sherril Steele-Carlin