The macro below will analyse your Excel workbook and create a list of pivot tables and their attributes in a new sheet. This is a great tool for auditing complex Excel spreadsheets containing multiple pivot tables.

Sub list_Pivots()

Dim pvt As PivotTable
Dim iSht As Long
Dim iRow As Integer

Application.ScreenUpdating = False
Set objNewSheet = Worksheets.Add
objNewSheet.Activate

iRow = 2
iSht = 2

'SET TITLES
Range("A1").FormulaR1C1 = "Name"
Range("B1").FormulaR1C1 = "Source"
Range("C1").FormulaR1C1 = "Refreshed by"
Range("D1").FormulaR1C1 = "Refreshed"
Range("E1").FormulaR1C1 = "Sheet"
Range("F1").FormulaR1C1 = "Location"

'GET PIVOT DETAILS
Do While iSht <= Worksheets.Count
Sheets(iSht).Select

For Each pvt In ActiveSheet.PivotTables
objNewSheet.Cells(iRow, 1).Value = pvt.Name
objNewSheet.Cells(iRow, 2).Value = pvt.SourceData
objNewSheet.Cells(iRow, 3).Value = pvt.RefreshName
objNewSheet.Cells(iRow, 4).Value = pvt.RefreshDate
objNewSheet.Cells(iRow, 5).Value = ActiveSheet.Name
objNewSheet.Cells(iRow, 6).Value = pvt.TableRange1.Address
iRow = iRow + 1
Next

iSht = iSht + 1
Loop

objNewSheet.Activate

Application.ScreenUpdating = True

End Sub

Excel's User defined functions give the user the ability to create their own custom functions. These UDFs can then be used in the same way as normal formulae such as SUM().

UDFs can either be attached to a workbook or to your personal workbook.

How to create and use UDFs

1.In Excel open the Visual Basic Editor (VBE). (Alt + F11).
2.Within VBE insert a new module in either your personal workbook or the workbook you are working on.
3. Enter your UDF code.
4. Save in VBE and close VBE window.
5. In the Excel workbook, type in your new formula.

Things to consider

• If the UDF is attached to the workbook it will only be available in that woorkbook.
• Using UDFs in a workbook will flag the workbook as containing macros. This may be an issue for file sharing.
• If the UDF is saved in your personal workbook the functional will not be available to other users sharing the workbook containing the formula.
• UDFs are often slower than standard Excel formulae.

A collection of our favourite UDFs

This UDF spreadsheet contains a collection of our favourite UDFs. The code for these UDFs is below:

IsFormula

We can customise Excel 2007 through the Excel Options area. If you have a spare few minutes it is well worth your time having a look through this area to see what options are available to you. In this quick tips post I discuss a few favourites.

To access all of the options below, click on the Office button and then Excel Options.

Colour Scheme

To change the colour scheme in Excel 2007 go to "Popular" > Color Scheme and then select from blue, silver or black.

Default number of sheet tabs

Excel defaults to three sheets when a new workbook is created. To change this default setting go to "Popular", under the section titled "when creating new workbooks" and then change the number next to "include this many sheets".

Show the developer toolbar on Ribbon

If you create and run a lot of macros in Excel 2007 it will be worth you switching on the developer toolbar on the Ribbon. In "Popular" tick the box next to "show the developer tab in the ribbon".

Use Table Names in formulae

When you create a formula based on a Table in Excel 2007 the formula includes the table references. Whilst this can be very helpful and allow you to create dynamic formulae there will also be occasions when you just want to write a straightforward formula.

To turn off the table functionality go to "Formulas" > "working with formulas" and untick the box next to "use table names in formulas".

Have you noticed that Excel will happily create a numbered drag down list for you but will not do the same for letters? To me this seems like a bit of an oversight but is easily fixed.

The solution is to create a custom list for the letters you require.

We have created a list for you in the file below.

Click to download file

To create this list we used the =CELL() formula with the address criteria and then stripped out the numbers and $ signs using the RIGHT() formula. Check out the file to see how it works.

To add our alphabetical list to a custom list:

Excel 2003: Select the list then click on Tools > Options > Custom Lists. Click on ok.

Excel 2007: Select the list then click on the office button (top left) > Excel Options > Edit Customs Lists. Click on ok.

You can now drag down letters alphabetically in your spreadsheets.

The Camera tool in Excel is a little known function but is one that can be really useful.It takes a linked copy of the image and pastes it as a picture. Any changes made to the source information will be reflected in the picture. This is particularly helpful if you want to see the impact of changing variables within a model.

One reason that it is hardly ever used is that it Microsoft chose to hide it away.

To add the Camera Tool to the Quick Access Toolbar (QAT) In Excel 2007:

• click on the dropdown arrow to the right of the QAT
• choose "more commands"
• choose "commands not in the Ribbon"
• click on the "add" button

Now that we have the tool on our QAT let start using it.

• highlight the area you wish to take a picture of
• click on the camera tool
• click the spot where you want the image to be located in the spreadsheet

=GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,item2,...)

GETPIVOTDATA allows an Excel user to directly reference a cell within a Pivot Table.

The easiest way to access this formula is to link directly into the Pivot Table cell from another cell. This will automatically generate a GETPIVOTDATA formula.

The GETPIVOTDATA formula allows you to build a report that is linked in to a Pivot Table. It is also possible to create a dynamic report by tweaking the GETPIVOTDATA from fixed values to cells in your new report.

So how do we turn off GETPIVOTDATA?

A quick way to avoid the GETPIVOTDATA formula is to simply type in the cell reference rather than selecting it with the mouse.

Disabling GETPIVOTDATA formula in Excel 2007

Activate the Pivot table ribbon by clicking anywhere on the Pivot Table. GETPIVOTDATA can be turned on or off by choosing Pivot Table Tools (opens pivot Table ribbon) > Options (far left dropdown list) on the ribbon.

Disabling GETPIVOTDATA formula in Excel 2003

The GETPIVOTDATA function is pretty well hidden in Excel 2003. Open the Pivot Tables Toolbar and then click on the down arrow next to the close button (X). The down arrow activates Toolbar Options. Click on Add or Remove Buttons > Pivot Table > Generate GetPivotData.

The GETPIVOTDATA formula is covered in our Pivot Tables course.

An absolute number is the number without its sign. E.g. the absolute value of 10 and -10 is 10.

To determine the absolute value of a number in Excel we can use the ABS() formula:

=ABS(Number)

But what if we have a list of numbers in Excel and we need to find the sum of the absolute numbers? One solution would be to create a helper column containing the ABS formula and then simply sum that column. But surely there must be a simpler way to do it? Of course there is:

=SUMPRODUCT(ABS(number range))

This uses both the SUMPRODUCT() and ABS() formulae.

A common example of implementation of this solution is when summing the differences returned in a reconciliation. Simply summing the differences is dangerous as you may end up netting down the differences. E.g. one difference of £1,000,000 would net down another difference of -£1,000,000 to £0 – i.e. there are no issues to report.

Using the SUMPRODUCT(ABS(number range)) formula would return £2,000,000 – i.e. there is an issue, please investigate.

Before this great new formula came along you needed to use the following formula to manage errors in Excel:

=IF(ISERROR(Value),value_if_error,value)

Whilst this worked perfectly well you ended up with a long and sometimes difficult to audit formula. Also because of the repetition of the original formula it was inefficient.

Here is the new formula:

=IFERROR(value,value_if_error)

A great step forward from Microsoft.

The next step for the Microsoft team will be to build the error handler into the original formulae themselves. Fingers crossed...

You can set a macro to run when an excel workbook is opened or when it is closed. This is particularly useful for setting protection or hiding/unhiding worksheets.

In order for these macros to work you need to enter the code into the workbook rather than in a module:

In VB explorer (ALT + F11 to open) > Microsoft Excel objects > This workbook.

Type in the following for action on open:

Private Sub Workbook_Open()

ENTER YOUR CODE

End Sub

or the following for on close:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ENTER YOUR CODE

End Sub

Do you need to generate some random numbers in Excel? Excel has two formulae to help you:

RAND() and RANDBETWEEN(Bottom,top)

RAND() generates a random number between 0 and 1.

RANDBETWEEN(Bottom,top) generates a random number between the numbers declared in the formula.

And just in case you need some VBA to create some random numbers, here you are:

Sub Random()

Dim LRandomNumber As Integer
Dim Low As Double
Dim High As Double
Dim i As Integer
Dim ix As Integer

'DEFINE NUMBERS TO RANDOMISE BETWEEN
Low = 1
High = 1000

'DEFINE NUMBER OF ITERATIONS
ix = 5000

i = 1

'GENERATE RANDOM NUMBER
Do While i <= ix
LRandomNumber = Int((High - Low + 1) * Rnd() + Low)

'MAKE CURRENT CELL = RANDOM NUMBER ABOVE
ActiveCell.Value = LRandomNumber

'MOVE DOWN TO NEXT CELL
ActiveCell.Offset(1, 0).Activate

i = i + 1

Loop

End Sub