How to Use Excel Chart - Step by Step

"A picture is worth a thousand words" – is also true in Data Analysis. Today we will discuss about pictures which expresses the data, the Excel Charts or Graphs.

Why draw Chart or Graph ?

The goal of data preparation using functions or excel formula is to analyze that data and to get productive and growth oriented management decisions.

If you are linked with data analysis (even you make single report based on some data) then you will understand the importance of Charts in Excel after this example. Let suppose there are 365 rows in a column as sales value for the entire financial year day after day and you need to find out the deviations throughout the financial year. I mean to say 'On which days the Sales volume was low ?' or 'On which days the Sales volume was High or at peak ?'   Obviously, we can use Excel’s inbuilt functions or complex Excel formulas but a chart can solve this in few minutes and we can avoid complex excel formulas.

Besides in Power Point Presentations, Charts are very important as the actual status or performance will better be understood using charts.

Today I will discuss about Charts in excel, the basic and then how to use it. If you know how to create or use charts you can skip this article but reading a topic again might give you some new idea.

There are few types of charts,  

The available Charts in Microsoft Excel are as below.

1.       Column or Bar Chart

2.       Line or Area Chart

3.       Pie or Doughnut Chart

4.       Hierarchy Chart

5.       Statistic Chart

6.       Scatter or Bubble Chart

7.       Waterfall or Stock Chart

8.       Combo Chart

9.       Surface or Rader Chart

And finally

10.   PivotChart.

In this article, we will discuss about those charts which we use very frequently and later I will discuss about the rest charts.

Let us first Know the names of the Charts to identify the charts by its' name, please follow the below images, for the shake of an example I have taken few City names in United States like New York, Los Angeles, Chicago, Houston, Philadelphia, Phoenix, San Diego and fictitious Potentiality and Sales.

Pie or Doughnut Chart : 

A pie chart (or a circle chart) is a circular statistical graphic which is divided into slices to illustrate numerical proportion. In a pie chart, the arc length of each slice (and consequently its central angle and area), is proportional to the quantity it represents.

Pie or Doughnut Chart

Column Chart or Bar Chart : Column charts are used to compare values across categories by using vertical bars.

Column Chart

Column Chart

Line chart : A line chart is a graph that shows a series of data points connected by straight lines.

Line Chart

Area Chart : An area chart is a line chart with the areas below the lines filled with colors. Use a stacked area chart to display the contribution of each value to a total over time.

Area Chart

Combo Chart : A Combo or Combination chart is a chart that combines two or more chart types in a single char e.g., Column or Bar Chart with a Line Chart.

Combo Chart

How to insert a Chart?

v  To insert a Chart, you need to move the menu INSERT, you will find Charts Group as shown in the picture.

Chart Menu Option in Ribbon

v  Select the entire data or you can select specific columns to get a chart, we will discuss later.

Potentiality & Sales Table

v  Now choose any chart (to understand the basic chart functionality, later we will use the most appropriate chart).

v  For example, I choose the Column Chart and the first Chart, as shown below which is a very basic chart, we need to ‘decorate’ it.

Default Excel Chart

v  This chart is very basic without a Chart Title (always give Title to your chart), to give a title to your chart just click on the Letter CHART TITLE, a text box will appear

v  You can now remove the default title and put your own title

Quick Format Buttons

v  If you click on the chart, you will find three buttons at the right side of the chart, these are the quick chart formatting tools, you can even change the Chart Type.

v  But let us concentrate on the New Helper window at the right side named as Format Chart Area. In this area, you can format your chart, for example, you can give border, border color, border type and you can change the corner to rounded corner, you can fill the space in the charts etc.

Format Shape of a Chart

Format Shape of a Chart

v  In the above image, there are three options, Fill & Line, Effects & Size & Properties. Using these tools, you can easily modify your chart.

v  If you right click your mouse selecting the chart, a drop-down menu opens as shown in the below image, from here you can also modify your charts, the options are self-explanatory.

Right Click Menu

v  Two additional new menu opens in the ribbon, if any chart is selected, as shown in the below image, one is Design menu and the other is Format menu. You can choose any design and format Charts very quickly.

These are the basic of Chart or Graphs and their formatting, in the next article I will show you some advanced use of Charts in Excel as well as in PowerPoint Presentations.

I hope you have enjoyed this article.
Thank you for reading… Take care…

How to Split Cells Using Excel Formula – Step by Step

Hi friends,

Splitting cell content was a challenge but using Text to Columns and using Flash Fill we can now split the cell content in the next columns.

It was easy, isn’t it?

But sometimes it is the situation’s call that we must use formula to split the cell content (or better to say to extract specific data) and spread it over the next columns.

Honestly speaking, if you want to master Microsoft Excel, you must learn to solve the problems using formulas.

In this tutorial or article we will discuss how to split cell content in the next columns.

How to separate text in excel using formula?

or

How to separate first name, last name and date of birth in excel?

Suppose we got a table containing few names and their birthdays like ‘Jacob Elordi - June 26, 1997’ and in next row ‘Joey king - July 30, 1999’ and so on and we need to get the first name, last name and their date of births in the next columns.

Note : Separators can be commas, dots, hashes etc, in place of spaces but the excel formula will be the same, just replace space by that character. 

How to do it?

Let us solve this problem Step by Step.

1.     If we analyze the first row containing “Jacob Elordi - June 26, 1997”, we can understand that there are two separators, spaces and hyphen

2.     If we can identify the position (number) of the spaces and the hyphen, then we can get the desired texts easily using some TEXT functions.

What functions to use to split the cell text and spread over the next columns?

3.     We will use functions, SEARCH function and LEFT function, RIGHT function and MID function

Here SEARCH function will give us the position of the space and hyphen.

How to get First Name using formula in Excel?

Steps :

1.     What is the output of SEARCH function? It gives us the position of a specific character in a text or string, like =SEARCH(" ",C3,1); output is ‘6’ for the first row. (Here we are searching for space.)

2.     Now we will use LEFT function and we need only one number, the position of first “space”, but =SEARCH(" ",C3,1) formula gives us the position of the first occurrence of space, we need to less one to get the number of characters in first name, therefore, our excel formula becomes =LEFT(C3,SEARCH(" ",C3,1)-1) the output is ‘Jacob’, the first name in the First Name column.

We did the first task !

Split Cell Content using Excel Formula - Functions LEFT & SEARCH

How to get Last Name using formula in Excel?

This is a little complex, but think logically and enter the formula step by step, you will get the Last name.

Steps :

1.     We got the position of first space as 6 in =SEARCH(" ",C3,1) formula. The next task is to find out the second occurrence of space. How to get the next occurrence of space?

2.     Use nested SEARCH function as

=SEARCH(" ",C3, SEARCH(" ",C3,1)).

Explanation :

The first SEARCH function is searching for space but the starting number in this function should be 6 (the position of first space), this number is provided by the next SEARCH function colored in RED and we used it in our previous task. If we less 1 from this output, we will get the number of characters in the last name i.e., SEARCH(" ",C3,SEARCH(" ",C3,1)-1) ; output is 6.

3.     Now we know the starting position and the number of character in the last name part. We can use MID function to extract the last name because MID function requires text, start number and number of characters and our final formula using MID function is,

=MID(C3,SEARCH(" ",C3,1)+1,SEARCH(" ",C3,SEARCH(" ",C3,1)-1))

In this formula, ‘+1’ is used to get the position of the first character in the last name after first space and ‘-1’ is used to get the number of characters’ present in the last name before second space.

Split Cell Content using Excel Formula - Functions MID & SEARCH

How to get Date of Birth using formula in Excel?

This is easy, very similar to the method we use to get the first name, the difference is that we will use RIGHT function, as it is logical and easy to get the data of birth from the right side rather than traversing from left side.

Steps :

1.     We will search for hyphen in the given text using = SEARCH("-",C3,1), which gives us the position of hyphen from the left side, therefore, we will less one (as ‘-1’) to get the number of characters in the date of birth.

2.     Our excel formula to get the date of birth from the text is =RIGHT(C3,SEARCH("-",C3,1)-1)

Split Cell Content using Excel Formula - Functions RIGHT & SEARCH

I hope you have enjoyed this article.

Thank you for reading… Take care…

Flash Fill - Split Cells in Excel – Step by Step

Hello friends,

Today I will discuss about Flash Fill in Excel, a wonderful tool introduced in Excel 2013 and 2016 to separate the data in a cell.

In my last article, I had discussed about Text to Columns, generally we use to split cells and spread the data over the next columns. In this method, I hope you have noticed that when applied text to a certain column, the column also split and the original column disappeared. But sometimes we need to keep the original column.

How Flash Fill works?

Flash Fill analyzes the data you are entering and it tries to figure out what result you are trying to achieve. I told you, Microsoft Excel is getting wiser!!!

How to Use Flash Fill in Excel?

Let us explain this tool with an example, suppose as shown in the below image we have few names with their birthdays and we need to separate it by first name, last name and birthdays. You can split these by months or days or by years but for simplicity we will break it by first name, last name and birthdays.

We need the table as under from the second table.

Table - 1

How to Use Flash Fill in Excel

Table - 2

Use Flash Fill to Split Excel Cell

In the first row, I have “Jacob Elordi - June 26, 1997”, (a movie actor,) I desire to get the text to be split by ‘Jacob’ then in the next column ‘Elordi’ then ‘June 26, 1997’.

Let’s use Flash Fill Step by Step :

     1. First of type the header of the table as First Name, Last Name & Date of Birth.

     2. Type the first name in the column named First Name e.g., Jacob

     3. Press Enter, then select the above cell again where you typed Jacob

     4. Go to Data then in the Data Tools group click Flash Fill.

Flash Fill Option in Excel

     5. Now you will see all the column is filled with the first names

     6. Again, type the last name in the next column labeled as Last Name and repeat the steps.

     7. Apply the same procedure to the Date of Birth column.

The final table structure will be as under. Isn’t it amazing tool provided by Microsoft Excel!!!

After Using Flash Fill in Excel

The Shortcut for this tool is Ctrl+E.

Note : Flash Fill is turned off by default, you need to activate it from File, Options then Click on the Advanced and select Automatically Flash Fill.

Flash Fill Option in Excel

I hope you have enjoyed this article regarding Flash Fill.

Thank you for reading…

Split Data

Hello friends,

In any organization, the data is stored in Databases, like Oracle, SQL, SQLite, MySQL, Sybase etc., and we collect or download these data from e-commerce software or ERPs like SAP, CRM, ERP5, ERP Next etc., or directly from the database, sometimes you might have noticed that these files are with “ *.CSV ” extension or other extensions which gives us all data in a single row in one Cell! 

But to analyze these data we need to separate these data from one Excel cell to more than one cell in the next columns.
We can split these data using Excel’s Text to Column option. Today we will discuss about Text to column in Excel.

Let us have a look at the below image, which is combination of numbers, texts, special characters and dates.

Excel Split Cells by Text to Column Option

To analyze such complex data, we first need to spread it over the next columns by the data type, like numbers in a column then texts in the next column and so on.

How to split one column into multiple columns in excel?

Let me show you step by step and with related images.

Steps :-

1.     Select the cells row wise

2.     Click on the Data tab

3.     In the Data Tools group, click on the Button ‘Text to Columns’

Text to Columns Option in the Ribbon in Excel

4.     A new window will appear, ‘Convert Text to Columns Wizard – Step 1 of 3’, as shown in the below image

Excel Split Cells by Text to Column Option

5.     Here we get two Options, Delimited & Fixed Width.

a.     Delimited : If we select this option Excel will offer us to split the cell by ‘specified’ criteria, like comma, semicolon, space, tab etc., in the next window. In  this window if you have some special delimiter (e.g., @, !, #, $, * etc.,) besides these in built option (Tab, Semicolon, Comma & Space) you can use the Other option and put your special delimiter to split the cell by this delimiter.

b.     Fixed Width : If we select this option, Excel will allow us to split the cell by fixed width, i.e., character count will be fixed for all cells in that column.

6.     Select your appropriate option and click Next.

Excel Split Cells by Text to Column Option

7.     If you are not using the Fixed Width method, just click Next and  move the upward pointing arrow from left to right as per requirement and click Finish.

Excel Split Cells by Text to Column Option

Excel Split Cells by Text to Column Option
8.     And you are done.

Excel Split Cells by Text to Column Option

Delimited or Fixed Width – Which one to use?

Remember Fixed Width will split cell contents by fixed character count, Let suppose we have got such a data where few digits (uniform number of digits) are at the beginning and then few texts are at the end of the data and we need to separate the numbers and the texts from this data. Here we will use Fixed Width option.

In the below picture, we have a fixed text in the C column as 
“Today is :”, suppose we need to separate or split these two data types One column containing the text part and the next will contain the Data portion.

Excel Split Cells by Text to Column Option

Here we will use Fixed Width option and the final data will be as shown in the below image.

Excel Split Cells by Text to Column Option

On the other hand, if a data is separated by Tab, Semicolon, Comma or Space we will use Delimited option.

I hope you have enjoyed this article, in my next article I will show you few techniques to split the cell using formula and other options available in the Excel.

Thank you for reading…