Analyze SQL Server Results in Excel with Conditional Formatting


By:   |   Updated: 2020-02-24   |   Comments (5)   |   Related: More > Microsoft Excel Integration

Problem

In this article I show a couple of very easy techniques for formatting data from SQL Server in Microsoft Excel that helps in analyzing results. In a previous article I demonstrated a technique for showing database activity based on transaction log activity.  In this solution I will use a similar result set and show how to use Excel conditional formatting to make it interesting and easy to analyze the results showing the lows and highs of transaction log activity.

Solution

In this solution I will show how to take a vanilla result set that is hard to interpret, such as Example A below and easily turn it in to an interpretable visualization like Example B below.

Example A

raw data

Example B

formatted data

Step 1 – Activity Based on Transaction Log Size – Database Level

In this example I am using the SUM aggregate to get total transaction log backup sizes in MB rolled up by hour for each day of the week. 

This has 2 parameters: (1) @NumWeeks which I will set to 8 weeks and (2) @Database which can be set to analyze a single database or set to NULL to analyze all the databases on a server with transaction log backups.

--Database LEVEL: SUM
--SUM: May be large for a large date range for analysis. this is my preferred over an 8 week period.
DECLARE @NumWeeks INT = 8 --<< SET the number of weeks to analyze.
DECLARE @Database nvarchar(100) = DB_Name() --'YourDBName'
SELECT 
   @@servername as 'Server', --For all full recovery model DBs 
   a.database_name,
   datepart(Hour,a.backup_start_date) as 'Hour',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Sunday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Sun_size(MB)',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Monday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Mon_size(MB)',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Tuesday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Tues_size(MB)',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Wednesday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Wed_size(MB)',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Thursday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Thur_size(MB)',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Friday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Fri_size(MB)',
   convert(INT, SUM(CASE WHEN datename(weekday,a.backup_start_date) = 'Saturday' THEN ceiling (a.backup_size/1048576.00) ELSE 0 END)) as 'Sat_size(MB)'
   ,MIN(a.backup_start_date ) as 'From:'
   ,MAX(a.backup_start_date ) as 'To:'
   ,COUNT(*) as 'NumberLogBackups'
FROM msdb.dbo.backupset a (NOLOCK)
WHERE a.type = 'L' --log backups
AND (a.database_name = @Database --Target Database 
      OR @Database IS NULL)
AND a.backup_start_date >= DATEADD(WEEK, DATEDIFF(WEEK,-1,GETDATE())-1*@NumWeeks,-1)  --Note: -1 Starts us at Sunday.
AND a.backup_start_date < DATEADD(WEEK, DATEDIFF(WEEK,-1,GETDATE()),-1)  
GROUP BY a.database_name, 
  datepart(Hour,a.backup_start_date)
ORDER BY a.database_name, 
  datepart(Hour,a.backup_start_date)
GO

Step 2 – Copy the Results and Paste to Excel Workbook

After running the query with results to grid, click the upper left corner of the result set grid and select Copy with Headers.

copy data from sql server

Step 3 – Paste the Results to Excel

Open Microsoft Excel and paste the results in cell A1.

paste data into Excel

Step 4 – Excel Standard Formatting to Table

With the Results highlighted, select "Insert" from the top menu, then Table.  In the Create Table dialog box check "My Table has headers". This turns your results into a nicely formatted table with alternating color rows and an automatic filter on each column.

format data in Excel

Below shows the formatted results. I do this often when I paste data into Excel. 

format data in Excel

Step 5 – Add Conditional Formatting

First highlight the data area to be formatted.  In this case all the data that comprises of the Transaction Log sizes. With the Results highlighted, select the Home tab, select Conditional Formatting and then "Color Scales".

format data in Excel

Step 6 Add Conditional Formatting

Next select the Color Scale Rules relevant for your results set.  In this case I select from Red to Green with red showing highest intensity.

format data in Excel

Final Results Conditional Formatting

Here are the final results.

I also, formatted dates in the From and To columns and shortened the Week day column names and adjusted the font size to make this easier to read. You can easily see the trends and the Hot Spots for the transaction log sizes each hour throughout the 8 weeks giving a visual of the normal weekly activity of the database.

format data in Excel

Wrap Up

I hope you find this demo useful in visualizing your database traffic patterns.  Certainly, you will be able to wow your co-workers when you share your results.  Look for other results set where using conditional formatting will help visualize the results.

Next Steps


Last Updated: 2020-02-24


get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager with Stericycle who has managed DBAs, BI Developer, and Data Management teams over the past 10 years.

View all my tips





Comments For This Article




Monday, February 24, 2020 - 4:38:41 PM - Jim Evans Back To Top (84775)

Thanks for the great comments Scott and Tim.  I too am a proponent on wrapping code in stored procs.  However, as Tim pointed out this article is focused on Excel's conditional formatting to visualize the data.

Though your topics on stored procs and Excel's parameterized data queries would make great topics to expound on. Thanks for reading and sharing your thoughts.


Monday, February 24, 2020 - 11:46:34 AM - Tim Emerick Back To Top (84770)

Agreed. I tend to create ad-hoc solutions for myself.  Publishing the solution for others to use would certainly benefit from the script being centralized at the server.  And yes to the security concerns!!


Monday, February 24, 2020 - 10:07:17 AM - Scott Stanek Back To Top (84766)

I tend to wrap things into a stored proc so that if I need to update it to add fields or format a particular way I don't have to go find every instance of Excel with the query to make them consistent. It also seems 'safer' to have a reporting database with stored procs that reference into the other databases on the system and grant permission in the reporting db only. (Not that I can claim this is ALWAYS the case, just a guideline).

I can see keeping the example simple and clean. (I was thinking non-parameterized Excel, but using a hardcoded parameter in the call from Excel) ;) --this would have resulted in 5 steps which might save one more TL;DR browser from wandering one, that is all. 


Monday, February 24, 2020 - 9:48:13 AM - Tim Emerick Back To Top (84764)

I was thinking the same thing as Scott Stanek then took a step back.  This is more about how to use Excel's conditional formatting to visualize the data than about using sp's and Excel's ETL capabilities.  In that spirit, well done explanation and easily usable for an excel novice.

Personally, I would take the next step as and Scott suggested and use Excel's parameterized data query entirely and probably roll it up into a pivot chart. The stored procedure may be unnecessary as excel could probably turn the entire thing into a native query, NOLOCK excepted.


Monday, February 24, 2020 - 8:52:12 AM - Scott Stanek Back To Top (84761)

Just curious why you wouldn't wrap the code you have in a stored procedure call and use Excel Data Query to call that stored proc with 'Exec ' as Step 2? The result would be refreshable, already is a table and would not require Step 3. For style points, you could pass the name of the database as a parameter so it would just be changing the "Exec" call to look into other places the same way.



download


Recommended Reading

How to Create a Burndown Chart in Excel from Scratch

Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

Passing Dynamic Query Values from Excel to SQL Server

Export data from SQL Server to Excel

Insert, Update or Delete Data in SQL Server from Excel





get free sql tips
agree to terms


Learn more about SQL Server tools