Excel Pivot Table – Data source reference not valid

If you have are trying to create a pivot table and are getting the error “Data source reference not valid”  It’s ususally caused by excel sheets with square brackets in the title [ remove these and it should work fine]

 

Store mobile numbers in Excel

When storing mobile numbers in excel you will often find that unless you put a ‘ in front of the mobile number (Storing it as text) Then the 0 at the start of the number will disappear.

Heres my solution

How to store phone numbers in excel

When storing phone numbers in excel you will generally find that unless you put a ‘ in front of the number (Storing it as text) The 0 at the start of the number will disappear.

Heres my solution

How to store phone numbers in excel

Rotate a table in microsoft excel

To rotate a table in excel 90 degrees

Destination reference is not valid in pivot table wizard

I was getting the following error in Excel 2010 today while trying to prepare a pivot table. “Data source reference is not valid”

When I copy and pasted the data into an entirely new worksheet it worked fine. The original spreadsheet was downloaded from the internet and it seems that excel throws up this error if you try to run the pivot table while the spreadsheet is stored in a temporary folder.

Hope this helps someone,

How to remove the password from an excel sheet

If you have an excel sheet you’ve manged to forget the password for dont panic its easily fixed:

Firstly open the spreadsheet and go to the VB editor

Paste in the code below and close the VB editor

Go to the ‘View’ tab and ‘View Macros’

You should see RemovePassowrd

Select this Macro and give it time to remove your password!

Thats it!

Contact training@domybooks.ie for Microsoft excel training

Sub Removepassword()
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66
For j = 65 To 66
For k = 65 To 66
For l = 65 To 66
For m = 65 To 66
For i1 = 65 To 66
For i2 = 65 To 66
For i3 = 65 To 66
For i4 = 65 To 66
For i5 = 65 To 66
For i6 = 65 To 66
For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) _
& Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox “The Password has been removed For more excel advice see www.domybooks.ie/Blog”
Exit Sub
End If
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
End Sub

This is the Blog of an Accountant based in Galway providing Bookkeeping services and accountancy software

Microsoft Office 2010

When were finally getting used to the new interface on MS Office 2007, Microsoft have gone and launched Office 2010.

You can download a beta version of microsoft office 2010 here

Watch out for a full review of excel 2010 on this blog over the next week or so

Remember Do My Books provide a complete Microsoft Excel training package with MCAS certified trainers.