Congratulations, you created your first Excel report! This was a great first quick and dirty report and now we need to review some Excel Table Best Practices. If you take just a few moments after initially creating a report to do a bit of proper setup, you’ll make your life easier down the road. Trust me on this one.
Connection properties is where details about your connection to SQL is stored within Excel. There are a few things you’ll want to edit for cleaner reading and also documentation. Yes, documentation. No, you’re not going to print a manual for the book shelves. Documentation is a note… a note to yourself in the future.
Under the Data tab in your ribbon, you’ll see a “Refresh All” with a small downward pointing triangle. Click the triangle, and then in the pull down, click “Connection Properties”. This will bring up a window with a few pieces of information you’ll want to edit.
Connection name: This is a narrative name for your connection into MS Query. Make this something useful for when you look at the report later on. For this report, something like “Adventure Works – SalesOrderHeader – Demo”.
Description: Here you can enter a nice short sentence or two to describe what you are doing and why. Remember, this is a note to your future self so be nice and complete!
Enable background processing. UnCheck this… turn it off so your report does not run in the background. On the surface, this sounds wrong… I mean, background processing… that sounds pretty cool and efficient! Well, actually no. For a simple report by itself, it can be OK. But if you ever build multiple queries or a Pivot table, you’re going to notice some strange things on the timing of the updates.
When completed, click “OK”.
Next, give your table a good name. This is different from the Query Name. The name is similar to a range name, and you will see it in formulas or pivot tables you create later on. It should be short and can have no spaces. Click Design and you ill see the Table Name on the left of the design ribbon. Change “Table_Query_from_Adventure_Works” to something like “SalesOrderHeader”.
The last thing we like to do before we dive into refining a report is to freeze the top line of the report. When you freeze the top line, it keeps the headers at the top when scrolling and we like knowing the column names! Make cell A1 your active cell, then click “Freeze Panes” and then “Freeze Panes” again (yes, it is two separate things).
BTW… Now would be a great time to save your file!
Next you’ll learn to use Multiple Tables For Useful Information!
