Making and understanding your first excel chart

Making a basic chart in excel is super easy. Suppose we have the following data of a retail store selling the following two product lines and their month-wise sales value in dollar terms.

We explain the simplest way of inserting the chart here, you will get more and more confident with charting as we move forward and may discover your own style of working with charts.

Introduction to charts & their usefulness

Excel is meant to handle a lot of data and it is extremely good at it. But decision makers need information (that come from such data) and this usually mean summarizing the data in visual forms (such as charts/graphs) so that the basic underlying story behind all this data stands out. The good thing is that Excel is reasonably good at visually presenting the data in the form of various charts (graphs).

Let’s understand this through an example:

Here is a 12 months data on 4 product groups that a company sells. If you see the table alone, you’ll find that it is difficult to make sense of this in a quick time.

lot of data

Some of the questions that are very basic but not straightforward to answer are: Which product group is showing most resilient growth? Which one is suffering? Which one could be the next big thing? Any month where all products suffered?

Answering this through the data table is not so easy.

These are the situations where visualization are very useful.

Excel Operators in Formula

Formula can very simple or very complex depending on combination of operators, cell references and/or static values used. Formula result depends on how different operators are evaluated.

Default precedence of operators in Excel are given in table below-

For example we want to calculate Tax on Net Income, essentially expenses to be reduced from Income  and then Tax rate is to be applied to net value.

Relative and Absolute Referencing in Excel

Relative Referencing

Suppose we are writing a formula in cell D3 to total numbers of Bananas and Oranges as shown in picture below.

We would start by putting (=) sign and then using cell references we will end up by putting formula as B3+C3. Now if same formula is to be performed in cell D4 and cells below it.

Color Scales

Excel provides extremely powerful & immensely useful data analysis tools under the Conditional Formatting tools. One of the best thing about these tools is that they are easy to comprehend and are at best 2-click away from being applied to your data.

We have already seen Data-Bars in a previous tutorial.

Color Scales reveal the relative position of each cell in a range (Colors are used to convey if the number is big or small with respect to all the numbers in the selected range)

Data Bars

Data bars provide a quick way of visualizing the data at hand. In essence it provides an in-cell bar graph to represent the data of that cell. Length of the bar depends on the number contained in the cell and is relative to other cell values for which this visualization is selected.

We take you through a quick overview of this functionality through an example.

Fix an area(Freeze panes)

We learnt about the Excel’s functionality of freezing top row or left most columns in the previous post. Another usual requirement will be to freeze a set of rows & columns on the sheet while viewing the sheet. To explain this better, please open the attached excel (View_freeze panes.xlsx). In this, suppose we want to keep the left most Item descriptors (Column A) as well as the months visible all the time while we navigate in the sheet (down or to the right side).

Writing Formula in Microsoft Excel

Formula in Microsoft Excel begins by typing an equalto(=) into any empty cell. Exce understands equal(=) sign and assumes that you are entering a formula.

Excel also allows you to begin your formula with a equalto sign(=) or a plus sign(+) or @ symbol. Once you complete your formula and press Enter, Excel inserts a leading equalto sign in front of formula.



Fix top row (freeze) or the first column

Freeze first row:

It is common to have a list of data rows in excel, with the first row containing headers (meaning of the columns). It is quite possible that there are lots of records in this list, so that when you scroll down, the headers go out of sight.

Understanding the View tab

Views Tab holds access to certain regularly used routines & shortcuts. The most common of which has to be freeze panes. Following is a brief description of all the major command groups:

Views Ribbon menu