Create Pareto Chart in Excel!!!

Watch the video - How to create Pareto in Excel!

Before we start first we should understand what Pareto Chart is.

Pareto Chart is based on the Pareto principle (also known as the 80/20 rule), which is a well-known concept in project management.

According to this principle, 80% of the problems are due to 20% of the issues.
If we control these 20% of the issues, our 80% of the problems will be reduced/removed. .

How To Create Dynamic Charts/Graphs!!!

Watch the Video - How to create Dynamic chart in Excel.

 

Let say we have the data table as above and we have created a chart below:

Create Random Data List based on Existing Data

How to create a Random data list based on an existing Data list. Let say we have a Name list in Range (A1:A5).

 

Get Unique Values from Data Range - Part 2 (Using Function)

In our last post Get Unique Values from Data Range - Part 1 (Using default option), we have learn 'Remove Duplicate' & 'Advanced Filter' to get Unique records from a data range. Today we will learn two different function to get Unique Records from a range. We have same example, we have used in Part 1.

1. Using Function (Normal Function)

Step 1: Add a helping column & put this formula and drag it down "=IF(COUNTIF($B$2:B2,B2)=1,A1+1,A1)". See below snap-shot:

Get Unique Values from Data Range - Part 1 (Using default option)

In this post, we will learn how to get Unique values from a Data range.  We will cover 2 ways in this post, next 2 ways we will cover in our next post Get Unique Values from Data Range - Part 2 (Using Function). As a bonus point, we will learn how to get Duplicate values as well in next post.

Scenario: We have a name list in Column A, we want to know how many duplicate values are there in the range. See below image:

Range (A1:A30)

How to remove Page Break lines

Today we will learn, how to remove page break lines today!

Scenario: Most of the time we mistakenly pressed wrong key and as a result page shows in page break view. Whether we change it to Normal view, still we see page break lines. Most of the people close the workbook and re-open it to remove page break view. We will learn how to remove these lines without closing the file.

1. Using Options

• Go to Option by Pressing Alt+T+O or Alt+F+T or File > Options
• Click on Advanced
• Scroll down and uncheck 'Show Page Breaks' under "Display Options for this Worksheet:" then Click OK. See below snapshot:

Fill blank cells in Excel with value in above cell

When we setup data in Excel, we generally leave blank if the same value repeating multiple times or we fetch data from other sources, data have some blank cells which should have values in the above cell. See below snapshot:

Raw Data

Now, we have to fill blank with the values in above cell e.g., Anu Gupta should be in Range(A2:A7), Delhi should be in Range(B2:B4) and so on.

For doing this, first of all we have to select all blank cells. We will do this step-by-step.

Step1. Select the range where you want to fill the data

Select the Range

Step2. Press F5 or Ctrl+G or Click on Home > Find & Select > Go to Special

Go to Special