98.6% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Hints and tips home » Excel hints and tips
Excel hints and tips
Navigate with keyboard without losing your active cell
If you like using your keyboard to scroll through your excel document, but want to keep your active cell the same, use the scroll lock, and then use your arrow keys to navigate around the document.
Apply Autosum with keyboard shortcut
If you press Alt and = at the same time, it applies autosum.
Shortcut fill a cell with contents from adjacent cells
Use Control + D to fill a cell with the data from an adjacent cell. This speeds up data entry and is a cool tip to share!
Add a € to your cells
If you need to add a € symbol to your Excel sheet - hold down the key Alt Gr and 4.
Alt Gr is located on the right side of the space bar.
Create own ribbon tab - Excel 2010
a. In Excel click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the New Tab button (below the list of tabs on the right hand side of the dialog box)
e. Select the New Tab (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
f. Select the New Group (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
g. Add commands to your tab and group by locating them on the list on the right hand side (remembering that you can change the list using the drop down box at the top of the list of commands) and clicking the Add button between the two panes to add them to your tab and group
h. You can rearrange the commands in your group, the groups on any tab or the tabs, using the up and down arrow buttons beside the list of tabs.
i. Click OK to apply your changes
Display developer tab - Excel 2010
a. In Excel, click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the box next to Developer in the list of tabs on the right hand side of the dialog box. When ticked the Developer tab will be visible.
e. Click OK to apply your changes
Autofit column width – Excel (all versions)
a. Highlight the column or columns you wish to alter the width of. You do this by clicking on the grey button at the top of the column showing the column letter. Click and drag on these letters to select more than one column.
b. Double click the dividing line between the columns. This dividing line is the break between the columns on the column headers (grey buttons showing the column letter at the top of each column). When you hover your mouse over one of these dividing lines the point will change and show an arrow pulling a line in two directions. When you have this mouse pointer you should double click to get Excel to automatically set the column width to fit the contents of the column (autofit)
Switch on smart tags – Excel 2007
In order to use smart tags, make sure they are turned on, to do this:
1. Click on ‘Microsoft Office‘ button and then click on ‘Excel Options‘.
2. Click on ‘Proofing‘ category and then click on ‘Auto Correct Options‘.
3. In the ‘Auto Correct‘ dialogue box appears, click the ‘Smart Tags‘ Tab.
4. Tick the boxes, next to the Smart Tags you wish to use in Excel
Move to edge of data block
When data is held in a block, however large, use the Ctrl key with your cursors to move quickly to the far edge of the block. Works with all 4 direction keys.
Multiple Lines of Text in a Cell
As an alternative to the Text Wrapping facility, type a word or two, press Alt+Enter to get a new line, type more text, and continue the process for as many lines as you need. Enter as normal when you have finished.
The line break is not affected by changing the column width, as text wrapping. To remove this you must edit the cell and remove the invisible character and replace with a normal space.
Switching Between Spreadsheets
As the Alt+Tab key switches between loaded applications or files, Ctrl+Tab switches between loaded or open Excel files. Hold down the Ctrl key until you have tabbed to the correct spreadsheet.
Screen Splitters in Excel
Screen splitter icons can be set from the ribbon bar, or dragged from the scroll bars. The icon just above the up arrow on the right scroll bar controls the horizontal splitter; the icon to the right of the right arrow on the bottom scroll bar controls the vertical splitter.
These icons can be double-clicked to split the screen at the top left of the cell currently in use.
Move or Highlight Cells
Use any of your movement keys, cursor, Home, End, PgUp or PgDn to highlight cells rows or columns by holding down the Shift key as you move.
Use in combination with the Ctrl key for quicker movements.
Jumping Across the Excel Screen
PgDn and PgUp keys scrolls up and down a screen page in most applications.
Alt+PgDn and Alt+PgUp is the equivalent across the spreadsheet.
Jumping Between Sheets in a Book
PgDn and PgUp keys scrolls up and down a screen page in most applications.
Ctrl+PgDn and Ctrl+PgUp keys jump from one sheet in your workbook to the next, up or down through the pages.
Quick Absolute Cell References
When entering cell references in a formula you probably click the cell, or you may type in the cell reference. If you require any of the 4 variations, press the F4 key now before you press Enter to toggle around the relative and absolute entries ($signs).
Note that the F4 key outside of editing a formula is the Repeat key to repeat a previous action.
Enter formulae into multiple cells
If a formula is to be used in a series of cells, select the cells first. Now type in your formula and hold the Ctrl key while you press Enter. This enters the formula in each selected cell.
Just be careful with your absolute and relative cell references.
Keyboard Shortcuts to Add Rows or Columns
Couple of other keyboard shortcuts. Shift+spacebar selects a row, Ctrl+spacebar selects a column. Select either row or column (or several) and use Ctrl and + to insert or Ctrl and - to delete rows or columns.
Using the Quick Access Toolbar in Excel2010
The Quick Access Toolbar is included in virtually every Office product, including Outlook 2010, Word 2010, Excel 2010, and PowerPoint 2010.
You will find the Quick Access Toolbar in the top-left side of the window. To begin, click the Customize button (it's the little black arrow at the end of the toolbar).
Simply click the commands you want to include.
Virtually any command can be added to the Quick Access Toolbar. Click the More Commands option and a new window will open from where you can browse the commands including those not on the ribbon.
How to apply the same formatting and data to multiple sheets at the same time in Excel 2010
When you want to format more than one sheet in a worksheet exactly the same way, Ctrl-click the tabs of the sheets you want to group together and they will all turn white. While they are grouped, anything you enter in one sheet gets entered into the others.
After you have done this, remember to click on the tabs to take them out of the group so that you do not accidentally insert data in multiple sheets when you just want to insert data in one.
Display Formulas Instead of Results in Excel 2010
By pressing Ctrl ~ once, Excel will display formulas rather than the results of the formulas. Press it again, and the results will appear again.
A much quicker and simpler way of displaying your formulas!
Use the Ctrl-key for quick navigation in Excel 2010
If you want to move quickly to the right, left, top or bottom of your spreadsheet, just press Ctrl and one of the arrow keys. If you want to then select all the data in that particular row or column, hold down the Shift key and press Ctrl and an arrow key.
Creating charts in Excel 2010
Here's how to present your data in a chart format:
Highlight the data you wish to use in a chart
Click the Insert Ribbon
In the Charts Group, select Column Chart
Your chart will then appear on your work sheet.
Conditional Formatting in Excel 2010
If you have lots of data in a spreadsheet, you may find that it is easier to read if you highlight some of the values. This is Conditional Formatting and here's how to use it:
1) Select the data you wish to apply the format to and click Conditional Formatting
2) A list of options will then appear, from this list, choose the format you wish to display e.g. find all cells with a value less than 0
3) Excel will then highlight all of these cells
To remove this: select the highlighted cells, click the drop down on the Conditional Formatting icon and select Clear Rules from selected cells.
Importing Numbers in Excel 2010
Occasionally, when importing data into Excel you find that the some of the imported values are treated as text.
To convert these numbers to actual values, click on an empty cell and press Ctrl+C.
Next, select the range that contains the values you need to change and in the Clipboard Group on the Home tab, click the Paste drop-down arrow and choose Paste Special. In the Paste Special dialog box, select Add and then click OK.
Copying Values Without Formulas in Excel 2010
If you want to copy the contents of a cell but don't want to copy the formula with it then use the following simple method:
Press Ctrl+C to copy the values in the cell. On the Home tab, click Paste and then click Paste Values.
Change the Default Width of All Columns in Excel 2010
If you want to change the width of the columns in your Excel 2010 spreadsheet, making them either larger or smaller, here's how:
In the Cells group on the Home tab, click Format.
Hover over the section called Cell Size and a drop down list will appear, select Default Width from this list.
In the Standard Width dialog box, enter the size you want to set as the default width and click OK.
Convert a column into row quickly in Excel 2010
Occasionally you might enter data into Excel vertically and then when you finish realize that actually it would look more clearer if it was represented in a horizontal format. If you follow these simple steps below, you can quickly change the data from going vertically to horizontally and vice versa.
First, select the column you want to convert into a row or a row into a column. Then right click and select Copy. Go to the sheet where you want to past this row as a column and select “Paste Special”. Remember to check the check-box “Transpose” and select “OK”.
Recovering Unsaved Work in Excel 2010
Ever closed your Excel workbook in a hurry without saving your work beforehand? Here's how to get it back.
Choose File then Info, click Manage Versions and then Recover Unsaved Workbooks which will automatically find the spreadsheets that haven't been saved.
Page Break Preview in Excel 2010 (Hint/tip)
If you select View then Workbook Views then Page Break Preview, you will be able to view how your Excel spreadsheet will be split across multiple pages when printed. Even better, you can also drag a page break to a new place. Excel will then scale down your entire worksheet to fit the information you want on the pages you want.
Convert Text to Columns in Excel 2010
If you have a cell in your Excel spreadsheet that contains a lot of text and you want to divide it into separate columns, this can only be done if there is a logical character which separates the text, for example, a comma.
Select the cells you would like to convert. On the Data tab, click Text to Columns. Choose the format of your current data.
Select Delimited if the text contains a logical character otherwise select Fixed Width if there are a certain number of spaces between each field.
Click Next when a preview of the data appears. Then select the type of character that separates the various fields. If the character is not listed, select Other and enter the character.
Click Next again and then choose the format for each of the columns. Select the column heading in the Data preview and then select a data type from the Column data format options.
Click Finish and the text will appear in several columns.
Saving your Excel Spreadsheet as a CSV File
In situations where you need to save your Excel spreadsheet as a CSV file, follow these simple steps.
Click the File tab and click Save As.
Enter a name in the File name field.
Click the drop-down arrow next to the Save as type field to select the file type. Scroll down the list and select CSV (comma delimited) (*.CSV)
The data will now be saved to a separate CSV file which can be used in different applications.
Filtering Data in an Excel 2010 Worksheet
When you have an Excel Worksheet with masses of data, it's not going to be easy to sift through it. So, in order to view sections of data, you can use the filter tool. Select the cells you want to filter (no need to select the column headers), then click the Home tab on the Ribbon, click Sort and Filter (you will find this in the Editing Section) then click Filter.
You will now see arrows in the top row of all the columns. If you click on an arrow, it will give you some filtering options so you can sort your data into ''Smallest to Largest'' or ''Oldest to Newest'' and so on..
To turn off the filtering, go back to the Home tab and click the Filter button again.
Brighten up your Excel 2010 Spreadsheet by changing the colours of the gridlines
Excel 2010 allows you to change the colour of grid lines instead of keeping them in boring black.
Select the File tab on the Ribbon, click Options, click Advanced, scroll down to ''Display options for this worksheet.'' Next to ''Gridline colour,'' choose your favourite colour, then once you've done this, click OK. Easy!
Moving or Copying Sheets Between Workbooks in Excel 2010
Here's how to move or copy sheets between workbooks in Excel 2010:
Open the sheet you want to move or copy then on the Ribbon click the Home tab. Click Format. Under Organize Sheets, select the option Move or Copy Sheet and then choose where you want the sheet to be moved/copied to.
Adding multiple rows in an Excel 2010 Worksheet
If you want to add more than one row to an Excel Worksheet, drag select the number or rows you want added to the spreadsheet.
Then right click on these selected rows, choose Insert from the menu, and the new rows will be added above the rows you first selected.
Adding Rows or Columns in an Excel 2010 Worksheet
If you want to add a row to an Excel spreadsheet, these are the simple steps you should take:
With your mouse, right click on the row header below where you want the new row to be added. Then, click Insert.
Follow exactly the same steps if you want to add a column to an Excel worksheet, right click on the column header, choose Insert and the new column will be inserted to the left of the selected column.
Hide columns in an Excel 2010 Worksheet
If you don’t want part of the Excel worksheet to be visible or when you don’t want certain data to appear in print outs, then a simple solution is to temporarily hide a column or multiple columns.
Hiding a single column:
1)Right click on the column header of the column you want to hide (this is the grey bar along the top edge of the worksheet)
2)Choose Hide from the menu
3)This column will now be hidden from view
Hide more than one column:
1)In the column header drag select to highlight the columns you want hidden
2)Right click and choose Hide from the menu
Hide separate columns in Excel 2010
If you want to hide columns not adjacent to each other for example, Columns A, C and E then:-
1) Click on the fist column to be hidden i.e. A
2) Press and hold down the CTRL key
3) While holding the CTRL key, left click on the rest of the columns you want to hide i.e. C and E
4) Right click and choose Hide
Some examples of CTRL key shortcuts in Excel 2010
Did you know that the old CTRL key shortcuts haven't changed from previous versions of Excel to the 2010 version?
They remain exactly the same:
Ctrl+B for bold
Ctrl+I for italics
Ctrl+P to Print
Ctrl+S to Save
Removing the Ribbon from view in Excel 2010
At times when you want to view the whole spreadsheet, try double clicking on the ''Home'' tab on the ribbon which will hide the ribbon from view.
Add Text to Displayed Numbers in Excel 2010
To add text to a number in a cell, you need to go to the Home tab on the Ribbon, and click on the Cells group. Select Format Cells from the drop down menu then Custom from the Category list. In the Type box select General. After the word General, enter a space, then opening quotation marks, then the word you want to type and then closing quotation marks. Click on OK and you have your text!
Paste with Live Preview in Excel 2010
Did you know you can preview what you are about to paste? Here's how to do it.
Copy what you are about to paste
Position the cursor where you want to paste
Right click to display the shortcut menu
Place the cursor over the Paste Options and this will give you a preview of the what you are about to paste.
Create Equations in Excel 2010
Here's how to create basic mathematical
equations in your Excel 2010 worksheet.
1) On the Ribbon, click the Insert tab
2) In the Symbols group, click the arrow next to Equation
3) Select from the equations and the equation will be inserted in a text box
Or you can create your own equation:
1) Insert and select the text box
2) On the Ribbon, click the Insert tab
3) In the Symbols group, click Equation
4) The Equation Tools Design Ribbon will now be displayed
Applying and removing border from cell in Excel 2010
Did you know the shortcut key for applying and removing the outline border for a cell?
CTRL+SHIFT+& Applies the outline border to the selected cells.
CTRL+SHIFT_ Removes the outline border from the selected cells.
The Easiest Way to See a Sum or Average in Excel 2010
Did you know the quickest and most simple way of working out the average or sum of a set of numbers?
Highlight numbers in cells or type some numbers in cells and then look at the status bar at the bottom of the window. Here you will see a display of the average of the numbers, as well as a count of the cells and the sum of these cells.
Viewing two Excel 2010 Workbooks at the same time
Did you know you can view two Excel 2010 workbooks side by side? Very useful when comparing data without constantly having to go back and forth!
1) Open both Excel workbooks
2) Select Window then select Compare Side by Side with (Spreadsheet 2)
3) When you have finished, select Window again and click Close Side by Side
Customize the toolbar in Excel 2010
You can create your own toolbar which contains your favourite or most used tools. This will make using Excel much more efficient. To do this, you need to click on View, then select Customize Quick access Toolbars and then select Customize. A list of tools will then appear on the screen of which you can add or remove them as you please.
Activating the formula bar with the keyboard in Excel 2010
The formula bar in Excel 2010 contains a number of different formulas. However, it is time consuming to open the formula bar every time and insert the formula using the mouse. Instead, you can activate the formula bar with the keyboard. Simply press the F2 button on the keyboard then the information in the cell will expand allowing you to edit the information in the cell.
The Easiest and Quickest Way to use Autosum in Excel 2010
Autosum is used frequently in Excel. As with almost every feature of Excel, there are more ways than one to use each feature. Below is the simplest way to use the Autosum feature.
1) Go to the bottom of the column of data.
2) Shortcut click in the column then Ctrl + down arrow
3) Use Alt + = for Autosum and press the enter key to complete.
How to select certain data in an Excel 2010 workbook
If you want to select the correct data set in a page full of data, the most accurate and efficient way of doing this is to use the ''Shift and Click'' technique.
For example: If you want to select all data in cells A2 to E10, then click on cell A2, hold down the Shift key and click on cell E10 and all the data you want to see is highlighted.
Keep holding down the Shift key and you can move from cell E10 to any other cell in the spreadsheet.
Excel 2010 Shortcuts - Start and End of the Worksheet
Did you know you can quickly move to either the beginning or the end of an Excel Worksheet using just a couple of keys on the keyboard?
Press Ctrl + Home key to move the cursor to cell A1
Press Ctrl + End key to move the cursor to the end of the current worksheet.
How to Spell Check an Excel 2010 Worksheet
Excel 2010 does not automatically spell check a document. So, here's how to manually spell check a worksheet.
Either select the ''Review'' tab in the Ribbon, go to the ''Proofing'' section and click ''Spelling.'' Or, simply press F7.
How to Remove Duplication's from a selection of data in an Excel 2010 Worksheet
It would take far too much time to scan through rows and rows of data to find and remove duplicate data. So, here's how to do this using a much more efficient method:-
1) Select the data that might contain duplication's
2) Click the ''Data'' tab
3) Go to ''Data Tools'' and click ''Remove Duplicates''
4) A box will appear. Tick the boxes of the columns that you want to use to check for duplicates. Finally, click ''OK.''
Wrapping Text in a Cell in an Excel 2010 Workbook
When you have a lot of text you want to put in a particular cell but you can't decrease the font size to fit because the text will then become ineligible, then manually wrap the text in a cell by simply pressing ALT+ENTER.
New to Excel 2010 - Sparklines!
Excel 2010 includes a new feature called Sparklines which are tiny charts that fit into a single cell and plot data in cells from the worksheet. There are a host of formatting and styles that can be applied to them and they are really quite interesting.
>Choose any style you want
You will be asked for the range and it will automatically select the cell your in to insert the sparklines.
Changing the Tab Colour of an Excel 2010 Worksheet
Did you know you could give the tabs in your worksheet different colours?
This is particularly useful when organizing all your worksheets relating to a particular period or year, for example.
Right click a tab
Select Tab Colour
And choose your favourite colour!
Create your own custom list on Excel 2010!
If you know how to use the auto-fill option on Excel then why not create your own customs lists?
The auto fill feature saves you time by allowing you to enter one of the list entries into a cell and then use your mouse to automatically drag the rest of the list into the cells below, above or to either side of the initial cell. When using your mouse to perform this task you will see a thin black cross appear at the bottom right hand side of the cell. Click, hold and drag to make the list appear.
Default lists include weekdays and months. To create your own list in Excel 2010 do the following;
>Scroll right to the bottom of the page and you will see a buttom "edit custom lists", click this button
>enter your list in the list entries
Now try it out. Good luck.
Rotating Text in an Excel 2010 Worksheet
Maybe you want to draw attention to certain text or you just simply want to make your worksheet look more exciting!
One of the things you might consider is, rotating the text in a particular cell or set of cells.
1) Select a cell you would like to rotate
2) Click the ''Home'' tab in the Ribbon
3) Click ''orientation'' in the ''Alignment'' section
4) A pop up menu will appear with a few choices, if you want to decide yourself how many degrees to rotate the text, then click ''Format Cell Alignment.''
Select the data and press the function key F11 and the chart will be created on a separate worksheet.
Copying Formulae Quickly
There is a quicker way of copying a formula down a column.
Just point and double click on the black autofill handle in the right hand corner of a cell.
This will work providing you have some data in the column to the left of the column.
Closing Multiple Workbooks quickly
When you have several workbooks open in Excel and want to just close them all at once:
1) Hold down the SHIFT key before selecting the File menu.
2) Once in File menu release SHIFT key and select Close All option.
3) All your files will close. If files require saving Excel will ask if you want to save the changes.
Missing Field handle
If your field handle goes missing all you need to do is go to tools > options > edit tab and then make sure that the check boxes for paste and insert buttons are checked.
Separate the year from a date
To separate the year from a date use the =year() function, eg a date is in cell A1 and in A2 you wish to display the year enter the function =year(A1)
Formula for last day of month
In some cases it is necessary to find the last day of a month for a given date. If you use the following formula, you can achieve this, ie; if you have a column of dates, use this formula to find the end of month for each day by using the fill handle. The formula is as follows, and assumes in this example that the first date in question is in cell C5, in any other cell type; =DATE(YEAR(C5),MONTH(C5)+1,1)-1
Select Single Data Marker
To select a single data marker in a chart, ie line, bar or column;
After you have pressed Ctrl+Click (to select the entire chart) you can press the Up or Down arrows to select a data series, then press the Left or Right arrow to select a data point within that series.
Using Alt in Save Dialog Box
When you are saving (or opening) a file, try these;
(Note the numbers are on the keyboard not the numeric keypad)
1. Go to previous folder Alt+1
2. Go up one folder level Alt+2
3. Search the Web Alt+3
4. Delete selected file Alt+4 or Delete
5. Create a new folder Alt+5
6. Cycle through all views Alt+6 repeatedly
7. Display the Tools menu Alt+7
New Normal Worksheet
Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)
Note: These changes are permanent changes on your PC.
Sorting List Subtotals
If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.
Manually rotating a 3D chart
To manually rotate a 3D chart;
Select the chart (edit mode) and click on one corner of the chart display area. A selection border will appear, using the left mouse button on one of the chart's "corners" (mouse pointer changes to a cross) drag the image which becomes a line representation of the chart (as you begin to drag) and rotate the chart to a view of your choice.
If you dont get it right first time, dont worry, the manoeuver is a little bit tricky.
Shared Conditional Formatting
In a shared workbook, conditional formats applied before the workbook was shared will continue to work; however you cannot modify the existing conditional formats or apply new ones.
Want all the zeros in your worksheet to appear blank? Choose Tools/Options, Click on the View Tab, Deselect the Zero Values Option, Click OK.
Easy wasn't it? But be aware that these cells are not actually blank, they still contain the value zero. This is important because certain functions (ie AVERAGE) make a distinction between blank cells and those with a zero value.
Remove unused toolbar buttons
Are there buttons on your Excel toolbars that you never use? Remove them from the toolbar by doing the following:
1. Go to Tools - Customise - Commands.
2. Select the toolbar button you wish to remove, then use your mouse to drag and drop the button into the Excel window. When you release your finger from the mouse, the button will disappear.
Reset Excel toolbars to default settings
If you find any of your toolbars in Excel have changed (i.e. they are missing or have extra buttons) then you can reset them to their defaults.
1. Go to Tools - Customise.
2. Select the Toolbars tab.
3. Select (highlight) the name of the toolbar you wish to reset, then click the Reset button on the right.
4. Close the dialogue box.
Printing spreadsheets without opening them first
Here's a fast way of printing a spreadsheet from Windows Explorer/My Computer.
Go to the location where the spreadsheet is saved on your computer, then right-click on the icon next to the document and select Print from the menu.
The spreadsheet will automatically open, print and close itself.
Find cells that match a format
In Excel you may wish to find cells that contain a specific formatting such s colour.
Select Edit > Find, click on Options and then Format...choose the formatting that you want found and click on OK
Choose one of the Find buttons to find.
Recently used file list
Under the File menu, you may find a list of files at the bottom of the menu. These files represent the most recently used Excel spreadsheets. This file list provides a quick way for you to access your files.
You can disable the file list feature of Excel. This is done by
1. Choose Tools > Options menu. You will see the Options dialog box.
2. Ensure the General tab is selected.
3. Make sure the Recently Used File List check box is cleared.
4. Click on OK.
Deleting a range of cells using the autofill handle
Firstly, select the range of cells for which you would like to clear the contents. Then drag the autofill handle to the the top left corner of the selection whilst holding down the shift key. Your selected contents should then be deleted.
Quickly select a block of data
To quickly select a block of data make sure your active cell is somewhere whithin the block of data and then press Ctrl+* or Ctrl+Shift+8.
Selecting blank cells within a range of data
Select the range of data which includes the blank cells that you would like to select. Press the F5 key, this will take you to the GoTo dialogue box where you can click on Special and then select Blanks.
Selecting constant values only
If periodically you need to change all your values back to zero, but leave formulas, text and blank cells as they are select the entire worksheet, choose F5 function key, Special and then Constants and choose the appropriate sub-selections. To enter zero in all the selected cells type 0 and then press Ctrl+Enter.
Copying the same value, label or formula quickly into a range of selected cells.
Select your range of cells. Type the value, label or formula that you want to appear in all the selected cells and then press Ctrl+Enter.
Change the default location for opening and saving spreadsheets
If you are always opening spreadsheets from and/or saving documents to a specific location that is not My Documents, save time by setting this folder as the default for opening files from and saving files to.
1. Go to Tools - Options.
2. Select the General tab.
3. Enter the pathname of the folder you wish to make the default in the Default File Location box (hint: it will be easier to use Windows Explorer to navigate to this folder, then copy and paste the pathname from the address bar at the top of the Windows Explorer screen).
4. Click OK.
You have now changed the default folder for opening and saving spreadsheets.
Shortcuts for working with named ranges in Excel
If you are working with or creating named ranges in your spreadsheets, then you may find the following shortcut keys useful.
- Bring up the Define Names dialogue box on screen by using Ctrl + F3 (instead of going to Insert - Names).
- Create Names from labels you have entered into the spreadsheet by highlighting the labels and related figures, then hold down Shift + Ctrl + F3. You can then choose to create names from the top or bottom rows, or left or right columns.
- Go directly to a named range by hitting the F5 key. The Go To dialogue box will open and display any named ranges in the spreadsheet. Simply select the named range to navigate to it in the spreadsheet.
Hiding a worksheet in Excel
Want to circulate an Excel workbook file but keep certain worksheets hidden from your colleagues' view?
You can do so by bringing up the sheet you wish to hide on your screen; then going to Format - Sheet - Hide.
It will not be immediately obvious that a sheet is hidden from view unless perhaps the sheet are still labelled Sheet 1, Sheet 2 etc.
To display the sheet again, you can go to Format - Sheet - Unhide on any of the other sheets in the workbook. A dialogue box will appear, allowing you to select the hidden sheet/s. Click OK to make the sheet/s reappear again.
Shortcut for accessing recently opened files
To get into recently opened Excel files without using your mouse, hold down Alt + F to open the File menu.
Recently opened files are listed down the bottom of the File menu - type in the number next to the file you wish to open and it should appear on your screen.
Moving between split pane sections in a spreadsheet
If you have used the split panes feature in your worksheet, use the following keyboard shortcut keys to move quickly and effortlessly between paned sections:
F6 - Move to the next pane
Shift + F6 - Move to the previous pane
Shortcut keys to move between sheets
Instead of clicking on a sheet tab to view a sheet, use the following keyboard shortcuts to move between sheets in the same file:
Ctrl + Page Down - Switch to the next worksheet (to the right)
Ctrl + Page Up - Switch to the previous worksheet (to the left)
Use RANDBETWEEN to generate random numbers
There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.
The function looks like this:
where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.
This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:
where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).
One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.
Using Excel's MODE function
Use Excel's MODE function to display the most common value present in a particular range of cells.
The Mode function looks like this:
As an example, if 35 is the most commonly recurring number in a particular cell range, then the function will display 35.
LARGE and SMALL functions and their uses
Two of Excel's most common functions are the MAX and MIN functions which will display the largest (MAX) or smallest (MIN) value in a series. What if you need the 2nd or 3rd largest or smallest values instead of the largest or smallest?
The =LARGE(array,n) returns the nth largest value of a series.
The =SMALL(array,n) function returns the nth smallest value of a series.
In both functions, 'n' represents the order of the number you want to display. For example, putting in 2 as n will give you the second highest number; putting in 3 as n will give you the third highest number.
Turn off AutoComplete in Excel
You may have noticed when typing into your spreadsheets that if you start to enter labels that begin with the same letters as a label that has been previously entered in the same spreadsheet, Excel will try and automatically complete the text for you. This feature is called AutoComplete.
If you find this feature more annoying than useful, you can turn it off by:
1. Going to Tools - Options.
2. Select the Edit tab.
3. Remove the tick from next to the "Enable AutoComplete for cell values" option.
4. Click OK.
Use the Format Painter to copy formatting more than once in Excel
The format painter tool provides a quick and easy way to copy formatting from one cell to another in Word.
The only problem is that if you click the Format Painter once to turn it on, you can only click and drag over a single cell or adjacent range of cells; then the Format Painter turns itself off automatically.
If you want to copy formatting to cells or groups of cells that are not adjacent to each other, double-click the Format Painter - this way you will be able to copy formatting to multiple cells.
When you have finished using Format Painter, press the Esc key or click on the Format Painter button once to turn it off.
Checking formulas with multiple operators
When dealing with formulas containing more than one operator (+, -, /, *), Excel follow standard BEDMAS order of operation rules. These rules specify the order that calculations will be performed in, regardless of how the formula reads left to right:
B = brackets
E = exponents
D = division
M = multiplication
A = addition
S = subtraction
It should be noted that multiplication and division are considered equal; as are addition and subtraction.
If you would like to check the order in which Excel is performing calculations in a formula, simply click on the cell containing the formula. Then go to Tools - Formula Auditing and select Evaluate Formula.
In the Evaluate Formula dialogue box that appears on your screen, click the Evaluate button to see how Excel calculates the formula result.
Changing Excel file and worksheet defaults
The appearance of any new Excel files or any new worksheets that are inserted into a file are controlled by two template files, Book.xlt and Sheet.xlt.
By opening, modifying and saving these templates you can change the default settings for all new files and/or all newly inserted sheets.
Use Book.xlt to make change to defaults for new workbooks; and Sheet.xlt to change defaults for sheets.
If you can't find either of these files on your computer, you can create and save them yourself.
You can do this simply by creating a new workbook with the setting you want to use as your defaults; then saving them with the appropriate name in the XLStart folder. If you are using Office 2003, this is usually found in C:\Program Files\Microsoft Office\Office11\XLStart.
Use Paint Brush to Format More than one Cell
To copy a format to many cells or ranges
1. Select the cell with the format that you want to copy
2. Double-click on the paint brush
When you move to the cell where you want to paste the format you will notice that a paint brush follows the cursor.
Paste the format to all the cells or ranges you need to format.
When finished go back to the paint brush on the tool bar and single click on it to deactivate the function.
Stop Formula Returning A "#DIV/0" Error
If a formula returns a #DIV/0 error message there is a way to avoid such results.
For example the formula =A1/B1 will return a #DIV/0 if B1 is empty or a zero.
If you protect your formulas with the ISERROR function, the formula will then look like this:
In plain English: should the result of A1 divided by B1 be an error change the result to 0 else show the result of A1/B1.
Make macros work in newer versions of Excel
If you have created macros in Excel 97 or 2000 that you want to be able to use in 2002/XP or 2003, you may need to alter the macro security settings in the newer version of Excel you are using.
To do this, go to Tools - Options - Security.
Select Macro Security and change the security setting to Low.
Tick the boxes next to 'Trust Add-ins' and 'Trust Visual Basic' and click OK.
After you have restarted Windows, you should then be able to use your macros created in earlier versions of Excel.
Use the SUBTOTAL function in Excel
You can create subtotals in your spreadsheet using the SUBTOTAL function, which looks like this:
9 represents the function being used (SUM), followed by the range of cells the function is operating on.
The neat thing about using the Subtotal function is that if you have used it several times in the same column or row, clicking on the AutoSum button at the end of the column or row will make Excel add only the results of cells containing the Subtotal function in that column or row.
Repeating headings on spreadsheets that print on more than one page
By default when you print a spreadsheet out and it prints on more than one page, the headings at the top and the side of the spreadsheet don't appear on all the pages following page 1.
To get Excel to repeat headings on all pages when printing, go to File - Page Setup - Sheet, then select the rows to repeat at the top of pages, and the columns to repeat at the side of pages by clicking on the red arrows at the right side of the two boxes under the 'Print titles' area. Then click OK.
If you view your spreadsheet in Print Preview, you should see the headings being repeated on each page.
Shortcut for deleting all comments in a spreadsheet
If you have entered multiple comments into a spreadsheet and wish to delete them all at once, you can achieve this by:
1. Holding down Ctrl, then Shift, then O - this will select all cells containing comments in the worksheet you are looking at.
2. Right-clicking on one of the selected cells, and selecting Delete Comment from the menu that appears.
3. Clicking anywhere else in the spreadsheet to deselect comments - all comments should have disappeared from the spreadsheet.
Change the Print button so it brings up the Print dialogue box
If you want to bring up the Print dialogue box to check your print settings when you hit the Print button, do the following:
1. Right-click on the toolbar that displays the Print button.
2. Select Customise.
3. Click on the Print button on the toolbar to select it, then hold the left mouse button down and drag the button towards the screen below. The button should come off the toolbar.
4. In the Customise dialogue box on your screen, select the Commands tab.
5. Select File from the Categories list, and then locate the Print... icon (looks like the normal Print button, but the word Print has three dots following it).
6. Click on the Print... icon to select it, then use your left mouse to drag and drop the icon onto the toolbar at the top of the screen.
7. Close the Customise dialogue box.
##### displaying in Excel
When you get a series of hash symbols (####) appearing in some of your cells in a spreadsheet, this can make you think that you've make some kind of mistake.
This is a common misconception - what this actually means is that the cell is not wide enough to fully display the content of the cell.
All you need to do to see what is actually in the cell is to widen the column that the cell is in.
Sorting data stored in rows
Primarily Excel is set up to sort data that is stored in columns rather than rows.
It is possible to get Excel to sort data stored in rows however.
Click in the row you want to sort, or select the cells in the rows you wish to sort.
Go to Data - Sort, then go to the Options button in the bottom left corner of the Sort dialogue box.
Select blank cells automatically
Get Excel to find any blank (empty) cells in a region for you by:
1. Selecting the appropriate region from your spreadsheet.
2. On the menu bar, go to Edit - Go to.
3. Click the 'Special' button, then select Blanks and click OK.
Quickly Adding New Worksheets
Want to place a new Excel worksheet before current worksheet. Use keystroke SHIFT+F11
Create a unique items table from a duplicating table
1. Ensure that your list has column headings
2. Select the entire list
3. From the menu bar, select DATA, FILTER, ADVANCED FILTER
4. Select "Filter the list, in place", and tick the "Unique Records Only" box
5. Click OK, filtered list appears.
Converting an American date format to European using Formula
Excel depending on your local setting will only pick up date values of the dd mmm yyyy oders as date type. If you import data from various sources including America their date order is different with data value in mmm dd yyyy, excel can only treat it as text indicated by left aligning it. To overcome this you have to do the the following.
1. Extract the date components mmm dd yyyy, by using the the Text functions LEFT, MID or RIGHT
2. Reorder dd mmm component and concatenate using "&" in the right order this will create a text string with the date in the right order it then needss to be converted to a value so excel can recognise it.
3. To convert to value encase in TEXT function.
4. Format to desired date format.
Change Excel's default font
You can change the default font and font size for all spreadsheets created in Excel by:
1. Going to Tools on the menu bar.
2. Select Options, then General.
3. Next to Standard Font you can change the font and font size.
4. Click OK.
Each new file you start from this point onward should use the font and font size you have selected.
Convert Text into Number
Some times numbers maybe imported in as text or you maybe concatenating numbers that form a text string that now are treated, because you had to extract them by Text functions
To convert Text into Number just encase the relevant cell reference or formula in the TEXT function. See Converting American Date to European hint
eg TEXT(Ref) or TEXT(formula)
Concatenating Results of Formulas
To concatenate the results of formulas simply add the "&" after the formula or function closing bracket.
see example Creating a range of monthly payments as text.
Creating a range of monthly payments as text
You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.
The text that would be "between Xamount and Yamount".
Here is how to do it.
1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.
See PMT under Excel Help
2. Nest these in the ROUND function to round decimals see ROUND under Excel Help
3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".
4. Concatenate the above to PMT function for the end range
="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)
Apply currency format quickly in Excel
To quickly apply the currency format to cell in your spreadsheet, select (highlight) the cells you wish to apply currency format to, then use Ctrl + Shift + $
This will apply a pounds symbol even though the $ key is pressed.
Select only cell that contain text to lock format
For selecting cells that only contain Text in Excel
By selecting cells that only contain text, you can delete, fill or protect cells of this type.
Use short cut to Go to box (F5) or Edit, Go to
In the dialog box, click special button & select Constants and only check text or any other desired type.
And text cells will all be highlighted for you to apply format. Please note only works on one sheet at a time.
Conditional formatting for cells that return text , not picked up by Go to command
If you have tried to format all cells containing text even those that display text as a result of a formula you may have had difficulty. As Go to command with constant selected does not pick up formulas that result in text.
Then try this. Select the range the formula cells appear in on your sheet. Format, select Conditional Formatting menu. In the dialog box under Condition 1, select "Formula Is" from drop down. Next to it in the Formula Box, enter the formula =Istext(A1. Click Format button , choose desired formatting settings and click OK. To go ahead and apply the conditional formatting click OK to accept
Reconciling a list to correspond with another
May have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way.
Create another column in the sheet that has the most items and type in the first cell
=Exact(text1,text2) text1 being the cell that you want compared with text2 cell reference. Drag to filldown and your first false will give you for first cells that does not match. Correct insert the row with data in other sheet and continue the process until all the data returns true. Delete the column inserted.
Quickly create a list of numbers
The fill handle in Excel has many uses. If a number is typed into a cell and then the fill handle is used, the number will be copied from one cell to the next.
If you wish to create a list of numbers that increment by one at a time, hold down Ctrl in conjunction with using the fill handle - this should give you a series of numbers (e.g. 1, 2, 3 etc) rather than copying a single number.
Counting Non Number Cells (Text)
If you try to use the COUNT FUNCTION =COUNT(Cell range)with a range of cells with numbers and or containing text fields you wil find that that the text cells will be excluded from the the count. If you want to include them try the the COUNTA FUNCTION =COUNTA(Cell range). This counts both text and number cell values.
Some times you want to check if there are cells missing data in your range. You can use the COUNTBLANK FUNCTION to acheive this. It is =COUNTBLANK(Range). Note Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.
Viewing Many Worksheets
If there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen - Right click on the navigation arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds.
Generating Random Numbers
To generate a random number in Excel use the = RAND() function.
The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)
Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.
Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive
Calculate difference between two times
For presenting the result in the standard time format (hours : minutes : seconds . Use the subtraction operator (-) to find the difference between times, and the TEXT function to format the returned value to text in a specific number format.
Hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.
Hours between two times (4)
Hours and minutes between two times (4:55)
Hours and seconds between two times (4:55:00)
Where B2 and A2 must hold the end time and start time respectively formatted as a time format
Colouring cells containing formulas
Cells in a worksheet can contain values or they can contain formulas. You may wish to identify all the cells in your worksheet that contain formulas by colouring those cells.
Follow these steps:
1. Choose Edit > Go To menu, or press either F5 or Ctrl+G. Excel displays the Go To dialog box.
2. Click Special. Excel displays the Go To Special dialog box.
3. Select the Formulas radio button option.
4. Select OK.
At this point, every formula cell in the worksheet is selected, and those cells can be coloured formatted as desired.
Large Icons on toolbar
You can make the buttons on your toolbars bigger by going to Tools / Customize / Options / select Large icons.
Showing all menu items
If you go into a pull down menu you usally find that you get a selection of items(this is the default) or sometimes everything. If you only get a selectiopn you have to go to the double arrows at the bottom of the menu and click it to get all of the hidden items.
To turn this off so that you always get everything, go to Tools / Customize / Options Tab / "Always show full menus". Make sure there is a tick in the box and you will always have every item.
Sum Up All the Values in A Column
If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:
However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.
To get around this you can sum all the values in a column using the following formula:
Which, in our example, would be:
NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.
The formula must be placed in any other column, EXCEPT the one being calculated.
Display pictures on Chart Data Point
Replacing a single chart data point bar with a picture.
Step 1: Left click on a bar. Then, wait, and do a second single click on the bar. This will select just one data point.
Step 2: Right click on the bar and select Format Data Point.
Step 3: On the fill effects tab, choose a picture. Browse for a picture for that bar. Indicate if you want it to be stretched or stacked. Repeat for each bar.
Create Charts with One keystroke
Create a graph with one click
1. Select your data.
2. Press F11.
3. You have a graph.
Cycling through Absoulte cell references
If you are working with formulas in excel and need to convert your formula to an absolute formula, instead on manually adding in the $dollar signs you can highlight the specific part of your formula and press the F4 key.
You can cycle through all the absolute options by pressing the button (up to four times)
editing a formula quickly
If you want to edit a fomrula or text quickly witin a cell instead of the formula bar, you can click either double click in the cell or press the F2 key
Pivot table grouping
If you want to group items in a pivot table together, simply highlight the labels for the fields either with the shift key (if adjacent)or with the contral key if they are not next to each other.
Right click and choose group. Give the cell a name.
When you double click on this cell it will either expamd or collapse your grouped area
Creating custom lists
In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.
This list has come from Tools- options and Custom lists.
Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.
All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.
Hiding Formulae in the Formula Bar
It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.
Step 1: Select Format > Cells > Protection.
Step 2: Tick Hidden option. Ensure Locked is ticked
Step 3: Select Tools > Protection > Protect Sheet
(Ensure "Protect worksheet and contents of locked cells" is ticked)
Difference between Two Dates
The DATEDIF function computes the difference between two dates. The DATEDIF function is as follows:
Please note that Date1 must be less than (earlier) or equal to Date2
Please note that Interval must be one of the following codes: "d" (in days), "m" (in months), "y" (in years) expressed in quotes
Move data worksheet to worksheet
To move data from one worksheet to another, highlight the data.
Select and hold down the ALT key and position the mouse on the border of the selection until the mouse pointer displays four-headed arrows.
Drag the selection down to the destination worksheet tab.
When the arrow touches the tab, Excel switches to the desired worksheet. Now drag the selection to the correct position. Let go of the mouse and then the ALT key.
To copy data from one worksheet to another, select and hold down the CTRL+ALT keystroke combination and perform the steps above.
Create a hyperlink navigation sheet
In large files, it is often useful to have a front sheet with hyperlinks to the key databases and summary calculations in your spreadsheet. Hyperlinks can save you and (more importantly) those less familiar with your spreadsheet a great deal of pointless scrolling between and within sheets.
Hyperlinks appear as underlined text and can jump to any cell or range name in your file. You can also use hyperlinks to jump to other files.
To create a hyperlink to a location in the active workbook: (1) Select the cell that contains the text you want to use as the hyperlink and choose Insert|Hyperlink.(2)Click Place in this document.(3)Choose the sheet you want to link to or the range name from the list of "Defined Names".(4)If necessary, type the cell reference in the Type in the cell reference box. (5) Click OK.
Go to source of a cell
The default setting in Excel is when you double click in a cell it actives the formula in the cell. If you have created a link and want to directly go to that link (say if on another sheet, click on Tools -options and take off the tick for eidt it directly in cell
Outlining - Grouping rows or columns
Highlight want you want to group and press ALT + SHIFT + left cursor arrow
Outlining - ungrouping rows or columns
Highlight want you want to ungroup and press ALT + SHIFT + right cursor arrow
autosum shortcut key
press ALT + =
removing border lines on the keyboard
Highlight your cell(s) that have boreders on them and press CTRL + SHIFT + _, this will then remove the border lines.
Hiding and unhiding columns using the keyboard
CTRL + 0 hides your columns and CTRL + SHIFT + ) unhides them although you would need to highlight the column letters either side as per normal
Hiding and unhiding rows using the keyboard
CTRL + 9 hides your columns and CTRL + SHIFT + ( unhides them although you would need to highlight the row letters either side as per normal
Trace Dependents / Precedents without the blue arrows
Rather than using the toolbar you can press CTRL+] which is the equivelent of trace dependants and CTRL+[ for precendants. Both of these ways though will not show the blue arrows but jump to the cell containing the formula.
paste functions box quickly
If you want to do a formula using the paste functions box press SHIFT + F3.
Adding cells, Rows & columns
place your cursor on a cell, row number or column letter and use CTRL + SHIFT + + or CTRL + + depending on which + you prefer to use.
Deleting cells, Rows & columns
place your cursor on a cell, row number or column letter and use CTRL + -.
Closing all your workbooks Quickly
Hold the SHIFT key down and using the mouse click on the file menu, it will now now CLOSE ALL rather than close. This closes all workbooks down but still leaves the application open.
Highlighting only Text cells
To select onlt text value cells in a spreadsheet, click on Edit-Go to(F5)
In the Go To dialog box, click Special.
In the Go To Special dialog box, select Constants.
Removing custom dictionary entries
If you add something to the custom dictionary in Excel you cannot remove it. The way to get around this is to go into word and remove it there.
Paste a web address into the hyperlink address field
If you copy a web address the only way to paste into the address field of the hyperlink box is to use CTRL + V. Right click paste does not work.
Adding up rows or columns without seeing formulas
Highlight a column or row and click the Autosum button. This gives you your answer without showing the formula.
Status Bar Functions
When using the SUM, MIN, MAX, AVERAGE and COUNT functions, the result of such calculations are displayed in the worksheet.
However, if a range of cells, containing numbers, is highlighted, and then a right-mouse click is performed at the bottom right side of the status bar, then the result of those functions will be displayed.
Get back to active cell
If you have scrolled away from the active celland want to get back t it quicly you can Ctrl and Backspace to get back
Line breaks in a cell
You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.
Click the cell where you want the label or heading to appear.
Type the first line of information.
Generate randon numbers
Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it:
Type =RAND() in a cell to generate a number between 0 and 1.
Type =RAND()*100 to generate a number between 1 and 100.
Auto-insert the current time
In Microsoft Excel, to enter the current time into a cell, hold CTRL+SHIFT and press SEMICOLON.
date and time
CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON
calculate age or service
The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip:
In a blank worksheet, type the birth date in cell A1, using slashes to separate day, month, and year.
In cell A2, type =DATEDIF(A1,TODAY(),"y") and press ENTER.
bring up formatting dialog box
You can transpose any range of cells, turning the columns into rows and the rows into columns. Just follow these steps:
Select the range.
Click the Copy button on the Standard toolbar to copy it to the Clipboard.
Select a cell outside of the range you copied.
Select Paste Special from the Edit menu.
In the Paste Special dialog box, click Transpose, then OK.
Number format shortcut
Ctrl+Shift+! applies the Number format, with two decimal places
Ctrl+Shift+$ applies the Currency format, with two decimal places
Ctrl+Shift+% applies the Percentage format, with no decimal places.
Adjusting the Elevation and Rotation in a 3-D Chart
For any 3-D chart you create, you can adjust the chart
edit and format multiple worksheets in one operation
To select two or more non-adjacent worksheets, click on the tab of each worksheet, while holding down the [CTRL] key.
To select two or more adjacent worksheets, click on the tab of the first worksheet and then on the tab of the last worksheet, while holding down the [SHIFT] key.
Enter or modify your data and apply the necessary formatting options to the sheet that's displayed
Finding cells that have data restrictions
Click anywhere on the worksheet.
On the Edit menu, click Go To.
Click Data validation.
Using an equal (=) sign that isn't part of a formula
Before you type the equal sign, type an apostrophe: '
Then type your equal sign: = (and anything else you want to add after your equal sign)
(the apostraphe will disappear
Autonumber in Excel
To create an autonumber field, can use the Offset() function.
In cell A1, enter the number 1.
Then in cell A2, enter this formula:
Then copy the formula from cell A2, down as far as you need.
See also: Autonumber in Excel forum post.
The DATEDIF function is a worksheet function that computes the difference between two dates. This function is documented only in the help files for Excel 2000. It isn't documented in Excel 2002.
the formula function is:
Y stands for year
COUNT function vs COUNTA function
The COUNTA function works in the same way as the COUNT function, except that it will count cells that contain text (labels) and also cells that contain numbers (values). The COUNT function will only count cells that contain numbers. Blank cells are not counted by either the COUNT or the COUNTA function.
Type =RAND()*200 to generate a number between 1 and 200.
Use the fill handle to drag down and populate as many cells as you'd like with random numbers.
Bracketed negative numbers
Often Excel users wish to display negative numbers in colour red and bracketed
Step1. Select Format > Cells menu options. Within Numbers tabsheet, select Category = Custom.
Step 2. Select a type such as #,##0;[Red]-#,##0;; that specifies a colour in square brackets.
Step 3. Amend as follows; #,##0;[Red](#,##0;;
Notes: Excel formatting featues are of the form
"Positive; Negative;Zero;Text" separated by semicolon.
Closing Multiple Open Worksheets At Once
When multiple Excel worksheets are opening, rather than performing a File > Close menu option multiple times, hold down the the SHIFT key and select the File > Close All menu option.
Close All menu option is only displayed when Shift key is down
Display Functions on Worksheets
Functions in Excel can be difficult to recall their format/syntax
For example, you want to use the =PMT function.
Enter =PMT, then select keystroke, CTRL+SHIFT+A.
This usful memory jog, will display the arguments of a function on a worksheet, allowing the user to proceed with the generation of the function
Highlighting a data range
Attempting to use a mouse to highlight a large range of cells with data in Excel can make the mouse to have a life of it's own!
Use keyboard strokes instead.
Step 1. Place the cusor in the cell where the highlighting should begin.
Step 2. Select keystroke, CTRL+SHIFT+END
This will take the cursor to the furthermost bottom corner of the data range found in that worksheet. And highlight that range of cells at the same time
Naming and Using Constants
Constants make calculations easier so worksheets are more easily understood. Constant values also need to be given relevant and memorably names. It is also easier to change the value of a constant.
Instead of entering 17.5% in each cell when you generate a VAT amount you could name a Constant "VAT" and assigning a "0.175" value to it. To do this:
From the 'Insert' menu select 'Name', then select 'Define'.
Enter the constant
Change the Value of a Constant
When using a named constant in a worksheet, you may wish to change the value of that constant.
From the 'Insert' menu, select 'Name', then select 'Define'.
In the 'Define Name' dialog box, select the constant that you want to change.
Change the value in the 'Refers To' box.
Wherever that named constant has been used it will now use its new value.
Hide data in Excel Worksheets
Let's say you have some data in cell 'C5' you would like to hide from the casual viewer.
Click cell 'C5' to select it.
Click the 'Format' menu, select 'Cells'. When the 'Format Cells' dialogue box opens, click the 'Numbers' tab (if necessary), then select 'Custom' from the 'Category' list.
Double-click the 'Type' entry box and type three semi-colons: ";;;"
Click 'OK' to close the dialogue box and accept the new formatting.
The data in cell 'C5' disappears. It's still there and will work in calculations, but it isn't visible.
If you need to check the data, just click the blank cell and the contents appear in the 'Formula bar'.
Checking if a calculation adheres to Order of Precedence
When writing formulas you must make sure that results will be calculated as you intended.
Excel adheres to the standard order of precedence for calculations. It calculates percentages, exponents, multiplication, and division in this order before calculating addition and subtraction.
For example, =7+5*3 results in an answer of 22, not 36.
To force a calculation to be completed before another calculations, place the section in parentheses: =(7+5)*3 will result in 36.
To check how excel is evaluating a formula, click on the cell and select the 'Tools' menu, select 'Formula Auditing' and click 'Evaluate Formula'
In the dialog box click on 'Evaluate' to watch as each part of the formula is successively calculated.
Working with 3D formulas
You can only use fill-down if you remove $ for Absolute References.
3D formulas find and replace
After you know all the components of a 3D reference, you can change them to suit by using a localised Find and replace crt+f, if need be.
Toggle Formulas and Results
Ctrl + 'The key above Tab with the
Editing Formulas in Excel Cells
Although people like to edit a formula in the Formula bar, you can also edit a formula in the cell.
To do this select the cell and press "F2". This puts Excel into Edit mode, and you can move around in the cell and make any necessary changes.
Double-clicking the cell also puts Excel in Edit mode.
Freeze Rows and Columns to keep lables displayed
You can freeze rows and columns in your worksheet so they don't move.
This allows you to keep row and column labels displayed on your screen as you move through a large worksheet.
Click below and/or to the right of the cell(s) you want to freeze. (NB. Excel freezes ALL the rows above and ALL the columns to left of the selected cell)
Click on the 'Windows' menu and selct 'Freeze Panes'.
Lines appear in your worksheet. The required rows and columns are frozen and remain on your screen as you move through your worksheet.
To unfreeze rows and columns, click on 'Window' menu and select 'Unfreeze Panes'.
Formst Excel to display leading zeroes
Select the cells that you want to have displaying leading zeroes.
From the 'Format' menu select 'Cells' (Or Right-click on the selected range and and select 'Format Cells').
In the 'Number' tab click on 'Custom' in the 'Category' window.
In the 'Type:' box enter zeroes that correspond to the size of the required number (eg 5 zeroes).
When you enter numbers into these cells, leading zeroes will be displayed.
eg 123 = 00123
Moving between Worksheets without using the mouse
Use the 'Ctrl+PgDn' and 'Ctrl+PgUp' keys.
'Ctrl+PgDn' will move to the right and 'Ctrl+PgUp' will move to the left one worksheet at a time.
Entering text in Multiple Worksheets
If you have a number of worksheets in a workbook that require the same information (data or tables) on each worksheet, this can be done as follows:
Hold down the Ctrl key and click one or more of the additional worksheet tabs (i.e. Sheet2, Sheet3, etc).
In your mainsheet (Sheet1) enter the required data or design a table.
When done 'Click' on the other sheet tabs and you will see that the information entered in Sheet1 is on all the other selected worksheets.
NB Do not forget to deselect the worksheets - otherwise you may add data to the main worksheet and all the selected worksheets will also have that data!
Seeing named ranges as part of the zoom
If you have large areas of named ranges this works better.
If you zoom down to 39% you will see your named range.
Selecting your working range
In excel if you have an area you want to highlight, press Ctrl + * or Ctrl+Shift+8 (to get the *). This will select your working range.
Quick Zooming in Excel with rollerball mouses
To zoom in and out of your page hold down the control key and roll the wheel up and down. This will zoom up and down 15% at a time.
Adding up time greater than 24 hours
When you add up time if it exceeds 24 hours i.e 27 hours appears as 03:00. Go to Format / Cells / Number / Custom. The format is hh:mm but if change it to [hh]:mm it will add up to the correct amount of hours.
Get to the end of the used range
View a unique list
You have a column with hundreds of entries, and you need to see what unique items are entered in it. Select any cell in that column, hold down Alt and press the down arrow: Excel produces an alphabetically-sorted list of unique entries in that column.
Adding date and time
Here are two quick ways to add the date and time to your spreadsheet:
1) Type =NOW(), which displays both date and time in the same cell
2) Hold Ctrl and type the colon (:) into one cell for the date and the semi-colon(;)into another for the time.
Note that =NOW() updates to the current date/time whenever the spreadsheet recalculates.
Quickly insert a function
In Excel 97 and 2000 it was known as the Paste Function dialog box, these days it's known as the Insert Function dialog box. Regardless, one has to choose Insert|Function. or the fx button to open it up. There is, however, a non-mousey way to get hold of the Insert Function dialog box: press Shift+F3 in a blank cell to open the Insert Function dialog.
Press Shift+F3 after a function name and open bracket to open the Function Arguments dialog. For example, type =VLOOKUP( into a cell and press Shift+F3 to obtain a detailed description of VLOOKUP's arguments.
The dual nature of toolbar buttons
Many toolbar buttons are dual purpose, though the two purposes are often linked in some way. For example, Align Left aligns a cell's contents to the left of the cell. However, hold down Shift and press the Align Left button: Excel aligns the cell contents to the right.
You may respond: So what? Well, you can reduce the number of buttons on your toolbar to make your screen less cluttered and allow more room for, perhaps, some of your own commands. After all, what's the point of an Align Right button when Shift+Align Left does the same thing?
Quickly hide and unhide rows and columns
Use the keyboard shortcut Ctrl+9 to hide selected rows and Ctrl+0 to hide selected columns. The good thing about this shortcut is that you do not need to select entire rows or columns. For example, select B3:D3 then press Ctrl+0 to hide columns B to D.
Ctrl+Shift+9 unhides rows and Ctrl+Shift+0 unhides columns.
Do a fast scroll
In big Excel databases with many records, you can move down thousands of rows super-fast as follows: hold down Shift then click on the scrollbar somewhere below the scrollbar handle. This will move you way down the sheet without your having to use the scrollbar up/down arrows or drag on the scrollbar handle.
Fill formulae across a sheet
To copy a formula down a spreadsheet where there is data underneath, to the left or to the right of the formula, double-click on the fill handle. The fill handle is the little black cross that appears in the bottom right-hand corner of the formula cell. Unfortunately, no similar facility exists to copy formulae across the sheet.
One reasonably quick way to copy an existing formula across a sheet is to select the formula and the cells on the right to which you want to copy it. Then press Ctrl+R to copy the formula across the selected range, or, if you are menu-minded, use the Edit|Fill|Right command.
Turn Function tooltips on and off
Excel 2002 (XP) and Excel 2003 have the Function tooltips facility. When you type in a function name followed by a bracket, for example, =IF(, a yellow box appears beside the function name and lists the function's arguments. This is very useful when you can't quite remember the order of a function's arguments or what the arguments actually are!
However, Function tooltips can become annoying. To turn them off, choose Tools|Options. and select the General tab. Then, untick the Function tooltips box and choose OK.
Create and delete borders
To put a border around the outside of a selected range, press Ctrl+Shift+&. Use Ctrl+Shift+_ (underscore) to remove any borders from a range.
Return to the active cell after scrolling
When I scroll a long way down the screen from a selected cell, I can return to that cell with the Ctrl+Back Space shortcut. The active cell now appears in roughly the middle of the screen.
Shift+Back Space does something similar. Scroll down from the active cell and Shift+Back Space returns me to it and puts the active cell at the top of the screen; scroll up from the active cell and Shift+Back Space returns me to it and puts the active cell at the bottom of the screen.
Note also, that while Ctrl+Back Space will return me back to a selected range, Shift+Back Space only ever returns me to the active cell, which is normally at the top left-hand corner of any selected range.
Ctrl+d's double life
Suppose I have a formula in B1 that I wish to copy into B2:B10. I can select B1:B10 then press Ctrl+d to copy the formula down the selected range. Users generally ignore this shortcut in favour of double-clicking on the fill handle to copy down, but Ctrl+d is useful sometimes particularly when there is no data in surrounding columns to guide to how far the double-click method should copy formulae.
Ctrl+d has another use though. When I use the drawing toolbar to draw objects such as Text Boxes, Rectangles and Ovals onto a worksheet, Ctrl+d makes an instant duplicate of selected shapes. For example, I need five Text Boxes the same size. I draw one Text box and adjust it to the size I want, select it, then press Ctrl+d four times to get four identical copies.
Use shortcut keys to select rows or columns
Most users use the mouse to select rows or columns. It may be more convenient to use keyboard shortcuts to do that.
The shortcut key combination to select an entire row is Shift+Spacebar.
The shortcut key combination to select an entire column is Ctrl+Spacebar.
These are pretty easy to remember as the spacebar looks like a long row (or column if you're looking at it sideways). Remember that Ctrl, beginning with C, selects columns and Shift, by a process of elimination, the rows.
Using basic functions without doing formulas
When you highlight figures Autocalc tells you the total in the bottom right of the screen, but if you right click on the sum it will give you some basic functions. The functions are Min, Max, Average, Sum, Count, and Count Nums.
Creating Quick Column Charts
Select the information you want to appear in the chart and press F11. This creates a new chart on a seperate worksheet.
Finding your worksheets quickly
The arrows to the left of the worksheets are used to move between one sheet at a time or first / last worksheet, but if you right click on the arrow buttons it gives you all worksheets in your workbook.
Make a quick copy of a worksheet
Hold down the Ctrl key, then click and drag on a sheet tab to make a copy of that sheet. Though this process usefully copies the formats of the original sheet, note that any Range Names you have on the original sheet will be duplicated too.
To make a copy of a worksheet's contents and formats without duplicating range names: (1) Ensure that you have a blank worksheet to paste to. (2) On the sheet to copy, click on the sheet selection square to the left of Column A's heading to select the whole sheet. (2) Copy the whole sheet. (3) Paste to the blank worksheet.
Quickly copy a formula across sheets
Suppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets.
Remember to ungroup the sheets afterwards! Right-click on any tab and choose Ungroup Sheets to do that.
Generating simple column charts
1.Select cell range containing data/figures
2. Press F11
The F4 key will usually repeat your last action. e.g. delete a row, then select another row and press F4 to delete again.
Adding a comment to a formula
1. At the end of the formula, add a + (plus) sign.
2. Type the letter N, and in parentheses, type your comment in quotation marks.
=CurrentAssets / CurrentLiabilities+ N("The formula returns Current Ratio")
Manually wrapping text
To manually wrap text, use the shortcut key Alt+Enter.
To cancel manual text wrapping, simply delete the new line.
Validating text entries
1. Select the range of cells.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:
where A1 is the first cell in the range.
6. Click OK.