25 Advanced Excel Chart Tips and Tricks (video)
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)
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.
In the video I am using Excel 365 on Windows 10, and most modern versions of Excel will look very similar. If you like the video, please subscribe to my YouTube channel, like the video on YouTube and add a comment below the video.
If you want to learn what you should do to create presentation-ready Excel charts that are easy to update and re-use, sign up for my free mini-course at https://thinkoutsidetheslide.teachable.com/courses/presentation-ready-excel-charts-that-are-easy-to-update-and-re-use/.
If you want quickly learn expert-level Excel techniques to create presentation-ready charts that are easy to update and re-use without programming, add-ins, or web tools, check out my Excel Chart Skills 501 course at https://thinkoutsidetheslide.teachable.com/p/excel-chart-skills-501/.
You can access the entire playlist of Excel Chart Tips videos at www.ExcelChartTips.com.
Dave Paradi has over twenty years of experience delivering customized training workshops to help business professionals improve their presentations. He has written nine books and over 100 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 less than ten people in North America recognized by Microsoft with the Most Valuable Professional Award for his contributions to the Excel and PowerPoint communities. He regularly presents highly rated sessions at national and regional conferences of financial professionals and is NASBA registered to deliver CPE credit courses to CPAs.