course bg
EduPristine>Blog>Lesser Known but Useful Facts about Some Excel Functions & Charts

Lesser Known but Useful Facts about Some Excel Functions & Charts

June 10, 2013

We all are experimental by nature. And at times, I feel its good that we don’t have optional or alternative ways of doing few things. Options and alternatives available with us, at times force us to cross check. And surprisingly cross checking throws light on some of the facts we would have otherwise ignored.

This article explores lesser known but useful facts about some of the excel functions and charts.

Example 1: Usage of ROUNDUP Function

Many a times, in financial modeling, you will come across raw data that needs to be grouped into various bins. Lets say credit scores of individuals are given to you as below:

 

How to use roundup function in excel

How are you going to do this excel model? Has your mind already started working?

IF THEN function?

Think about how many nests you will have to build?

AND function inside IF function? It’s definitely an improvement over the nested IF function but still the effort required is enormous.

Have you come across a function called ROUNDUP? Have you ever noticed how powerful this function is?

Excel definition of ROUNDUP – Rounds a number up, away from 0 (zero).

Syntax is: ROUNDUP(number,num_digits)

Syntax of roundup Function in excel

Example 2: Bar Chart in 3D

Please examine the bar chart below. Can you guess the values corresponding to variables A, B, C, D & E from the chart below?

 

3D Bar Chart in excel

Why 2D charts are better than 3D charts

Example 3: Plotting the data on 2D charts

Suppose a data in following format is given to you. You are required to make a simple bar chart plotting years on x-axis and Sales on y-axis.

If you blindly select the data and plot the 2D bar chart in excel, this is what you will get:

Exhibit 3: An Attempt to Plot Data on x-axis directly

An Attempt to Plot Data on x-axis directly

Surprisingly excel has displayed the years also by way of a bar. You can now do jugglery to rectify the graph. But whats the problem with excel?

E can fix this easily in a following manner

Make the column you want to be read as x-axis heading less. Excel will automatically make it x-axis in the chart.

 

How to correctly create a 2D chart

Example 4: Plotting the correct pie chart

Suppose you have to plot the operating expenses as a pie chart as shown in the example below. If you simply select the data and do a pie chart, this is what you will get:

Exhibit 4: An Ordinary Pie Chart

Wrong way to create a pie chart

Slices corresponding to Small expenses like Rent, Electricity, Stationery, Telephone & Internet and Housekeeping are so thin that they are hard to distinguish.

When we encounter such a situation, we should try to enhance the quality of our graph by grouping the smaller expenses in head of Others and then splitting them into another pie chart. A pie of pie chart in excel does exactly this. If we choose that graph, see the output below. Once you obtain the pie of pie chart, right click on it and say format data series. There you will see an option to choose as many values from the bottom to group into Others. Excel groups the values from bottom by default. Hence, if you intend to use this function, you need to arrange lower value heads towards the bottom in your excel table.

Exhibit 5: A Pie of Pie Chart

Way Forward:

This series gives you a flavor of useful tips of some of excels functions and charts which are extensively used in financial modeling. To learn more about financial modeling consider joining our Financial Modeling course.

Templates to Download:

I have created a template for you, where the subheadings are given and you have to link the model to get right numbers or charts! You can download the same from here. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

For any queries regarding financial modeling, feel free to put the comments in the blog.

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.

Comments

Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 28861