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

40 Power Query Editor features in Power BI

40 Power Query Editor features in Power BI along with examples: 1. Filter Rows: Remove rows based on conditions. Example: Remove rows with a null value in the "CustomerName" column. 2. Remove Duplicates: Eliminate duplicate rows. Example: Remove duplicate entries based on the "OrderID" column. 3. Sort Rows: Arrange rows in ascending or descending order. Example: Sort data by "Date" column in descending order. 4. Replace Values: Substitute one value with another. Example: Replace "N/A" with "Unknown" in the "Status" column. 5. Split Columns: Divide a column into multiple columns. Example: Split "FullName" into "FirstName" and "LastName." 6. Merge Queries: Combine data from multiple sources. Example: Merge customer and order data based on the "CustomerID." 7. Group By: Aggregate data based on a specific column. Example: Group sales data by "ProductCategory" and calculate the sum ...
Post a Comment
Read more

20 Time Intelligence Dax Measures

20 Time Intelligence DAX measures in Power BI with examples: Year-to-Date Sales: css Copy code YTD Sales = TOTALYTD( [Total Sales] , Calendar [Date] ) Month-to-Date Sales: css Copy code MTD Sales = TOTALMTD( [Total Sales] , Calendar [Date] ) Quarter-to-Date Sales: css Copy code QTD Sales = TOTALQTD( [Total Sales] , Calendar [Date] ) Previous Year Sales: mathematica Copy code Previous Year Sales = CALCULATE ( [ Total Sales ] , SAMEPERIODLASTYEAR ( Calendar [ Date ] ) ) Year-over-Year Growth: css Copy code YoY Growth = DIVIDE( [Total Sales] - [Previous Year Sales] , [Previous Year Sales] ) Rolling 3-Month Average Sales: sql Copy code 3 M Rolling Avg Sales = AVERAGEX(DATESINPERIOD(Calendar[ Date ], MAX (Calendar[ Date ]), -3 , MONTH ), [Total Sales]) Cumulative Sales: scss Copy code Cumulative Sales = SUMX (FILTER(ALL(Calendar), Calendar [Date] <= MAX (Calendar[Date])), [Total Sales] ) Running Total Sales: scss Copy code Running Total Sales = SUMX (FILTER(ALL(Calendar), Calen...
Post a Comment
Read more

Protect rows from getting deleted

Toggle navigation   MrExcel Home Here is an odd use for an array formula: Say that you don‘t want anyone to delete or insert any rows in one section of a worksheet. Scroll far to the right, off the screen, and build an array in those rows. Select Z1:Z9. Type  =2  and press  Ctrl+Shift+Enter . You can use any number,  =0 ,  =1 ,  =2 , and so on. If someone tries to delete a row, Excel prevents it and shows a cryptic message about arrays, shown below.
Post a Comment
Read more