Charts - Make your data presentable

One-click charts are easy: Select the data and press Alt+F1.

Press Alt+F1 and you get a default chart: Clustered Columns, with legend at the bottom and a title of Chart Title at the Top.

What if you would rather create bar charts instead of the default clustered column chart? To make your life easier, you can change the default chart type. Store your favorite chart settings in a template and then teach Excel to produce your favorite chart in response to Alt+F1.

Say that you want to clean up the chart above. All of those zeros on the left axis take up a lot of space without adding value. Double-click those numbers and change Display Units from None to Millions.

Change the Display Units for the chart axis. Choices are None, Hundreds, Thousands, 10000, 100000, Millions, and so on, up to Trillions.

To move the legend to the top, click the + sign next to the chart, choose the arrow to the right of Legend, and choose Top.

Change the color scheme to something that works with your company colors.

Right-click the chart and choose Save As Template. Then, give the template a name. (I called mine ClusteredColumn.)

Select a chart. In the Design tab of the Ribbon, choose Change Chart Type. Click on the Templates folder to see the template that you just created.

After setting up a template, the All Charts tab in the Change Chart Type dialog offers a new category at the top called Templates.

Right-click your template and choose Set As Default Chart.

In the Dialog box with all of the chart types, right-click on any chart tile and choose Set As Default Chart.

The next time you need to create a chart, select the data and press Alt+F1. All your favorite settings appear in the chart.

After customizing the Default Chart, the legends appear at the top, the left axis is in millions. Data labels (also in millions) appear above each column.

Comments

3D Map in Excel

3D Maps ( Power Map) is available in the Office 365 versions of Excel 2013 and all versions of Excel 2016. Using 3D Maps, you can build a pivot table on a map. You can fly through your data and animate the data over time. 3D Maps lets you see five dimensions: latitude, longitude, color, height, and time. Using it is a fascinating way to visualize large data sets. 3D Maps can work with simple one-sheet data sets or with multiple tables added to the Data Model. Select the data. On the Insert tab, choose 3D Map. (The icon is located to the right of the Charts group.) If you have Excel 2013 you might have to download Power Map Preview from Microsoft to use the feature. Next, you need to choose which fields are your geography fields. This could be Country, State, County, Zip Code, or even individual street addresses. You are given a list of the fields in your data set and drop zones named Height, Category, and Time. Hover over any point on the map to get details such as last sale date and a...

CAGR Dax Measure

CAGR stands for C ompound A nnual G rowth R ate. It describes the rate at which an investment would have grown over several years if it had grown at the same rate every year on a compounding, rather than simple, basis. The CAGR metric is calculated using the following formula: If we were to fit this entire formula into a single measure it may get messy and confusing for other users, so let’s step it out. We will need to create several measures to calculate the individual pieces the CAGR formula. Of course, this is not the only way to calculate CAGR in Power Pivot, but this is the way we’ve decided to go about it. So, let’s break it down, we will need the following measures to calculate CAGR: A measure to retrieve the First Year in the data set A measure to retrieve the Last Year in the data set A measure to calculate the Number of Years between the First and Last Years in the data set A measure to aggregate the total sales in the data set...

Turn Data Sideways

Someone built this lookup table sideways, stretching across C1:N2. I realize that I could use HLOOKUP instead of VLOOKUP, but I prefer to turn the data back to a vertical orientation. Copy C1:N2. Right-click in A4 and choose the Transpose option under the Paste Options. Transpose is the fancy Excel word for “turn the data sideways.” I transpose a lot. But I use Alt+E , S , E , Enter to transpose instead of the right-click. There is a problem, though. Transpose is a one-time snapshot of the data. What if you have formulas in the horizontal data? Is there a way to transpose with a formula? The first way is a bit bizarre. If you are trying to transpose 12 horizontal cells, you need to select 12 vertical cells in a single selection. Start typing a formula such as =TRANSPOSE(C2:N2) in the active cell but do not press Enter. Instead, hold down Ctrl+Shift and then press Enter . This puts a single array formula in the selected cells. T...

Solutions for Data Science

Search This Blog