Skip to main content

Charts - Make your data presentable


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

Three regions appears in A2:A4. Five months appear in B1:F1. Numbers appear in B2:F4. The top-left corner cell A1 is blank. You have A1:F4 selected.
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.

Click the Plus icon to the right of the chart. Hover over the entry for Legend and choose Top as the location.

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.)

The context menu for a chart offers Reset to Match Style, Font, Change Chart Type, Save as Template, Select Data, and Move Chart. Choose Save as Template.

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

Rank Function

How to Use the RANK Function If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order. For example, in the screen shot below, there is a list of 10 student test scores, in cells B2:B11. To find the rank of the the first student's score in cell B2, enter this formula in cell C2: =RANK(B2,$B$2:$B$11) Then, copy the formula from cell C2 down to cell C11, and the scores will be ranked in descending order. RANK Function Arguments There are 3 arguments for the RANK function: number : in the above example, the number to rank is in cell  B2 ref : We want to compare the number to the list of numbers in cells  $B$2:$B$11 . Use an absolute reference ($B$2:$B11), instead of a relative reference (B2:B11)so the referenced range will stay the same when you copy the formula down to the cells below order : (optional) This argument tells Excel whether to rank the list in ascending or descending o...
Post a Comment
Read more

Send Bulk Email from Excel for Outlook

  Download the File from below Link https://drive.google.com/file/d/1tcb4lzNFgEfDKsvQqCW05sgoiGFEhqcK/view?usp=sharing Instructions are given in the image below. Save the File as Excel Macro - Enabled workbook (.xlsm) Use this file to send bulk emails at a time i personally have sent more than 2000 bulk emails at a time. Error may occur if email id typed contains space etc. Only one email id one cell.
Post a Comment
Read more

Pivot table for Each item in Report Filter - Hidden Feature

The pivot table below shows products across the top and customers down the side. The pivot table is sorted so the largest customers are at the top. The Sales Rep field is in the report filter. If you open the Rep dropdown, you can filter the data to any one sales rep. This is a great way to create a report for each sales rep. Each report summarizes the revenue from a particular salesperson‘s customers, with the biggest customers at the top. And you get to see the split between the various products. The Excel team has hidden a feature called Show Report Filter Pages. Select any pivot table that has a field in the report filter. Go to the Analyze tab (or the Options tab in Excel 2007/2010). On the far left side is the large Options button. Next to the large Options button is a tiny dropdown arrow. Click this dropdown and choose Show Report Filter Pages.... Excel asks which field you want to use. Select the one you want (in this case the only one available) and click OK. Over the next few...
Post a Comment
Read more