Training By Himanshu

Tag: Excel Tutorials

  • Pareto Analysis in Excel – Step-by-Step Guide with Charting & Business Examples

    Pareto Analysis & Charting in Excel – Detailed Guide

    Pareto Analysis is a decision-making technique used for identifying the most significant factors in a dataset. It is based on the Pareto Principle (80/20 rule), which states that:

    “80% of consequences come from 20% of the causes.”

    In business, it helps prioritize efforts on the most impactful issues.

    🔍 Step-by-Step: Pareto Analysis in Excel

    Let’s go through the complete process with an example.

    🧾 Example Scenario:

    Problem: You’re a Quality Manager analyzing 100 customer complaints. You want to identify the top issues to prioritize.

    Sample Data:

    Complaint TypeFrequency
    Late Delivery35
    Damaged Product20
    Incorrect Item15
    Poor Customer Support12
    Difficult Website10
    Others8

    📊 Step 1: Prepare the Data

    Start with your data like above – two columns:

    • Categories (causes)
    • Values (frequency or cost)

    📈 Step 2: Sort Data in Descending Order

    Sort the complaint types by frequency from highest to lowest:

    Data → Sort → Sort by Frequency → Largest to Smallest

    🧮 Step 3: Add Cumulative Percentage

    Add three more columns:

    • Cumulative Frequency
    • Cumulative %
    • Percentage of Total
    Complaint TypeFrequencyCumulative Frequency% of TotalCumulative %
    Late Delivery353535%35%
    Damaged Product205520%55%
    Incorrect Item157015%70%
    Poor Customer Support128212%82%
    Difficult Website109210%92%
    Others81008%100%

    Excel formulas:

    • Total Complaints: =SUM(B2:B7)
    • % of Total (C2): =B2/$B$8
    • Cumulative Frequency (D2): =B2; (D3): =D2+B3
    • Cumulative % (E2): =D2/$B$8

    Use Number Format → Percentage and show 0 decimals for clarity.

    📉 Step 4: Create the Pareto Chart

    Option 1: Built-in Pareto Chart (Excel 2016 and later)

    1. Select the original two columns (Complaint Type and Frequency).
    2. Go to: Insert → Charts → Histogram → Pareto

    Excel will automatically:

    • Sort data
    • Calculate cumulative %
    • Overlay line graph on bar chart

    Option 2: Manual Combo Chart (for all Excel versions)

    1. Select:
      • Categories
      • Frequency
      • Cumulative %
    2. Go to: Insert → Chart → Combo Chart → Custom Combo
    3. Set:
      • Frequency → Clustered Column
      • Cumulative % → Line Chart
      • Check Secondary Axis for Cumulative %

    🎯 Step 5: Interpret the Chart

    • Bars show the frequency of each cause.
    • Line shows cumulative %.
    • Identify where the line crosses 80% → those are your top contributing issues (usually 2–3 categories).

    ✅ Use Cases in Business

    AreaPareto Use Case Example
    Quality ControlIdentify top causes of product defects
    Customer ServiceAnalyze top reasons for complaints
    Inventory ManagementFocus on top items causing stock-outs
    Sales & RevenueTop customers/products contributing to revenue
    IT / HelpdeskMost frequent support ticket categories

    📌 Tips

    • Use data labels for better readability.
    • Apply conditional formatting to highlight top 20% causes.
    • Use slicers/filters if working with dynamic dashboards.

    🔖 Summary

    StepAction
    1Collect and structure your data
    2Sort in descending order
    3Add cumulative and percentage columns
    4Create Pareto chart (built-in or manual)
    5Analyze and act on the top issues

    Top rated products

  • How to Use SORT and SORTBY Functions in Excel 365

    The SORT and SORTBY functions in Excel 365 are part of the dynamic array family — they allow you to sort data easily, flexibly, and without altering the original range.

    🧠 1. SORT Function in Excel

    🔧 Syntax:

    SORT(array, [sort_index], [sort_order], [by_col])
    ArgumentDescription
    arrayThe range or array to sort
    sort_indexColumn or row number to sort by (default is 1)
    sort_order1 = Ascending, -1 = Descending
    by_colTRUE = sort by columns, FALSE = by rows (default)

    ✅ Real-Life Example: Sorting Employee Salaries

    You have the following data in A2:B6:

    NameSalary
    Ravi35000
    Priya42000
    Neha39000
    Akash30000
    Anjali45000

    Formula to sort by Salary (ascending):

    =SORT(A2:B6, 2, 1)

    ✅ Output:

    NameSalary
    Akash30000
    Ravi35000
    Neha39000
    Priya42000
    Anjali45000

    Formula to sort by Salary (descending):

    =SORT(A2:B6, 2, -1)

    🧠 2. SORTBY Function in Excel

    The SORTBY function is more flexible — it allows you to sort one array based on another.

    🔧 Syntax:

    SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
    ArgumentDescription
    arrayThe data to sort
    by_array1The column/array to sort by
    sort_order11 = Ascending, -1 = Descending

    ✅ Real-Life Example: Sort Students by Marks in Another Column

    StudentRoll NoMarks
    Aman10287
    Kirti10192
    Mohan10476
    Preeti10389

    Formula to sort by Marks (descending):

    =SORTBY(A2:C5, C2:C5, -1)

    ✅ Output:

    StudentRoll NoMarks
    Kirti10192
    Preeti10389
    Aman10287
    Mohan10476

    🔄 SORT vs SORTBY: What’s the Difference?

    FeatureSORTSORTBY
    Sort by positionYesNo
    Sort by other dataLimited✅ Full flexibility
    Multiple criteriaLimited✅ Supports multiple arrays
    Practical use casesTables, visible dataSort by hidden/helper columns

    🎯 Real-World Use Cases

    • Sort sales data by amount or date
    • Sort student scores without rearranging original data
    • Sort project tasks by deadline
    • Sort inventory by stock level using helper columns

    🎓 Want to Learn More Excel Sorting & Automation Tricks?

    Explore how to use Excel 365’s new tools like SORT, FILTER, TAKE, UNIQUE, and more in real business applications.

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Smart Excel functions (SORT, FILTER, XLOOKUP)
    • Dashboards & automation
    • Excel VBA and real-life case studies

    🎯 Click Here to Enroll Now

    On sale products

  • Excel 365 TAKE Function Explained: Extract Top or Bottom Rows Easily

    The TAKE function in Excel 365 is one of the powerful Dynamic Array functions introduced to make data extraction easier and cleaner.

    🧠 What is the TAKE Function?

    The TAKE function allows you to extract a specific number of rows or columns from the beginning or end of a range or array.

    It’s extremely useful when you want to:

    • Limit results (like top 5 or last 3 rows)
    • Display recent data
    • Dynamically filter table portions

    🔧 Syntax of TAKE

    =TAKE(array, [rows], [columns])

    📌 Arguments:

    ParameterDescription
    arrayThe range or array to extract data from
    rows(Optional) Number of rows to take. Positive = from top, Negative = from bottom
    columns(Optional) Number of columns to take. Positive = from left, Negative = from right

    ✅ Practical Examples of TAKE in Excel 365

    🔸 Example 1: Get the Top 5 Sales Entries

    If you have a list of sales data in range A2:B20 (Product, Sales):

    =TAKE(A2:B20, 5)

    ✅ Returns the first 5 rows from the dataset (top 5 sales records).

    🔸 Example 2: Get the Last 3 Rows

    To extract the last 3 rows from that same range:

    =TAKE(A2:B20, -3)

    ✅ Returns rows 18 to 20 from the array.

    🔸 Example 3: Take First 2 Columns Only

    If your data range is A1:E10, and you want only the first 2 columns:

    =TAKE(A1:E10,,2)

    ✅ Returns columns A and B with all 10 rows.

    🔸 Example 4: Last 5 Rows, Last 2 Columns

    For a full dynamic slice (e.g., a summary report):

    =TAKE(A2:E100, -5, -2)

    ✅ Takes the last 5 rows and last 2 columns from your dataset.

    🛑 Notes

    • You can omit either the rows or columns argument if not needed.
    • Works only in Excel 365 and Excel for the Web.
    • Output updates dynamically if the source array changes.
    • Compatible with other dynamic functions like SORT, FILTER, UNIQUE.

    🧠 Combine with Other Functions

    =TAKE(SORT(A2:B100, 2, -1), 3)

    ✅ Sorts the data in descending order by Sales (column 2), and returns the top 3 results.

    📌 Real-Life Use Cases

    • Display Top N performers from a team list
    • Show Latest 5 transactions in a bank log
    • Get first 2 columns from a wide dataset (e.g., IDs & names)
    • Build a summary dashboard showing recent trends

    🎓 Want to Master Excel’s Smartest Features?

    Learn to use TAKE, DROP, SORT, FILTER, XLOOKUP, LAMBDA, and more with real-life dashboards and projects.

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ What you’ll learn:

    • Excel 365’s latest tools
    • Powerful automation techniques
    • Real-world problem-solving with formulas
    • BONUS: Excel dashboards, VBA, and charts

    🎯 Click Here to Enroll Now

    On sale products

  • UNIQUE Function in Excel 365 – Explained with Examples

    The UNIQUE function in Excel 365 is a game-changing tool that helps you extract distinct or unique values from a list or range — instantly and dynamically.

    It’s part of Excel’s new dynamic array functions, introduced in Excel 365 and Excel for the web.

    📘 What is the UNIQUE Function?

    The UNIQUE function returns a list of unique values from a range, array, or table column. It removes duplicates automatically and updates dynamically if the source data changes.

    🧪 Syntax

    UNIQUE(array, [by_col], [exactly_once])
    ArgumentDescription
    arrayThe range or array to extract unique values from
    [by_col]Optional. Use TRUE for column-wise, FALSE (default) for row-wise
    [exactly_once]Optional. If TRUE, returns values that appear only once

    ✅ Example 1: Basic Unique List

    List of names in A2:A10:

    Ravi  
Priya  
Ravi  
Neha  
Priya  
Amit

    In another cell:

    =UNIQUE(A2:A10)

    ✅ Output:

    Ravi  
Priya  
Neha  
Amit

    ✅ Example 2: Unique Values That Appear Only Once

    Same list, but only those that appear exactly once:

    =UNIQUE(A2:A10,,TRUE)

    ✅ Output:

    Neha  
Amit

    ✅ Example 3: Unique Rows from a Table

    If you have multiple columns like Name and Department:

    NameDept
    RaviSales
    NehaHR
    RaviSales
    AmitFinance

    Use:

    =UNIQUE(A2:B5)

    ✅ Output:

    Ravi  Sales  
Neha  HR  
Amit  Finance

    🔄 Dynamic Behavior

    When new values are added to the source range, the UNIQUE function automatically updates its results — no manual refresh needed.

    🧠 Real-Life Use Cases

    • Remove duplicate customer names or email addresses
    • Count unique product types in sales data
    • Build dynamic dropdown lists with Data Validation
    • Filter one-time entries from logs or records

    🎓 Learn More Excel Magic

    Functions like UNIQUE, FILTER, SORT, XLOOKUP, and LAMBDA can transform your workflow.

    📘 For step-by-step guidance, real-world dashboards, and hands-on Excel automation:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Covers:

    • Dynamic Array Functions
    • Excel 365 Exclusive Features
    • Dashboard Projects
    • Form Controls, VBA & More

    🎯 Click Here to Enroll Now

    On sale products

  • How to Use the LET Function in Excel 365 (Step-by-Step with Examples)

    The LET function is one of the powerful new additions in Excel 365, designed to make complex formulas easier to read, write, and optimize.

    🧠 What is the LET Function in Excel?

    The LET function allows you to:

    • Define variables within a formula
    • Assign values to those variables
    • Use the variables multiple times without repeating the calculation

    This makes formulas cleaner, faster, and more efficient—especially when repeating the same expressions.

    🧪 Syntax of the LET Function

    LET(name1, name_value1, [name2, name_value2, …], calculation)
    • name1, name2: Variable names (your choice)
    • name_value1, name_value2: The value or expression assigned to the variable
    • calculation: The final expression that uses those variables

    ✅ Benefits of Using LET:

    • Improves readability of complex formulas
    • Reduces redundancy (no need to repeat the same expression)
    • Enhances performance (calculates repeated expressions once)

    🔍 3 Practical Examples of LET in Excel 365

    🔸 Example 1: Simplify a Repeated Calculation

    📍 Task:

    Calculate total profit using Revenue - Cost, but both are used multiple times in the formula.

    🔽 Without LET:

    =(A2*B2 - C2) + (A2*B2 - C2)*0.1

    ✅ With LET:

    =LET(
    Revenue, A2*B2,
    Profit, Revenue - C2,
    Profit + Profit*0.1
)

    ✔️ This is more readable and avoids repeating A2*B2.

    🔸 Example 2: Average of Adjusted Scores

    📍 Task:

    Subtract a baseline (e.g., 5) from each of three values and then average the results.

    ✅ With LET:

    =LET(
    x, A2-5,
    y, B2-5,
    z, C2-5,
    AVERAGE(x, y, z)
)

    This makes it clear what is being subtracted and from where.

    🔸 Example 3: Nested Logical Check

    📍 Task:

    If a score is greater than 40, calculate bonus as 10% of it. If it’s less than or equal to 40, no bonus.

    ✅ With LET:

    =LET(
    score, A2,
    bonus, score*0.1,
    IF(score>40, bonus, 0)
)

    🧠 You can now reuse score and bonus in the formula cleanly.

    📘 Real-Life Use Cases

    • Financial modeling (e.g., tax formulas, profit sharing)
    • Academic scoring systems
    • Inventory management with dynamic thresholds
    • Any case where a formula becomes long or repeats similar calculations

    🎓 Learn More Excel 365 Features Like LET

    Mastering functions like LET can help you write smarter, faster, and more powerful Excel models. For full Excel training with real-world applications:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✔️ Covers:

    • Excel 365 new functions (LET, FILTER, XLOOKUP)
    • Automation with formulas and VBA
    • Real-life dashboards and business models

    🎯 Click to Enroll Now

    Top rated products

  • Create Venn Diagrams in Excel Easily – SmartArt & Shape Methods

    Creating a Venn diagram in Excel is possible, though Excel doesn’t have a built-in Venn chart type. However, you can create one manually using Shapes or with the help of SmartArt. Here’s a step-by-step guide for both methods:

    ✅ Method 1: Using SmartArt (Quick and Easy)

    This is ideal for simple, 2- or 3-circle Venn diagrams for concept representation.

    Steps:

    1. Open Excel and go to a blank worksheet.
    2. Click on the Insert tab in the ribbon.
    3. In the Illustrations group, click SmartArt.
    4. In the dialog box, select Relationship from the left panel.
    5. Choose Basic Venn and click OK.
    6. The Venn diagram will appear. You can:
      • Click on each circle to enter text.
      • Use the SmartArt Design tools to format it.
      • Resize, recolor, and reposition circles as needed.

    🟢 Great for presentations, concept explanations, or comparisons.

    ✅ Method 2: Using Shapes (For More Customization)

    This method lets you control the overlap and data-driven appearance.

    Steps:

    1. Go to the Insert tab > Shapes.
    2. Choose the Oval shape.
    3. Draw a circle on the worksheet (hold Shift while dragging for a perfect circle).
    4. Copy and paste the circle to create two or more.
    5. Drag the circles so they overlap like a Venn diagram.
    6. Right-click each circle > Format Shape:
      • Set Transparency (e.g., 30-50%) to make overlaps visible.
      • Choose different Fill Colors for each circle.

    (Optional) Add Text:

    • Insert Text Boxes inside each area of the diagram to represent categories or data points.

    💡 Tip: Use Group (Ctrl+G) to keep the whole diagram together.

    ⚠️ Excel Limitation:

    These methods are visual only—Excel won’t calculate intersections or set logic automatically like specialized tools (e.g., R, Python, or dedicated Venn chart generators).

    If you want to create a data-driven Venn diagram with set values and intersections calculated, you’d need:

    • PowerPoint or Word (with SmartArt)
    • External tools like Lucidchart, Canva, or web-based Venn generators
    • Or, use Excel VBA with shape manipulation (advanced)

    🎓 Want to Learn More Smart Excel Tricks?

    If you’re enjoying these productivity tips in Excel, you’ll love the complete Excel training course:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    📘 Learn:

    • Advanced Excel charts & visualizations
    • Pivot Tables, Formulas, Data Tools
    • Excel Automation with Macros & VBA
    • Real-world projects and case studies

    👉 Perfect for beginners to professionals.
    🎯 Click here to enroll now!

    On sale products

  • Difference Between Formula and Function in Excel

    Let’s break down the difference between a Formula and a Function in Excel in simple terms, and include detailed examples to make it clear.

    FeatureFormulaFunction
    DefinitionA formula is a user-defined expression to perform calculations.A function is a built-in Excel operation used within formulas.
    Who creates it?Created manually by the userProvided by Excel
    ComplexityCan be simple or complexOften simplifies complex calculations
    Starts withAlways starts with =Always used inside a formula that starts with =
    Examples=A1 + A2=B2*C2-100=SUM(A1:A5)=IF(A1>50, "Pass", "Fail")

    🔍 What is a Formula?

    A formula is any user-created expression that performs a calculation or operation. It can include values, cell references, operators, and functions.

    ✅ Examples of Formulas:

    1. =A1 + A2
      ➤ Adds the values in cells A1 and A2.
    2. =B2 * 10 + C2
      ➤ Multiplies B2 by 10, then adds C2.
    3. =SUM(A1:A5) - D1
      ➤ Uses a function (SUM) within a formula.

    💡 All functions are part of formulas, but not all formulas include functions.

    🔍 What is a Function?

    A function is a predefined operation in Excel that performs a specific task, such as adding numbers, checking conditions, or working with text and dates.

    Functions save time and make complex calculations easier.

    ✅ Common Excel Functions:

    FunctionDescriptionExample
    SUM()Adds a range of numbers=SUM(A1:A5)
    AVERAGE()Finds the mean of values=AVERAGE(B1:B5)
    IF()Performs a logical test=IF(A1>50, "Pass", "Fail")
    VLOOKUP()Looks up a value in a table=VLOOKUP(101, A2:C10, 2, FALSE)
    LEN()Counts characters in a cell=LEN("Excel") returns 5

    🧠 Formula vs Function – A Simple Analogy

    • Think of a formula like a full sentence:
      ➤ “I added two numbers and subtracted 5.”
    • Think of a function like a word or tool used in that sentence:
      ➤ “added” is like the SUM() function.

    ✅ Summary

    FormulaFunction
    Made by the userBuilt-in by Excel
    Can contain operators, values, cell references, and functionsUsed inside formulas
    More flexible but manualEasier and more efficient

    📝 Final Example

    =SUM(A1:A3) + B1
    • This entire thing is a formula
    • Inside it, SUM(A1:A3) is a function

    On sale products

  • Excel Filter Option: Detailed Explanation with Examples

    Excel Filter Option: Detailed Explanation with Examples

    The Filter option in Excel is used to view specific rows in a dataset while hiding the rest, based on criteria you set. It’s especially useful when working with large data sets and you need to focus on certain types of data without deleting or moving anything.

    ✅ How to Apply a Filter in Excel

    1. Select the data range (including headers).
    2. Go to the Home tab or Data tab.
    3. Click on Filter (you’ll see small dropdown arrows appear in the header row).
    4. Click on the dropdown arrow in the column you want to filter.
    5. Choose:
      • Specific values to show
      • Text, Number, or Date filters (e.g., “Contains”, “Greater Than”, “Before”, etc.)

    🔍 Example 1: Filtering Text Data

    NameDepartmentCity
    AnjaliSalesMumbai
    RaviHRDelhi
    MeenaSalesMumbai
    SureshFinancePune
    NehaHRMumbai

    Task: Show only employees from the Sales department.

    Steps:

    • Apply Filter
    • Click on the dropdown in the Department column
    • Select Sales

    Result:

    NameDepartmentCity
    AnjaliSalesMumbai
    MeenaSalesMumbai

    🔢 Example 2: Filtering Numbers

    ProductUnits Sold
    A120
    B80
    C150
    D95

    Task: Show products that sold more than 100 units.

    Steps:

    • Apply Filter
    • Click on dropdown in Units Sold
    • Choose Number Filters > Greater Than > 100

    Result:

    ProductUnits Sold
    A120
    C150

    📅 Example 3: Filtering Dates

    NameJoining Date
    Aman01-Jan-2023
    Pooja15-Feb-2023
    Nikhil20-Jan-2022
    Kiran01-Apr-2023

    Task: Show people who joined in 2023.

    Steps:

    • Apply Filter
    • Click on dropdown in Joining Date
    • Choose Date Filters > After > 31-Dec-2022

    🧠 Real-Life Scenarios Where Filter is Useful

    ✅ 1. HR/Employee Records

    • Filter employees by department, city, date of joining, or performance rating.

    ✅ 2. Sales & Inventory

    • View products with stock less than a threshold.
    • Analyze sales from specific regions or sales reps.

    ✅ 3. Finance

    • Filter transactions above or below a specific amount.
    • Show only “Pending” or “Approved” expenses.

    ✅ 4. School/College Data

    • Show students from a particular grade/class.
    • Filter students who scored above 90 marks.

    ✅ 5. Customer Database

    • Target customers from a specific city or purchase history.

    💡 Bonus Tips

    • Clear Filter: Use “Clear Filter” option to remove applied filters.
    • Filter Multiple Columns: You can apply filters to multiple columns at once.
    • Use Custom Filters: Combine conditions like “greater than 100” AND “less than 200”.
    • Shortcut: Press Ctrl + Shift + L to toggle filters on or off.

    Here is your sample Excel file with filter examples

    Excel_Filter_ExamplesDownload

    Watch the Video to learn Filter

    On sale products