10 Super Tricks of MS Excel Charts

Before we begin, let's explore the significance of charts and graphs in Excel. They play a crucial role in visualizing data trends, patterns, and comparisons, facilitating the interpretation of complex information.

Now, let's delve into the customization of charts in MS Excel, where we have the flexibility to design various types of personalized charts. Through the application of different formats, we can create customized charts and graphs, ultimately contributing to the development of a dynamic dashboard.

In this article, we will take a deep dive into the realm of charts and graphs within Microsoft Excel. If you're looking to enhance your Excel skills specifically in the domain of Charts and Graphs, this read is tailored for you.

I will share 10 super tricks that can prove highly beneficial in creating a dynamic dashboard.

1.???? Number Formatting in Data Labels:

If your displayed numbers appear unwieldy in the data label box, you can refine their presentation by formatting them to show in thousands, millions, and so forth.

Begin by adding a data label to the chart through a right-click. Once the label appears, right-click on the data label box and choose "Format Data Labels." A formatting pane will emerge on the right side. Within the Label Options' number section, opt for "Custom" from the "Category" drop-down list. Enter the code "0,,.00 'm'," where 'O' represents your actual number, double commas convert the number into millions, and two zeros after the dot specify displaying two decimal values. Click "Add," and you will observe the updated result.

??Take a closer look at the above pictures and notice how the codes have been written.

?2.???? Adding New Data in Charts

My question is “How to Add New Data to Charts”? The answer is simple- just enhance the cell range, it will automatically add up.

Are you aware of an alternative method for incorporating new data values into charts? It's a straightforward process—just copy the ranges of the new data using Ctrl+C, then select the chart and apply the command by pressing Ctrl+V to paste the data. No need for complicated steps!

?The Cost of Goods Sold column is the new one and data of which has been added to charts by following the former step.

3.???? 3D Charts and Floor:

If you are working with 3D charts, Excel sometimes uses the term "floor" to describe the bottom surface of a 3D chart. In this case, you can format the floor of the chart by right-clicking on it and selecting "Format Floor."

Click on the 3D chart to select it. Right-click on the chart area, and a context menu will appear. From the menu, select "Format Chart Area." In the Format Chart Area pane that appears on the right side of the Excel window, you should find various formatting options. Look for a section related to the chart's 3D options. There might be an option specifically for the floor.

Within the 3D formatting options, you may find settings like "Floor Options" or similar. Here, you can adjust properties such as color, texture, transparency, and other aspects of the floor. After making the desired changes to the floor properties, click "Close" or select another part of the chart to apply the formatting.

Keep in mind that the exact steps and options can vary slightly depending on the version of Excel you are using.

4.???? Showing Target in Excel Charts

Display your desired quantity through Excel Charts, comparing targets to actual achievements. Choose the table, navigate to the Charts group in the Insert Tab, and opt for the Combo chart, combining Line and Bar elements.

After selecting the line, right-click and choose Format Data Series. Within the Fill option, opt for No Line. Access the Marker settings, choosing the "—" from the "built-in" option, and enhance the Marker Size.

Experience dynamic updates as the target dynamically adjusts. Observe the movement of the "-" marker by modifying your data.

?5.???? Different Colour of Target vs Achievement

Demonstrate dynamic differentiation between target and achievement bars in charts using distinct colours. Begin by employing the formula =IF(C5>E5, C5, NA()), adjusting cell references as per your data table. This formula selectively displays achievement values, marking non-achievement amounts as N/A.

Select the table and opt for the 2D clustered column chart. Assign a red colour to the Sales Amount Column and green to the Achievement Column. Right-click on the green bar, choose Format Option, and set the Bar as Secondary. This adjustment ensures automatic alignment of the bar.

Observe the dynamic nature as Achievement Bars are highlighted in green, while Non-achievement Bars are marked in red. The colour automatically adjusts based on changing figures.

Please note that if you find difficulties in learning by reading this article you can watch the video on YouTube by clicking on the following links.

https://youtu.be/QgejvX5k53w?si=4ESGlls0L4EOHuGU

Thank You and Enjoy Learning

?