Lollipop chart

The most commonly used charts in Excel are Column and Bar Chart. Although they do the job perfectly, sometimes it is necessary to emphasize the displayed values. For this purpose, you can create a nice chart called “Lollipop” (or Lollypop). It is still not a part of Excel’s standard charts palette and in the following text I will show you how to create one by modifying the Clustered Column chart.

Pivot table bins

When we create a pivot table report, based on statistical data, it’s sometimes convenient to group them into bins so that we can analyze them more easily. In one simple case, where we research the age of the respondents and the answer to the question whether they are smokers or not, you will see how the groups (bins) are made and how can we later use them to create beautiful charts.

Pivot cache

The Pivot cache is automatically created each time you insert a Pivot table, and it is an object that replicates the data source based on which Pivot table is being made. Each time you need to change the look of the Pivot table, Excel automatically calls the cache, simply because it’s much faster – it handles local data, stored in the computer’s memory. However, what happens when a synchronization error occurs?

Forecast sheet

Forecast Sheet is an option used to calculate trends using specific forecasting functions. This is the Excel 2016 novelty. Based on the data provided for the appropriate time period, future values are forecasted and the lower and upper confidence interval is calculated. The calculated values are shown in the table along with a graph showing trends in data movement.


The Goal Seek option allows us to easily find a value that meets the set goal. However, this is an option of modest possibilities. To find the optimal calculated solution, by setting the target value and additional parameters, use the Excel Add-in named Solver. It is a complex tool, with many possibilities, which comes up to the target by applying one of the methods of Operational Research.