When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.
Category Archives: Charts
Bold Your Headings
Apparently this is not widely known, but you should always bold the headings in your tables.
Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.
This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.
It also applies to the ranges used for charts.
In general ALWAYS BOLD your headings – it is something Excel looks for.
Ctrl + b is the bold shortcut.
Creating a Timeline Chart in Excel 2016
Error Bars plus a new feature
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Horizontal or Vertical Progress Bar in Excel
Conditional Format or Sparkline take your pick
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
An Easier Step Chart
A Line chart solution
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
TreeMap a new chart in Excel 2016
A square pie chart
The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy.
Step Charts in Excel
Hacking the error bars
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.
2018-05-15
2018-05-08
A good blog post on the when and how to use dotted lines in charts.
Dotted lines are great for lines you want on the chart but not featured.
Also for showing forecast values on the same chart as actuals.
Pie Charts and Negatives – an Alternative
A waterfall solution
Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out.
Cole Nussbaumer Knaflic
2018-04-27
2018-04-27
Don’t forget, many people (mainly men) have issues seeing all the colours.
2018-04-02
Jon Peltier is a legend when it comes to charts in Excel – check out his post on Gannt charts in Excel.
Gannt charts are a project management tool that help you visualise and understand the relationships between tasks and how the project time line is progressing.
Naming Charts in Excel
Two techniques
You may have noticed that Excel gives every chart a unique number when it creates the chart. It is displayed in the Name Box in the left corner above the grid. You have the ability to change that name and make it more descriptive.
Stop Charts Resizing
Works with other graphic objects like text boxes
We’ve all been there, our charts are looking just right and then some one inserts a column or changes the column width and throws out all our perfectly proportioned charts.
Hide and Seek with Excel Charts
Hiding rows and columns
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.
Cole Nussbaumer Knaflic
2017-10-30
2017-10-30
A nice example of how a chart changes as you develop the story and message behind it.
Note the additional change at the bottom of the post.
Katherine S. Rowell, M.S., M.H.A.
2017-08-29
2017-08-29
If you are ever tempted to start a column (bar) chart at a point other than zero, please read this post.
It also has suggestions for alternative chart types to handle comparisons.
Filtering Tip
Make your headings bold.
This tip applies to tables and to the structures you use for charts.
Excel looks for the bold format when it reviews tables and layouts to figure out if your table has a headings row.
You can use Ctrl + Shift + L to add or remove the filter icons to a data table. There is also an icon on Data ribbon tab.
This will work more reliably if the headings are bold.
I use the following keyboard combination on the top left corner of the table.
Ctrl + Shift + right arrow (this selects all the headings)
Ctrl + b (this applies bold to the headings)
Ctrl + Shift + L (to turn on filters)
This combination can be done very quickly.
You can just use Ctrl + Shift + L within the table, but sometimes this applies the filter to the wrong row.
I prefer to call them Bridge charts rather than Waterfall charts, but Waterfall is the common name.
Excel added Waterfalls in Excel 2016.
I think the name Bridge is more descriptive since a bridge takes you from one place to another which is what the chart does with values.
Waterfalls in nature only fall down, whilst a waterfall chart has measures that rise and fall.
This blog post show many examples. Most examples are not done in Excel.
Link to blog post.