These 25 tips and tricks will help you take your skills in creating Excel charts/graphs to the next level. The techniques apply to all modern versions of Excel (Excel 2013, 2016, 2019, and Excel 365 that is part of Office 365). These tips don’t require VBA, plug-ins, or add-ins so you can apply them right away. Here are the Excel charting tips and tricks in this video (with the linked time code for that tip in the video):
- A shortcut key to access the format pane (0:04)
- Select any data series or point if you can’t click on it (1:39)
- Quickly filter the series and/or categories shown on a chart (3:04)
- Three ways to make gridlines less prominent so the data is the focus (5:35)
- Two-level category labels to make a crowded axis clearer (7:23)
- Use a data label on a single point to replace the legend in a line graph (9:10)
- Use colored text in the chart title to replace the legend in a column graph (12:01)
- Use a cell as the chart or axis title to make updating easier (13:36)
- Create custom formatted data labels that include text and symbols (16:10)
- Use custom number formatting to add indicators to data labels (19:43)
- Add the % change above a pair of related columns (22:57)
- Add explanations on a column or line chart without using a text box (27:55)
- How to hide data points or data labels you don’t want the viewer to see (31:20)
- Automatically label the minimum and maximum values on a line graph (34:04)
- Add a rolling average line to a column chart and have it update automatically (36:56)
- Add the change % from a previous period to the end of the bars in a bar chart (40:54)
- Add direction indicators for the change % at the end of bars in a bar chart (44:50)
- Create a column of text for explanations on the right side of a bar chart (49:52)
- Break a line graph into actual and estimated segments (54:05)
- Keep a line continuous when there is missing data (55:59)
- Compare actual to both last year and budget using dotted lines on a column chart (57:46)
- Visually show how segments add to a total (1:02:33)
- Show a range of possible values around an expected line on a line graph (1:05:14)
- Create a column chart where the colors are set by the values (1:07:25)
- The best way to copy a chart so it can be used for another region, department, or time period (1:12:28)
Full YouTube video
These tips and tricks are just a small part of what I share in my customized workshops for business professionals where I show them how to create clear visual messages of financial data. Learn more about my customized workshops and get inspired by examples of effective visuals at www.FinancialViz.com.
Dave Paradi has over twenty-two years of experience delivering customized training workshops to help business professionals improve their presentations. He has written ten books and over 600 articles on the topic of effective presentations and his ideas have appeared in publications around the world. His focus is on helping corporate professionals visually communicate the messages in their data so they don’t overwhelm and confuse executives. Dave is one of fewer than ten people in North America recognized by Microsoft with the Most Valuable Professional Award for his contributions to the Excel, PowerPoint, and Teams communities. His articles and videos on virtual presenting have been viewed over 4.8 million times and liked over 17,000 times on YouTube.