No matter where you fall on the political spectrum, you’ll love the chart I’ve created, which you can adapt and use in your own work. The instructions are a bit complicated, so rather than go through every element with figures, I’ve written up what I would do assuming you’re watching and following along in the video.
Step 1 -Get the data
First, we’ll go to PollingReport.com to get the approval rating performance for Republican President Donald Trump.
We’re going to move the data from pollingreport.com into Excel. We’ll use Power Query to pull the data in. You can start by clicking Data > From Web. And pasting the correct URL into the dialog box that appears.
From here, you can select the table you’re interest in and select Edit to load Power Query
Step 2 – Using Power Query to Transform the Data
We remove the columns we don’t need by right-clicking each column we’re interested in and selecting remove. In the video, you’ll see I remove columns 1, 5 and 6.
I then remove the first three rows from Home > Remove Top Rows. I then change the name of each column name to make it more relevant.
The next step will be to normalize the dates across all the data points. To make things easy, date ranges are essentially combined into month. To complete this, I use the Split Column feature from on the Home tab. First, I split out the items to the left of the first slash in the date. Next, I split items by the right most slash. Finally I recombine everything using the Power Query Merge Columns feature to turn the dates into a monthly item.
Finally, to complete this step I ensure each column is set to the correct data type for the data house within. I convert the percentage values to decimal values and the new date column into date.
At the end of this step, we give it a solid name. And then we select Close and Load To…..
Step 3 – Building our Data Visualization Chart
I then create a Dashboard sheet. I insert a new chart from Insert > Scatter. Once I insert the chart, I right click and go to Select Data. In the series name, I add the Approval series. In the X-series, I select the data range. In the Y-series, I select the approval series. Both of these can be found on the Excel Data Table. I add both the Approve and Disapprove series.
Step 4 – Formatting the Chart
First, I set the Y-axis limits. I right click on the Y-axis and select Format Axis….I then set the upper and lower bounds to 65 and 30 respectively.
Second, I don’t like how the dates are being shown. Again, right-click format axis. I set the minimum and maximum date ranges. The date series type is being used in the bound input boxes. But you can simply type in the dates you want to set the range.
Next, we’ll remove chart junk by removing unnecessary lines. I then reformat the dates to month and year using format code “mmm yy.”
I then add a Chart Title in Design > Add Chart Element > Chart Title. I call it President Trump Job Approval vs Disapproval Ratings. I remove the gridlines by going to View > (uncheck) Gridlines to give myself whitespace.
I then add a Chart Legend by going to Design > Add Chart Element > Legend.
Next, I add a trendline by right clicking the series and selecting add trendline. The default trendline is dashed but I use Format > Shape Outline to set make it solid. I then delete the trendlines in the legend by clicking them twice and selecting Delete.
Step 5 – Adding a Slicer
To add a slicer, I go to Insert Slicer and select Poll Name. Because I locked in the axis, the data doesn’t resize. But it’s missing how many data points each slicer item represents.
To create slicers with captions I create a new table column called Poll Name (#). To create the caption I need, I use the formula =[@[Poll Name]] & ” (“& COUNTIFS([Poll Name],[@[Poll Name]]) & “)”. I then add another slicer based on that caption.
I think format the slicer by going to slicer options. To remove the header, by going to Options > Slicer Settings and unchecking Display Header. I also set it to 2 columns from the slicer options on the menu. Finally, I created an entirely new slicer style. I won’t try to explain this part. It’d just be easier if you watched the video.
Step 6 – Adding context
Adding the grey data point background isn’t hard. You just have to create another table of data. And then add both the approve and disapprove series back to the charts. You might have to reorder these series after adding them.
Once added, you’ll need to format them to be sufficiently a background color. A soft grey is the easiest way to go. The best way to format the series is right click > Format Data Series and then changing the fill options therein.
Finally remove these new series from the legend and that’s it!
Get the download file
Click here to get the download file!
How would you use this chart?
Politics aside, what did you think? How could you see yourself using this work? We can’t wait to find out.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020