March 31, 2020
Formulas in Excel are already a programming language. When you build a model in Excel, you are essentially writing a program to calculate a set of outputs from a set of inputs. The Calc team Redmond have been working on a couple of enhancements to the Excel formula language to make Excel a bit more like a programming language.
March 30, 2020
At my last day job, we had three sales regions: East, Central, and West. The company headquarters was in the East, and so the rule was that all reports were sorted with the East region first, then Central, then West. Well, there is no way to do this with a normal sort.
March 26, 2020
Three easy visualization tools were added to the Conditional Formatting dropdown in Excel 2007: Color Scales, Data Bars, and Icon Sets.
March 25, 2020
Rather than creating a generic bell curve, how about plotting a list of employees or customers on a bell curve?
March 23, 2020
A bell curve is defined by an average and a standard deviation. In statistics, 68% of the population will fall within one standard deviation of the mean. 95% falls within two standard deviations of the mean. 99.73% will fall within three standard deviations of the mean.
March 19, 2020
Early in 2017, Map Charts appeared on the Insert tab in Office 365. A Map chart shades closed regions on a map such as countries, states, counties, even zip code boundaries.
March 18, 2020
In 2016, Office 365 introduced Funnel charts, as well as Treemap, Sunburst, Box & Whisker, Pareto, and Histogram charts. A Funnel chart is great for showing a sales funnel.
March 16, 2020
For 12 years, I worked at a company doing data analysis. One of my regular tasks was to analyze the profit on sales proposals before they went out the door. I did this with a waterfall chart. For me, the waterfall chart never would have to dip below the zero axis.
March 12, 2020
It is nearly impossible to read a chart where one series is dramatically larger than other series. In the following chart, the series for Year to Date Sales is 10 times larger than most of the monthly sales. The blue columns are shortened, and it will be difficult to see subtle changes in monthly sales.
March 11, 2020
It is easy to create interactive charts without using VBA. By default, if you hide rows in Excel, those rows will be hidden in the chart. The technique is to build a chart with every possible customer and then use a slicer or a filter to hide all except one of the customers.
March 9, 2020
You might be responsible for updating charts every month, week, or day. For example, in my last job, a collection of charts were updated during the month-end close process. The charts would track progress throughout the year.
March 5, 2020
One-click charts are easy: Select the data and press Alt+F1. What if you would rather create bar charts instead of the default clustered column chart?