# Functions

Home/Functions

## Select Formula Cells Only

Select Formula cells only Is there a way to select all cells that have a formula? By Neale Blackwood Yes. Hold the Ctrl key and press the "a" key to select the whole sheet. Then press F5 (GOTO), click the Special button, then click the Formulas radio button and select the types of formula [...]

2017-11-02T15:38:14+08:00Functions|

## Rounding To 5 Cents

Rounding to 5 cents Is there a formula to round to the nearest five cents? By Neale Blackwood, 22 Jun 09 You can use the ROUND function to do it. If the number is in cell A1, this formula will work: =0.05 * ROUND(A1/0.05,0)  Example: This formula rounds 2.5 cents and 7.5 cents up [...]

2017-03-08T03:53:56+08:00Functions|

## 3D Sum Formula

3D Sum formula Is it possible to easily add up the same cell across spreadsheets? By Neale Blackwood Yes, these are called 3D formulae. To create a 3D sum formula that sums all the A4 cells in multiple sheets, open a new workbook, click the AutoSum toolbar icon, then click the Sheet2 tab, then [...]

2017-03-08T03:53:56+08:00Functions|

Dynamic Headings Is there a way to change headings based on a cell that has the current month? By Neale Blackwood Yes. If cell A1 contains Feb 03 as a date, then the following formula will create the heading - Report for Feb 03. ="Report for " &TEXT(A1, "mmm yy") Note the space after [...]

2017-03-08T03:53:56+08:00Functions|

Toolbar overload Help! I've added so many buttons to the toolbar its hard to find the one I'm after? By Neale Blackwood Two suggestions; the first is to right click the toolbar area. You will see a menu of toolbars. It's likely you don't use many of your buttons all the time. So it [...]

2017-03-08T03:53:56+08:00Functions|

## Auto Correct Explained

Auto Correct Explained How does Excel correct my typing errors? By Neale Blackwood Excel uses AutoCorrect to correct common typing errors. To see which words are included, click the Tools menu and click AutoCorrect. Use the scroll bar to view the combination of letters and symbols that are included. You can add your own [...]

2017-03-08T03:53:57+08:00Functions|

## Line Break Within A Cell

Line break within a cell Is it possible to insert a new line within a cell? By Neale Blackwood Yes, when typing in a cell hold down the Alt key and press Enter. This inserts a new line within the cell. You can insert as many lines as you want, but be warned that [...]

2017-03-08T03:53:57+08:00Functions|

## Use A Range Name In A Single Sheet

Use a Range Name in a single sheet Can I create a range name that only works in a single sheet? By Neale Blackwood Yes. To create a range name that only applies to a single sheet you type in the sheet name following by a "!" then the range name you require. To [...]

2017-03-08T03:53:57+08:00Functions|

## Dynamic Range Name

Dynamic Range Name What is a dynamic range? By Neale Blackwood A dynamic range is a range whose size changes as data is added or deleted in an area. You can use this technique to define a range for a Data Validation drop down list or define a variable length data range for a pivot table. [...]

2017-03-08T03:53:57+08:00Functions|

## Copy Quickly Between Sheets

Copy quickly between sheets Is there a quick way to copy between sheets in the same workbook? By Neale Blackwood Open another window of the workbook by clicking the Window menu and selecting New Window. You will notice that the sheet name in the title bar will have ":2" added. To see the two [...]

2017-03-08T03:53:57+08:00Functions|

## Dualing Screens

Dualing Screens I have recently upgraded to a computer with dual screens. Is it possible to display Excel across both screens and have one screen with one workbook and the other screen with another workbook? By Neale Blackwood To expand the Excel window across screens, first click the Restore Down button on the Excel [...]

2017-03-08T03:53:57+08:00Functions|

## Cumulative Summing

Cumulative Summing I use cumulative balance columns which add the figure to the left of the cell to the value in the row above the cell. When I insert a row I have to copy the formula from the row above to the new row and to the row below for the column to [...]

2017-03-08T03:53:57+08:00Functions|