Sometimes my customers ask me for data from their database that they want to see in a way they’ve never looked at or needed to look at it before.
When this happens to me and the answer is a data pull, I will often either pull up an existing query with the answer or create a new query. And then paste the results into Excel and format the data as a Data table. This allows a non-technical customer a lot of options for filtering and sorting in the Excel file by default. The best thing is, it’s pretty trivial to do.
In a real word example, I had a request to pull the vendors, vendor locations, and tax authority relationships so they could see which vendors and locations they needed to check which were using incorrect data they had entered for a tax authority.
There is exactly 1 tax authority defined for a location (typically a city tax percentage), and many locations for a vendor.
I just went to generatedata.com and created a random list of 100 vendors for this example (well, each vendor has a random human name, but hey, we can pretend). I exported this to a CSV file and imported it into Access to show how I can quickly generate a query and then paste the results into an Excel spreadsheet:
- I create a new query in Access using the Query Designer
- Next I view the query in datasheet view by right clicking on the query name and choosing “Datasheet”
- Then I right click on the upper left corner of the datasheet to select the entire table and query and choose to “Copy”.
- Now I’m ready to open a blank Excel sheet. I just have to right click on row 1 and choose “Paste”
- On the Home ribbon there is a control called Format as Table and with my cursor in the table, I can select a format type. I will choose the 6th row down in the first column.
- A create table dialog pops up and I click on OK
- After you format the data as a table, it will have the format you selected and have alternating row colors. I can quickly change the column sizes to fit the data and update the column names and voila! A spreadsheet my mother can be proud of.
You’ll notice that when Excel formats the table, it also puts controls in each column. These can be used by your customer to quickly filter and sort data. Now I just need to save it and send it to my customer. They appreciate the extra controls and the ability to manipulate the data easily. Thanks Excel!