Geography & Stocks

Excel has not changed essentially from its origin, and one of its features is that it does not have data types other than text and numbers. All other (derived) types are only differently formatted numbers. In this respect, Excel 2019 (O365) brings two new types: Stocks and Geography. Since it is unlikely that any of you will be trading on stock exchange, this “recipe” will most of the time deal with geographical data and their application.

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.