347 647 9001+1 714 797 8196Request a Call
Call Me

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

Selecting the right data for pie chart

Formatting the data for pie chart analysis

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 the Author

Shashi is a B.Tech from Indian Institute of Technology, Delhi and an MBA from Indian Institute of Management (IIM), Ahmedabad. With over 11 years of experience, he has dabbled in Project Management, Management Consulting, Investment Banking, Mergers & Acquisitions, Corporate Finance, Strategy Formulation and Fund Raising.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 28861