BONUS: Save your cheat sheet at the end for future reference.📄
It's a 4-steps process:
Let's get to the details on how to implement those step in this tutorial to save you hours in the future!
If you work on a Mac and you experienced difficulties connecting your SQL database, I’d like to tell you that I share your pain. I’ve been through it too… only to realize that there is kind of a bug on Excel for Mac 🤦♀️ (to be precise, I work on Microsoft Excel for Mac version 16.33). The good news is: I have the solution to your problem!
If you want to learn how to connect Excel to your SQL database with a Mac in order to query data from your SQL database into Excel, read the following lines.
You need an ODBC (Open DataBase Connector) to load data from a SQL database to Excel. In order to do so on Mac, you need to install an interface to manage ODBC configuration files. I’ve used ODBC Manager for instance. ODBC Manager is a Mac OS utility application that configures ODBC data sources such as Microsoft Excel. Mac OS used to include an ODBC Administrator utility but Apple stopped including the ODBC Administrator with Mac OS starting with the release of Mac OS X "Snow Leopard". ODBC Manager was developed as a replacement for ODBC Administrator.
You have to install a driver for your SQL database. For instance, I’m using MySQL so I installed the MySQL driver for Mac. You can download here the Microsoft ODBC Driver for SQL Server. Click here for the official PostgreSQL ODBC Driver.
Here might be the part where you struggled if you tried it by yourself. At least I did. To make it work, you need to move the SQL driver folder from /usr/local/ to /Library/ODBC/.
Why? Because the driver gets downloaded to /usr/local but Excel Microsoft Office is a sandboxed application, so it doesn't have access to /usr/local. Therefore, Excel doesn't know how to fetch the config file in the right place.
How did I figure this out? It happened after having wasted tons of time, growing increasingly frustrated and feeling totally stuck with the following error message:
If you got that one too, you know what I’m talking about! Help came from Stack verflow 🙏. I would never have guessed it and discover that many other people spent half a day looking for a solution. So here is the explanation for you to save time and move your MySQL ODBC driver to /Library/ODBC/:
You have to create a DSN (Data Source Name) config using the SQL driver in ODBC Manager and fill in the database connection information.
shell> myodbc-installer -a -s -t"DSN=mydb;DRIVER=MySQL ODBC 8.0 Driver;SERVER=mysql;USER=username;PASSWORD=pass"
shell> myodbc-installer -a -s -t"DSN=mydb;DRIVER=MySQL ODBC 5.3 Driver;SERVER=mysql;USER=username;PASSWORD=pass"
7. Enter the Data Source Name. Here, I named it “Sample”:
8. Enter the optional Description for the DSN. Here, I named it “Connection to sample my Sample database”. You can skip this step if you want:
9. Click Add to add a new keyword/value pair to the panel. You need to configure at least four pairs to specify the server, username, password and database connection parameters (further resources here on Connector/ODBC Connection Parameters):
10. Here will appear a first pair, set by default to “Keyword” and “Value”:
11. To modify its value, simply double click on “Keyword” to change it:
12. Here I want my first pair to be server=mysql so I replace “Keyword” by “server”:
13. And then double click on “Value”:
14. I replace here “Value” by ”mysql” and I click Add:
15. Another line set with “Keyword” and “Value” as default values will appear. I repeat the same process to setup my second keyword/value pair: user=sakila, click Add ; I continue with my third keyword/value pair: password=Sample, click Add ; and I finish with my fourth and last keyword/value pair: database=test_world, and finally click OK:
16. Hereunder is how my completed DSN configuration looks like with Data Source Name, Description and key=value pairs:
17. Click OK to add the DSN to the list of configured data source names and you’ll get a new line with you new DSN:
Now that you connected Excel to your SQL database, you’ll be able to query data from your SQL database directly into Excel using the pivot table feature, without needing to copy-paste it. Position yourself on a new worksheet in the cell where you want to insert your pivot table. We clicked on cell A1 here:
Let’s zoom in:
A Create Pivot Table dialog box will open:
Let’s zoom in:
This will open the iODBC Data Source Chooser dialog box on the User DSN tab.
You will see the list of your System Data Sources:
If needed, remember you can refer to the first part of this tutorial entitled “Connect Excel to an external data source: your SQL database”.
It’s quite common to get an error message at this point so, if it happens to you, no worries, we’ll cover it (otherwise, you rock, congrats for getting it to work on the first try 👏). Here is the error message I had for instance > “Alert [MySQL][ODBC 8.0(a) Driver]Unknown MySQL server host ‘Value’ (0)”:
This means that my host value had a wrong configuration. So I checked my dialog box and, indeed, it appeared that I had been forgetful when configuring my first keyword/value pair to the Data Source Name dialog box: I forgot to change the default “Value”🙃:
So I quickly corrected this oversight and clicked OK.
When Excel correctly connects to the MySQL server, the Microsoft Query dialog box opens:
Let’s have a closer look at the box here:
Only one table at a time should be selected in the SQL Statement. Once selected, the following query will automatically be created: “SELECT * FROM [table_name] ;”. If you know about SQL, you can also write your own custom SQL query here.
You’ll be brought back on the Create Pivot Table dialog box. Click on the OK button to get back to the pivot table:
At the end of the second part of this tutorial where you imported your SQL data into Excel, you get an empty pivot table with Pivot Table Fields on the right hand-side so that you can show the fields you want and rearrange them to create your own layout:
Here is a closer look at my PivotTable Fields, coming from my SQL database:
In order to build your pivot table, drag a field from the field section to an area in the areas section underneath, Columns, Rows or Values.
Here, I’d like to create a pivot table to show the Quarterly Revenue per Product. It means that I will need the list of products as rows, the quarters as columns and the revenues as values.
So I first dragged Product from the field section to the Rows area:
Then, the Quarter from the field section to the Columns area:
Finally, the Revenue from the field section to the Values area:
Here is a closer look:
Congrats on building your pivot table! 🎉
One question though: would you share the table as is? As for me, I deeply thank Microsoft engineers for having come with such a powerful tool for turning thousands of records of detailed data into a concise summary in a few clicks. But sorry folks, I don't share the same aesthetic tastes.🙈
Hereafter, I’ll show you how to transform this powerful-but-ugly pivot table and then use the GetPivotData function to populate a great-looking dashboard.
You’re in the right place if you want to learn how to update your great-looking dashboard automatically.💪
After this tutorial, never again you will have to painfully recreate pivot tables every week only to get results that you can format in a beautiful way.
Now, when new data will be added to your data set, you’ll refresh the pivot table, and your great-looking dashboard will automatically get new data from the pivot table, with a perfectly formatted report, ready to share and print.🤟
On a new worksheet, build your perfectly-looking report with no value inside.
Here is my initial pivot table:
And here is how I would rather present my data to my team:
What I initially thought I would do (and it would work as long as I didn't change anything) is that I would just hit an equal sign and then come over to the pivot table, left click in cell, hit enter:
And there you go, you have the dollar value that matches here:
Then, I could take my mouse, go into the lower right hand corner of the cell that I’ve selected until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:
If that's what I wanted, and I’d never change this pivot table, then I’d be fine, and just leave it like that. But, because this pivot table is dynamic, it’s going to break these formulas.🙀
Luckily, we don’t have to worry as there’s a way to get around these broken formulas, and it’s precisely what we’re going to explain here. You’ll learn how to specify the value you want to return from the pivot table using the function GETPIVOTDATA.😍
I have to admit I was a bit puzzled by the function syntax the first time I was trying to determine the arguments I should use. FYI, here is the official syntax:
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
One solution you could use, which is the easiest one but that I do NOT recommend to implement as it ends up taking way too much time, is to type =GETPIVOTDATA and click on the cell you want to retrieve data from. You will get the correct data but it won’t let you reuse the formula for other cell, forcing you to manually enter each cell data. Here the formula Excel automatically proposed to me when I typed =GETPIVOTDATA and selected the corresponding cell in the pivot table:
=GETPIVOTDATA("revenue",Sheet1!$A$1,"product","Career Transition Coaching","quarter","Q1")
The problem here is that, if I take my mouse, go into the lower right hand corner of the cell that I’ve selected until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:
Mmm… not very interesting. Why is that? Because I didn’t lock the field and item that are used by the function.
I had to go through a bunch of elliptical tutorials and felt no one was really clear. So I’d like to make it easy for you, and I believe that a graph is definitely the most simple way to understand how to write you GETPIVOTDATA function. Here is how:
So here is the syntax detailed description for you to fully grasp it:
The function syntax by the book:
GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)
The function used in our example, where it returns the quarterly revenue per product from a pivot table:
The GETPIVOTDATA function syntax has the following arguments:
Now that we wrote the function in a way that will allow us to work faster, let’s click on the cell with the GETPIVOTDATA function, go into the lower right hand corner of the cell until it becomes a plus sign, hold my left mouse key, and drag it down. I’d have all of these values coming up:
Yes! That’s what I wanted! 🥳. I do the same dragging it towards the right and I’m done:
Does your GETPIVOTDATA return the #REF! error value?
It might be for one of these reasons:
You can add a chart if you like:
Now, let’s test the automation of our table and ensure it updates if we change the data in our SQL database. So here is the initial data of my pivot table and the associated great-looking dashboard and chart:
Now, in my SQL database, I’ll boost the “Confidence Coaching” revenue in Q4 to $10,200. Let’s see how easy it is to automatically update the data in Excel. I click on Refresh all:
And boom, everything is refreshed in one click 😎:
You can keep your dashboard data up to date by refreshing the link to its source. Each time you refresh the connection, you see the most recent data, including anything that's new or has been deleted.
Save your cheat sheet for future reference: bookmark it, download it and share it with others who might also need it.
Oh, and if you think this whole thing is a painful process, feel free to check out our product Actiondesk, it's a spreadsheet software that lets you import and refresh data seamlessly from SQL, Stripe, Salesforce, Hubspot and more.