FunctionsConditional Formatting for visual data analysisLearn about data visualization & charting

Formula writing using Excel Function

Lets understand how to write a complete formula in Excel. Let’s start with one of the basic formula of adding numbers in Excel by using simple Sum function. Formula writing is a simple 3 step process as explained below-

  1. Selecting Cell and function to be used in Formula

As explained above, whenever we type an equal to (=) sign Excel understands this as a formula.

Excel Chart Basics and adding Axis Titles to excel chart

Following is an excel chart where we have marked all the components:

Data Series: The most important of all the elements, this represents the base data on the chart. We see 2 data series in the above graph: Toys (represented by data points 321, 305 & 345) and Office Supplies (165, 195 and 258).

Data Labels: This is the text for corresponding data points is shown on the graph

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.


Using office clipboard to copy paste multiple items

A lot of us live by the Copy-> Paste (or ctrl c/v) routine in our daily life (by far the greatest time saver invention of the centuryJ)

Ever thought of a requirement where excel could keep some of your previous copy items in history (& not just the immediate previous one)?  If the answer is yes, then congrats! this tip will cheer you up for sure.

We explain this through an example.

Highlight & Delete Duplicate Rows

If you are working with large data size with numerous data fields, there could be duplicate records in data which needs to highlighted and corrected to make data more meaningful and usable.

In example below (See picture 1), we have ROW number 2 and 7 which are duplicate and needs to be highlighted.

This can done using 2 simple formulas - CONCATENATE and COUNTIF

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)

Display & Hide Formulas

At times, we want to verify formula correctness by looking at the formulas. While we can look at the formula of a specific by clicking on it, Excel provides the functionality to make all formulas visible in the worksheet-


Keyboard shortcut for Hiding & Unhiding Formulas in Excel worksheets -


1. To show formula/s in Excel worksheets, Press CTRL + ~


 

Pages