Contextures News

Excel Data Bar Tricks

Oct 10, 2017

Data bar tricks, dashboard tips, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Dashboard Tips

If you're setting up a dashboard in Excel, what information should you show there, and how should you build the dashboard?

• First, if you're choosing content for your dashboard, see the problems with real-time dashboards. The article was easy to read, and I liked the list of simple changes that you can make, to improve your dashboard.
• Next, if you don't mind a bit of mild language, watch this short video about proactive dashboards. The presenter won't be to all tastes, but he makes good points about what to track.
• Now, if you're ready to start building dashboards, sign up for Mynda Treacy's highly-rated Excel Dashboard Course. Don't wait -- registration closes this Thursday, October 12th, at 8 PM Pacific time. Mynda also has Power BI Course, and it is open until Thursday too.

Data Bar Tricks

To make a chart right in the worksheet cells, use Excel's Data Bars, built with conditional formatting. They're really easy to add (Excel 2007 and later), and I'll show a couple of trick for improving them.

To add data bars, follow these steps:

• Select the cells with numbers (don't include any row or column totals).
• On the Ribbon's Home tab, click Conditional Formatting.
• Click Data Bars, and then click one of the Data Bar options

To improve the data bars, try these trick:

• If possible, make the column wider, so it's easier to see the differences among the bars
• In the Conditional Formatting list, click Manage Rules and edit your Data Bars rule.
  • To hide the numbers, add a check mark to Show Bar Only
  • To add a space at the right end of the bar, use a formula for the Minimum and/or Maximum. In my sample file, the Max formula finds the highest number, and increases that by 10%: =MAX($G$4:$G$9)*1.1

Learn more about data bars on my Contextures website, and download the sample file on that page, to see how they work. There's a short video there too.

Quick Tip

To divide a number by 100, use the % sign, instead of typing "/100". For example, to divide the number in cell B3, use this formula: =B3%

You can use multiple % signs too, as you can see in the screen shot below. Each % adds 2 zeros to the divisor. Thanks to UniMord for sharing this tip!

There is a full list of Excel's calculation operators and precedence on the Microsoft website.

Excel Articles

Here are a couple of recent Excel articles that you might find useful or interesting.

Excel Tips - There are lots of articles with Excel tips, but this one is a little different -- 10 things you should NOT do in Excel. Do you break these rules? What else should we add to this list? (Level - All)

Excel Charts - Chandoo shows what Histograms and Pareto charts are, and how to build them, in both Excel 2016 and earlier versions. (Level - Intermediate)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Humour

Sewing Skills

Last week, my granddaughter and I worked on new bathrobe for her doll. It was her first sewing project, and she took her job very seriously. Now that she can follow sewing instructions, do you think she's ready for Excel formulas? Maybe I'll show her those next time.

That's it for this week! If you have any comments or questions, send me an email.

NOTE: If you have trouble with the images or links in this email, paste this URL into your web browser, to see the online version: https://www.contextures.com/newsletter/excelnews2017/20171010ctx.html

Debra Dalgleish
ddalgleish @ contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.