Training By Himanshu

Tag: Advanced Excel

  • Top 20 Excel Tricks That Will Make You Work Faster

    Microsoft Excel is more than just rows and columns—it’s a productivity powerhouse. Yet, most people only use a fraction of its potential. Whether you are a student, a professional, or someone managing personal finances, knowing the right Excel tricks can save you hours of work every week.

    In this article, we’ll cover the top 20 Excel tricks that will make you faster, smarter, and more confident while working with data.

    1. Use Flash Fill for Instant Data Entry

    Typing repetitive patterns like names, email IDs, or codes?

    • Just type the first example, press Ctrl + E, and Excel will auto-complete the rest.
      👉 Example: If you have a column of full names, type the first first-name in the next column and press Ctrl + E. Excel instantly extracts all first names.

    2. Quickly Select Data with Ctrl + Shift + Arrow Keys

    Instead of dragging the mouse, use:

    • Ctrl + Shift + ↓ to select an entire column of data.
    • Ctrl + Shift + → to select a full row.
      Perfect for big data sets!

    3. Turn Numbers into Charts in Seconds

    Highlight your data → Press Alt + F1 → Boom! Instant chart on the same sheet.
    👉 Use F11 to create the chart in a new sheet.

    4. Paste Special (Values, Formats, Operations)

    Right-click → Paste Special (or Ctrl + Alt + V) to:

    • Paste only values (skip formulas).
    • Paste formats only.
    • Even add, subtract, multiply directly while pasting.
      Huge time-saver!

    5. Insert Today’s Date & Time Instantly

    • Ctrl + ; → Inserts today’s date.
    • Ctrl + Shift + ; → Inserts current time.

    6. Use Conditional Formatting for Insights

    Highlight data trends without formulas.
    👉 Example: Use Color Scales to quickly spot highest and lowest values in a report.

    7. Freeze Panes for Easy Navigation

    Working on long spreadsheets?

    • Go to View → Freeze Panes to lock headers or first columns so they stay visible as you scroll.

    8. Quickly Remove Duplicates

    Go to Data → Remove Duplicates.
    👉 Example: Clean email lists or product codes in seconds.

    9. Use Text to Columns

    Split data without formulas.
    👉 Example: Separate first and last names or split data by commas, spaces, or custom delimiters.

    10. VLOOKUP (Still a King!)

    Find data instantly from large tables.
    👉 Example: =VLOOKUP(101, A2:D100, 3, FALSE) → Finds product info for ID 101.

    11. XLOOKUP (The Modern Alternative)

    Available in newer Excel versions. Unlike VLOOKUP, it works left-to-right and right-to-left.
    👉 Example: =XLOOKUP(101, A2:A100, D2:D100)

    12. Use FILTER Function

    Extract data that matches a condition.
    👉 Example: =FILTER(A2:D100, C2:C100=”Sales”) → Pulls all Sales department rows.

    13. Quick AutoSum with Alt + =

    Select a column → Press Alt + = → Excel automatically inserts a SUM formula.

    14. Turn Data into a Table (Ctrl + T)

    Tables auto-expand, have filters, and make formulas easier to manage.

    15. Power Query for Data Cleaning

    Found in Data → Get & Transform Data.
    👉 Combine multiple sheets, clean messy data, and automate tasks without writing a single formula.

    16. Use Named Ranges

    Instead of =SUM(A2:A100), use =SUM(Sales).
    👉 Named ranges make formulas easier to read and maintain.

    17. Keyboard Shortcuts You Must Know

    • Ctrl + Z → Undo
    • Ctrl + Y → Redo
    • Ctrl + F → Find
    • Ctrl + H → Replace
    • Ctrl + Space → Select entire column
    • Shift + Space → Select entire row

    18. IF Function for Logic

    👉 Example: =IF(C2>=50, “Pass”, “Fail”)
    Automates decision-making in your reports.

    19. Use PivotTables for Instant Summaries

    Analyze large data sets without writing formulas.
    👉 Example: Summarize sales by region, month, or product with just a few clicks.

    20. Protect Sheets and Cells

    Go to Review → Protect Sheet to lock formulas while allowing data entry in specific cells.

    ✅ Final Thoughts

    Learning these 20 Excel tricks can easily make you 2X faster at work. The key is not just to know them but to practice regularly. The more you use these shortcuts, formulas, and tools, the more time you’ll save.

    💡 Whether you’re preparing financial reports, handling business data, or cracking a job interview, mastering these Excel hacks will give you a professional edge.

    Office Productivity Courses

    Recommended Laptops

  • 100 Excel Interview Questions and Answers: Crack Your Next MIS, Data Analyst, or Excel Job Interview

    Microsoft Excel is a powerful tool used across industries for data analysis, reporting, financial modeling, and business intelligence. Whether you’re applying for roles in data analysis, finance, accounting, MIS (Management Information System), operations, or even marketing, a strong grip on Excel can set you apart.

    👤 Who Should Use This?

    This list is ideal for:

    • Job seekers in roles like MIS Executive, Data Analyst, Financial Analyst, Business Analyst, Operations Manager, or Accountant
    • Freshers preparing for entry-level roles requiring Excel
    • Professionals upskilling for promotions or transitions to analytical roles
    • Trainers or HR professionals preparing candidates for interviews

    ✅ Excel Interview Questions and Answers (100 Q&A)

    🟩 Section 1: Basic Excel Skills

    1. Q: What is Microsoft Excel used for?
      A: Excel is used for data entry, data analysis, calculations, charting, pivot tables, and automation using formulas and macros.
    2. Q: What is a cell in Excel?
      A: A cell is the intersection of a row and a column where data is entered.
    3. Q: What is the difference between a worksheet and a workbook?
      A: A worksheet is a single sheet in Excel; a workbook is a file containing one or more worksheets.
    4. Q: How do you save a workbook in Excel?
      A: Use Ctrl + S or go to File > Save/Save As.
    5. Q: What are the different data types in Excel?
      A: Text, Numbers, Dates, Boolean (TRUE/FALSE), Currency, and Custom formats.
    6. Q: How do you insert a new row or column?
      A: Right-click on the row/column header > Insert, or use Ctrl + Shift + "+".
    7. Q: How do you freeze panes?
      A: Go to View > Freeze Panes to lock rows/columns for scrolling.
    8. Q: What is a range in Excel?
      A: A range is a selection of two or more cells, e.g., A1:A10.
    9. Q: How can you wrap text in a cell?
      A: Select the cell, go to Home > Wrap Text.
    10. Q: How do you merge cells?
      A: Select cells > Home > Merge & Center.

    🟨 Section 2: Formulas and Functions

    1. Q: What is the difference between a formula and a function?
      A: A formula is user-created (e.g., =A1+A2), while a function is a predefined operation (e.g., =SUM(A1:A2)).
    2. Q: What does the SUM function do?
      A: It adds up numbers in a given range. Example: =SUM(A1:A5)
    3. Q: What is the use of IF function?
      A: It performs logical tests. Example: =IF(A1>50, “Pass”, “Fail”)
    4. Q: What does VLOOKUP do?
      A: It searches for a value in the first column and returns data from a specified column.
      Example: =VLOOKUP(101, A2:C10, 3, FALSE)
    5. Q: What is the difference between VLOOKUP and HLOOKUP?
      A: VLOOKUP searches vertically; HLOOKUP searches horizontally.
    6. Q: What does the INDEX function do?
      A: It returns the value of a cell at a specific row and column in a range.
    7. Q: How does MATCH work?
      A: MATCH returns the position of a value in a range.
      Example: =MATCH(50, A1:A10, 0)
    8. Q: What is the use of CONCATENATE or CONCAT function?
      A: Joins multiple text strings into one.
      Example: =CONCAT(A1, " ", B1)
    9. Q: What is the difference between COUNT, COUNTA, and COUNTBLANK?
      A:
      • COUNT: counts numbers only
      • COUNTA: counts non-empty cells
      • COUNTBLANK: counts empty cells
    10. Q: How do you round numbers in Excel?
      A: Use ROUND, ROUNDUP, or ROUNDDOWN functions.

    🟧 Section 3: Intermediate Excel (Data Tools & Formatting)

    1. Q: What are conditional formatting rules?
      A: They format cells based on criteria (e.g., highlight values > 100).
    2. Q: How do you apply data validation?
      A: Data > Data Validation to restrict input (e.g., allow only numbers 1–100).
    3. Q: What is the use of “Remove Duplicates”?
      A: It deletes repeated data from a range.
    4. Q: How to use Text to Columns?
      A: Data > Text to Columns (used to split data based on delimiters).
    5. Q: What is a named range?
      A: A defined name for a cell or range (e.g., =SalesTotal)
    6. Q: What are sparklines?
      A: Mini charts within a cell to show trends.
    7. Q: How do you use Find and Replace?
      A: Ctrl + F (Find), Ctrl + H (Replace)
    8. Q: What is Flash Fill?
      A: Automatically fills patterns based on previous entries (Ctrl + E)
    9. Q: What is a drop-down list in Excel?
      A: Created using Data Validation to restrict input to a list.
    10. Q: What is the use of Goal Seek?
      A: To find the input value needed to achieve a desired result.

    🟦 Section 4: Charts and Visualizations

    1. Q: How do you insert a chart?
      A: Select data > Insert > Choose a chart type (e.g., column, line, pie)
    2. Q: What is a combo chart?
      A: A chart combining two chart types (e.g., column + line)
    3. Q: What is a pivot chart?
      A: A chart based on PivotTable data.
    4. Q: Can charts be dynamic?
      A: Yes, by using named ranges or tables with formulas.
    5. Q: What is a slicer in charts or pivots?
      A: A filter control used to filter PivotTables visually.

    🟫 Section 5: Pivot Tables & Data Analysis

    1. Q: What is a PivotTable?
      A: A tool to summarize large data sets with drag-and-drop fields.
    2. Q: How do you insert a PivotTable?
      A: Insert > PivotTable > Choose data and location
    3. Q: Can you group data in PivotTable?
      A: Yes, right-click on values > Group (useful for dates or ranges)
    4. Q: What is the difference between Value Field Settings – SUM vs COUNT?
      A: SUM totals numeric values, COUNT counts entries.
    5. Q: How do you refresh a PivotTable?
      A: Right-click > Refresh or use the Refresh button in the Ribbon.

    🟥 Section 6: Advanced Excel

    1. Q: What is Power Query?
      A: A data transformation tool to import, clean, and combine data.
    2. Q: What is Power Pivot?
      A: A data modeling tool to create relationships and use DAX formulas.
    3. Q: What are array formulas?
      A: Formulas that perform multiple calculations on one or more items.
    4. Q: What is the use of XLOOKUP?
      A: A more powerful and flexible replacement for VLOOKUP.
    5. Q: How do you use dynamic arrays like FILTER and SORT?
      A:
      • =FILTER(range, condition) to filter data
      • =SORT(range, column, order) to sort data
    6. Q: What is a dashboard in Excel?
      A: A visual interface using charts, KPIs, and PivotTables to monitor key metrics.
    7. Q: What is DAX in Power Pivot?
      A: Data Analysis Expressions – a formula language for creating custom calculations.
    8. Q: What is a data model in Excel?
      A: A relational database built using Power Pivot or linked tables.
    9. Q: What is Solver?
      A: An add-in used for optimization problems (e.g., maximize profit).
    10. Q: Can Excel connect to external data sources?
      A: Yes, from Access, SQL Server, web, CSV, etc.

    🔵 Section 7: Macros and VBA

    1. Q: What is a macro in Excel?
      A: A recorded sequence of steps that can be replayed.
    2. Q: How do you record a macro?
      A: View > Macros > Record Macro
    3. Q: What is VBA?
      A: Visual Basic for Applications – programming language for automating tasks.
    4. Q: What is a module in VBA?
      A: A container for procedures or code.
    5. Q: How do you open the VBA editor?
      A: Press Alt + F11.

    🟣 Section 8: Macros and VBA (Continued)

    1. Q: What is the difference between a Sub and a Function in VBA?
      A: A Sub performs actions but doesn’t return a value. A Function performs actions and returns a value.
    2. Q: How do you write a simple macro in VBA to display a message box?
      A:
    Sub ShowMessage()
    MsgBox "Hello, this is a message!"
End Sub
    1. Q: How can you run a macro using a button?
      A: Insert a Form Control button from the Developer tab, assign the macro.
    2. Q: What is a UserForm in VBA?
      A: A custom form/dialog box you can design for data entry or interaction.
    3. Q: What are some common uses of VBA in Excel?
      A: Automating reports, generating emails, cleaning data, creating dashboards, etc.

    🔶 Section 9: Error Handling and Troubleshooting

    1. Q: What does #DIV/0! error mean?
      A: Division by zero error – occurs when dividing by 0 or a blank cell.
    2. Q: What is #N/A error?
      A: “Not Available” – typically occurs with lookup functions when value not found.
    3. Q: What is #REF! error?
      A: Invalid cell reference – often happens when a cell referred in a formula is deleted.
    4. Q: What is #VALUE! error?
      A: Incorrect data type used in a formula.
    5. Q: How do you use IFERROR function?
      A: Wrap formulas to catch and replace errors.
      Example: =IFERROR(A1/B1, "Error in calculation")
    6. Q: What is circular reference in Excel?
      A: A formula that refers to its own cell, creating an endless loop.
    7. Q: How do you audit formulas in Excel?
      A: Use Formula Auditing tools (Formulas > Trace Precedents/Dependents)
    8. Q: How to evaluate formulas step by step?
      A: Use “Evaluate Formula” tool under Formulas tab.
    9. Q: What is the purpose of Watch Window?
      A: To monitor the values of key cells during calculations.
    10. Q: How can you protect a worksheet or cell?
      A: Review > Protect Sheet. Use Format Cells > Protection to lock/unlock cells first.

    🔷 Section 10: Excel Productivity Tips

    1. Q: How do you quickly select a range of data?
      A: Use Ctrl + Shift + Arrow keys.
    2. Q: How do you select non-contiguous cells?
      A: Hold Ctrl and click on individual cells.
    3. Q: How do you convert rows to columns (or vice versa)?
      A: Use Paste Special > Transpose.
    4. Q: How do you remove blank rows quickly?
      A: Use filters to find blanks and delete rows.
    5. Q: What does Ctrl + ; do?
      A: Enters the current date.
    6. Q: What does Ctrl + Shift + L do?
      A: Applies or removes filters.
    7. Q: How can you repeat the last action?
      A: Press F4.
    8. Q: How to lock row 1 while scrolling?
      A: View > Freeze Panes > Freeze Top Row.
    9. Q: What does Alt + = do?
      A: Inserts the SUM function automatically.
    10. Q: How do you insert the current time?
      A: Press Ctrl + Shift + ;

    ⚫ Section 11: Scenario-Based & Practical Questions

    1. Q: You have employee data. How do you find duplicate names?
      A: Use Conditional Formatting > Highlight Duplicates or use =COUNTIF(range, cell)>1
    2. Q: How would you create an attendance tracker in Excel?
      A: Use dates in columns, names in rows, and mark “P”/”A”; use COUNTIF for totals.
    3. Q: How to find top 3 sales from a list?
      A: Use =LARGE(range, 1), =LARGE(range, 2), etc.
    4. Q: How to split full names into first and last names?
      A: Use =LEFT() and =RIGHT() with FIND() or use Text to Columns.
    5. Q: How would you highlight weekends in a calendar?
      A: Use Conditional Formatting with formula: =WEEKDAY(A1,2)>5
    6. Q: How do you prepare a monthly sales dashboard?
      A: Use PivotTables, Pivot Charts, Slicers, Conditional Formatting, KPI indicators.
    7. Q: A client sends data in PDF – how do you get it into Excel?
      A: Use Power Query > Get Data from PDF or copy-paste and clean.
    8. Q: How do you track changes in Excel?
      A: Use File > Info > Version History (for OneDrive) or use manual versioning.
    9. Q: How would you remove all hyperlinks in a sheet?
      A: Select all cells > Right-click > Remove Hyperlinks.
    10. Q: How do you compare two columns for matching entries?
      A: Use =IF(A2=B2, "Match", "No Match") or use =COUNTIF(range, value)

    🟤 Section 12: Bonus & Conceptual Questions

    1. Q: What is the default file extension for Excel?
      A: .xlsx (macro-enabled workbook: .xlsm)
    2. Q: Can you open CSV files in Excel?
      A: Yes, Excel can open and edit CSV files.
    3. Q: What are Excel Tables and their benefits?
      A: Structured data ranges with automatic formatting, filters, and dynamic references.
    4. Q: What is a 3D reference in Excel?
      A: A formula referring to the same cell across multiple sheets. Example: =SUM(Sheet1:Sheet3!A1)
    5. Q: What are dynamic named ranges?
      A: Named ranges that adjust automatically as data changes using formulas like OFFSET or INDEX.
    6. Q: How does Excel handle leap years in date calculations?
      A: Excel treats dates as serial numbers and accurately accounts for leap years.
    7. Q: What is the use of INDIRECT function?
      A: Returns a cell reference from a text string. Example: =INDIRECT("A"&1)
    8. Q: What is the TODAY function used for?
      A: Returns the current date. Example: =TODAY()
    9. Q: Can Excel perform web scraping?
      A: Yes, using Power Query or legacy Web connectors (with limitations).
    10. Q: What are some common interview tasks given in Excel interviews?
      A:
    • Creating dashboards
    • Cleaning raw data
    • Performing VLOOKUP/INDEX-MATCH
    • Creating PivotTables
    • Writing formulas for KPIs
    • Automating tasks using macros

    🎓 Final Tips for Excel Interview Preparation

    • Practice real-world Excel projects (MIS reports, dashboards, sales trackers).
    • Be comfortable with both mouse navigation and keyboard shortcuts.
    • Focus on accuracy, speed, and logic—especially when solving lookup or data-cleaning tasks.
    • If the job requires automation, learn VBA basics and Power Query.

    🚀 Master MIS & Data Automation – One Course, Endless Opportunities!


    Boost your career with our Complete MIS Training Program – designed for professionals who want to excel in Data Management, Reporting, and Automation using Excel, Access, Macros, and SQL.

    ✅ 16.5 hours of expert-led video
    📂 26 downloadable resources
    🏅 Certificate of Completion
    💼 Real-world projects & job-ready skills

    👉 Perfect for MIS aspirants, analysts, and working professionals.

    Start now and become the go-to expert for smart data solutions!
    🔗 Enroll today

    Featured products

  • MIS Executive Job Analysis: What Companies Are Really Looking For

    Here’s a detailed job market analysis for the MIS Executive role, based on real listing

    🧩 Key Responsibilities Across Companies

    From startups to giants like Axis Bank, here’s what employers expect from an MIS Executive:

    AreaResponsibilities
    Data Management– Collect, clean & validate data- Maintain live databases (like HRMS or Org Charts)- Ensure data accuracy and integrity
    Reporting– Prepare Daily/Weekly/Monthly MIS reports- Design dashboards & data summaries- Present KPIs (Sales, Inventory, HR, etc.)
    Excel Proficiency– Use advanced formulas (VLOOKUP, HLOOKUP, SUMIF, COUNTIF)- Create Pivot Tables & Charts- Automate reports with Macros
    Cross-functional Coordination– Work with HR, Sales, Ops for inputs- Help in audits & compliance reporting
    Visualization & Insights– Track anomalies & trends- Suggest areas of improvement

    💼 Job Titles

    • MIS Executive
    • MIS Reporting Analyst
    • Data Coordinator
    • Excel Reporting Specialist

    💰 Salary Insights

    ExperienceSalary Range (LPA)
    0–1 Years₹1.75 – ₹3 LPA (Aarti, Axis Bank)
    1–6 Years₹3.25 – ₹4.25 LPA (Bigbasket)

    💡 Tip: The salary varies based on Excel skill level, automation ability, and domain knowledge (retail, HR, finance).

    🎯 Must-Have Skills (From Job Listings)

    ✅ Technical

    • Microsoft Excel (VLOOKUP, HLOOKUP, Pivot Tables, SUMIF, COUNTIF)
    • Excel Automation using Macros (VBA – sometimes optional)
    • Dashboard Creation
    • Basic Data Visualization
    • HRMS, Google Sheets (for HR/Org roles)

    ✅ Soft Skills

    • Attention to detail
    • Communication with cross-teams
    • Analytical thinking
    • Time management for regular reporting

    🎤 Common Interview Questions (and How to Prepare)

    TypeSample QuestionWhat They’re Testing
    Excel Skills“What’s the difference between VLOOKUP and INDEX-MATCH?”Advanced formula knowledge
    Practical“How would you create a monthly sales report with trends?”Real-world Excel reporting
    Scenario“What if a team gives you inconsistent data every week?”Problem-solving & communication
    Tech“Can you automate a daily report?”Macros / Power Query (if applicable)
    Behavioral“Have you ever spotted an anomaly in data?”Attention to detail & impact

    📘 How to Prepare for the MIS Executive Role

    1. Master Excel Thoroughly

    Don’t just “know” Excel. Learn to solve business problems using Excel. Practice:

    • Creating dashboards with Pivot Tables & Charts
    • Writing nested formulas
    • Automating monthly reports
    • Simulating HR or sales data reports

    2. Build Sample Projects

    • Inventory Tracker
    • Employee Attendance Dashboard
    • Sales Performance Analysis
    • HR Org Chart Maintenance (Google Sheets + Excel hybrid)

    3. Be Interview-Ready

    • Prepare 2–3 real examples of Excel work
    • Explain how you improved speed or accuracy
    • Learn to explain technical formulas in simple terms

    💡 Your Path to Becoming an MIS Pro Starts Here…

    If you’re serious about landing an MIS Executive job, Excel is not optional—it’s your core skillset.

    🎓 Master Excel 365 – From Beginner to Advanced is a complete, job-oriented course to take you from basic to pro in just 11.5 hours.

    ✅ Includes:

    • Real-life reporting scenarios
    • VLOOKUP, Pivot Table, Macros, Charts
    • Downloadable resources
    • Certificate of Completion
    • Only ₹299

    🚀 Whether you’re a fresher or upskilling for a promotion—this course will make you confident, interview-ready, and Excel-savvy.

    Top rated products

  • Master Financial Modeling in Excel – From Basics to Advanced Forecasting

    Mastering financial modeling is essential for anyone looking to work in finance, business analysis, or consulting. This step-by-step Excel tutorial walks you through the complete process of building a fully integrated financial model from scratch. You’ll learn to forecast revenues, build dynamic Profit & Loss statements, Balance Sheets, and Cash Flow statements, and perform valuation analysis using DCF and ratio analysis. Whether you’re a beginner or a professional looking to refine your skills, this tutorial gives you a practical and structured approach using Excel’s most powerful functions and techniques.

    💼 Financial Modeling in Excel – Step-by-Step Tutorial

    1. Introduction to the Exercise

    Understand the objectives of financial modeling:

    • Forecast business performance
    • Analyze profitability, liquidity, solvency
    • Build an integrated model: P&L, Balance Sheet, Cash Flow

    You’ll work with:

    • Historical financial data (3–5 years)
    • Forecast assumptions
    • Dynamic Excel functions

    2. Mapping the Financials

    Create a mapping sheet to classify raw data into categories like:

    • Revenue
    • COGS
    • Operating Expenses
    • Assets
    • Liabilities

    Example:

    =IF(A2="Sales","Revenue",IF(A2="Interest Income","Other Income",""))

    Use this mapping to structure output statements.

    3. Build the Output Profit & Loss (P&L) Sheet

    Create a clean summary for P&L:

    • Rows: Revenue, COGS, Gross Profit, OPEX, EBITDA, Net Profit
    • Columns: Historical and forecast years

    Link each line item to mapped categories using SUMIF, INDEX, or MATCH.

    4. Populate Historical Financials in Output P&L

    Pull values from raw input sheets:

    • Use SUMIFS, INDEX-MATCH, or structured references from Power Query outputs.

    Ensure accuracy by cross-verifying totals.

    5. Calculate Percentage Variance and Add Conditional Formatting

    Calculate YoY variance:

    =(CurrentYear - PreviousYear)/PreviousYear

    Add conditional formatting:

    • Green for growth
    • Red for decline

    Improves visual storytelling in reports.

    6. Build the Output Balance Sheet

    Sections:

    • Assets: Current (Cash, AR, Inventory), Non-current (PP&E)
    • Liabilities: Current (AP), Long-term (Loans)
    • Equity: Share Capital, Retained Earnings

    Follow the accounting equation:

    Assets = Liabilities + Equity

    7. Use INDEX-MATCH-MATCH for Balance Sheet Lookup

    For structured and scalable lookup:

    =INDEX(Data!$B$2:$G$100, MATCH("Inventory", Data!$A$2:$A$100, 0), MATCH("2023", Data!$B$1:$G$1, 0))

    This allows flexible, multi-year access.

    8. Add Forecast Period Columns

    Extend your model with forecast years (e.g., FY24E to FY26E).

    Forecast revenue:

    =LastYearRevenue*(1 + AssumedGrowthRate)

    Apply same for cost and other variables using drivers.

    9. Calculate Ratios Using OFFSET and MATCH

    Use ratios to analyze trends and build assumptions:

    • Gross Margin, OPEX %, Net Margin
    • DSO, DPO, DIO, etc.

    Example:

    =OFFSET(P&L!C5,0,1)/OFFSET(P&L!C5,0,0)

    MATCH dynamically selects year/period.

    10. Build Flexible Models with CHOOSE and MATCH

    For scenario-based models:

    =CHOOSE(MATCH(Scenario, {"Base","Best","Worst"}, 0), BaseGrowth, BestGrowth, WorstGrowth)

    Useful for executive decision-making.

    11. Use VLOOKUP and COLUMNS for Dynamic Scenarios

    Another way to automate scenario modeling:

    =VLOOKUP("Revenue", ScenarioSheet!$A$2:$D$10, COLUMNS($A:A)+1, FALSE)

    Each scenario (Base, Best, Worst) in separate columns.

    12. Calculate Historical Working Capital Ratios

    Key ratios:

    • DSO = (Accounts Receivable / Revenue) * 365
    • DPO = (Accounts Payable / COGS) * 365
    • DIO = (Inventory / COGS) * 365
    • Other Assets % = Other Assets / Revenue

    Helps in building accurate cash flow and net working capital forecasts.

    13. Forecast Working Capital Items

    Use historical averages or policy targets to forecast:

    • DSO, DPO, DIO
    • Other Assets & Liabilities as % of Revenue

    Apply to forecast Balance Sheet and cash flow needs.

    14. Build a Fixed Asset Roll Forward

    Track the PP&E movement:

    Opening Balance
+ Additions
– Disposals
– Depreciation
= Closing Balance

    Automate using rows and formulas across forecast years.

    15. Build the Financial Liabilities Schedule

    Track debt repayments and interest:

    • Opening Balance
    • Additions
    • Principal Repayments
    • Interest Expense

    Create an amortization table using formulas:

    =IF(Year<=MaturityYear, PreviousBalance – Repayment, 0)

    16. Build the Equity Schedule

    Track:

    • Issued capital
    • Retained earnings (link to Net Profit)
    • Dividends

    Formula:

    =LastYearRetainedEarnings + NetProfit – Dividends

    Ensure this flows into Balance Sheet and matches accounting identity.

    17. Prepare the Cash Flow Statement

    Break into sections:

    • Operating: Net Profit + adjustments
    • Investing: Capex, asset sales
    • Financing: Loans, repayments, dividends

    Start from Net Profit and adjust:

    =Net Profit + Depreciation – Capex ± Working Capital Changes ± Financing

    18. Calculate Final Cash Flows & Validate Model

    Link ending cash from cash flow to Balance Sheet.

    • Ensure:
    Opening Cash + Net Cash Flow = Closing Cash

    Use a balance check:

    =IF(Assets = Liabilities + Equity, "Balanced", "Error")

    ✅ Final Output

    You now have a:

    • Fully integrated 3-statement model
    • Scenario-based forecasting tool
    • Financial ratio analyzer
    • Decision-making dashboard

    Top rated products

  • How to Create a Pivot Table from Another Pivot Table in Excel (Step-by-Step Guide)

    Creating a Pivot Table from another Pivot Table in Excel can be very helpful when you want to summarize, filter, or analyze data further without returning to the raw source data. Here’s how you can do it the right way, along with best practices and real-world examples.

    🧠 Why Make a Pivot Table from Another Pivot Table?

    Sometimes, your original Pivot Table has too much detail, and you want to:

    • Summarize it again (e.g., monthly to yearly totals)
    • Filter it differently without changing the original
    • Build dashboards with multiple views of the same summarized data

    ✅ Methods to Create a Pivot Table from Another Pivot Table

    🔹 Method 1: Use the Existing Pivot Table as a Data Source

    ⚠️ Note: This works only if the original Pivot Table was created from a data range or table, not from OLAP models or external sources.

    Steps:

    1. Click anywhere inside the original Pivot Table.
    2. Press Ctrl + A to select the whole Pivot Table.
    3. Copy it using Ctrl + C.
    4. Paste it into a new location using Paste Special → Values.
    5. Select the pasted data.
    6. Go to Insert → PivotTable.
    7. Choose the pasted data as your new source.
    8. Click OK.

    You now have a new Pivot Table that is based on the output of the first one, and you can summarize it however you want.

    🔹 Method 2: Convert First Pivot Table to Static Data

    If you want a permanent copy of the summarized data from Pivot #1:

    1. Select the Pivot Table → Right-click → Copy.
    2. Paste it as Values Only using Paste Special (Ctrl + Alt + V).
    3. Use this new static table as the source for your second Pivot Table.

    🔹 Method 3: Use GetPivotData or Power Query (Advanced)

    For more dynamic scenarios:

    • Use GETPIVOTDATA to extract specific values and feed them into formulas or dashboards.
    • Use Power Query to pull data from the Pivot Table range, clean it, and create a new Pivot Table.

    📊 Example Scenario

    Original Pivot Table

    You have a monthly sales Pivot Table:

    MonthSales RepSales Amount
    JanRavi₹25,000
    JanNeha₹30,000
    FebRavi₹22,000
    FebNeha₹33,000

    You now want to:
    👉 Create a yearly total per Sales Rep
    Use the steps above to:

    • Copy & paste the first Pivot Table as values
    • Insert a new Pivot Table summarizing by Sales Rep only

    🚀 Bonus Tip: Use Named Ranges for Flexibility

    If you plan to reuse this method:

    • Convert the pasted values into a named range or Excel Table
    • This helps you reference it dynamically across the workbook

    ⚠️ Important Notes

    • The second Pivot Table won’t update automatically if you change the first one unless it’s linked via formulas or Power Query
    • Always double-check for grand totals or subtotals, which might skew your new Pivot Table

    📘 Want to Learn Pivot Tables Like a Pro?

    ✅ Master dynamic reporting, nested PivotTables, GETPIVOTDATA, slicers, charts, and more in my course:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Best selling products

  • EXPAND Function in Excel 365 – Resize Arrays with Ease

    🔍 What is the EXPAND Function in Excel 365?

    The EXPAND function is a dynamic array function introduced in Excel 365. It allows you to resize an array to a specified number of rows and columns by adding empty cells or a custom value as needed.

    Think of it as a way to force a range into a specific shape, useful when building dynamic templates, padding arrays, or preparing structured data outputs.

    🔧 Syntax

    =EXPAND(array, rows, columns, [pad_with])
    ArgumentDescription
    arrayThe original array to expand
    rowsThe total number of rows desired in the output
    columnsThe total number of columns desired
    pad_with(Optional) The value to use for padding if the array is smaller than the specified size (default is blank)

    ✅ Examples of EXPAND in Excel

    🔹 Example 1: Expand a 2×2 Array to 4×4 with Blanks

    =EXPAND({1,2;3,4}, 4, 4)

    ✅ Output:

    1   2   ""  ""
3   4   ""  ""
""  ""  ""  ""
""  ""  ""  ""

    🔹 Example 2: Expand with a Custom Padding Value

    =EXPAND({1,2;3,4}, 3, 5, 0)

    ✅ Output:

    1   2   0   0   0  
3   4   0   0   0  
0   0   0   0   0

    🔹 Example 3: Use with VSTACK or HSTACK

    You can combine EXPAND with VSTACK to align data nicely:

    =EXPAND(VSTACK({1,2}, {3,4}), 5, 2, "-")

    🔹 Example 4: Prepare Fixed Template Output

    Use EXPAND to standardize report sections, e.g., always show 10 rows in a report, even if data has fewer:

    =EXPAND(A2:B4, 10, 2, "N/A")

    🔹 Example 5: Resize Named Ranges for Dashboards

    Create a uniform input structure for dashboards that doesn’t break when data is missing.

    🧠 Why Use EXPAND?

    • Ensures consistent array size for formulas or visualizations
    • Helps in report automation
    • Pairs well with functions like DROP, TAKE, VSTACK, HSTACK
    • Great for data transformation pipelines

    ❓ 5 Interview-Based Questions on EXPAND

    1. What is the purpose of the EXPAND function in Excel 365?

    Expected Answer: To resize an array to a specified number of rows and columns, filling in missing cells with blank or a defined value.

    2. What will this formula return?

    =EXPAND({10,20;30,40}, 3, 3, "X")

    Answer:

    10   20   X  
30   40   X  
X    X    X

    3. How can EXPAND be used to create a fixed-size export template?

    Expected Answer: By padding data with a default value up to a known row/column count, ensuring uniformity in exports or dashboard feeds.

    4. What happens if the array passed to EXPAND is already larger than the specified size?

    Answer: Excel will not truncate the array — it will simply return the full array. EXPAND only pads; it doesn’t shrink.

    5. Write a formula to expand a 2×2 array into a 4×4 array using the value “NA” as filler.

    =EXPAND({1,2;3,4}, 4, 4, "NA")

    🎓 Learn More Excel 365 Power Functions

    Ready to master advanced Excel functions like EXPAND, REDUCE, SCAN, LAMBDA, and more?

    👉 Join My Excel Mastery Course
    ✅ Covers automation, dynamic reports, dashboards, and real-life use cases.

    Top rated products

  • What is the RANDARRAY Function in Excel 365?

    The RANDARRAY function generates an array of random numbers. You can define the number of rows and columns, the minimum and maximum values, and whether you want whole numbers or decimals.

    It’s part of the dynamic array functions introduced in Excel 365 and is useful for simulations, testing, data sampling, random list creation, and more.

    🔧 Syntax of RANDARRAY

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [whole_number])
    ParameterDescription
    rows(Optional) Number of rows to return
    columns(Optional) Number of columns to return
    min(Optional) Minimum value (default is 0)
    max(Optional) Maximum value (default is 1)
    whole_number(Optional) TRUE for integers, FALSE for decimals (default is FALSE)

    ✅ Examples of RANDARRAY in Excel

    🔹 Example 1: Generate a 5×2 Array of Random Decimals Between 0 and 1

    excelCopyEdit=RANDARRAY(5, 2)

    Generates 5 rows and 2 columns of decimal numbers between 0 and 1.

    🔹 Example 2: Generate 10 Random Whole Numbers Between 1 and 100

    excelCopyEdit=RANDARRAY(10, 1, 1, 100, TRUE)

    Creates a single column of 10 random whole numbers between 1 and 100.

    🔹 Example 3: Generate a 3×3 Matrix of Random Decimals Between 50 and 75

    excelCopyEdit=RANDARRAY(3, 3, 50, 75)

    Each cell contains a random decimal number in the range 50–75.

    🔹 Example 4: Dynamic Range for Randomized Data

    If you link rows/columns to cell values:

    excelCopyEdit=RANDARRAY(A1, B1, 10, 99, TRUE)

    This generates random whole numbers based on user-defined dimensions from cells A1 and B1.

    📌 Key Features

    • Recalculates every time the sheet changes (just like RAND or RANDBETWEEN)
    • Generates arrays dynamically — no need to drag formulas
    • Replaces the need for helper columns when generating random values
    • Supports structured logic when used with INDEX, SORTBY, SEQUENCE, etc.

    🧠 Use Cases

    • Random student roll numbers
    • Create sample datasets for testing
    • Simulate random sampling in analytics
    • Build games or quizzes in Excel
    • Generate randomized IDs, passwords, or numbers

    🔒 Prevent Random Changes

    To freeze the results (make them static):

    1. Select the range.
    2. Press Ctrl + C to copy.
    3. Right-click > Paste Values.

    🚀 Combine with Other Functions

    Example: Randomly sort names in A2:A10

    excelCopyEdit=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

    This randomly shuffles the list of names.

    🎓 Want to Learn More About Excel 365’s Smartest Tools?

    Explore RANDARRAY, SORTBY, UNIQUE, FILTER, LET, and more in real-world projects with my Excel course:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Top rated products

  • Master the XMATCH Function in Excel 365

    📘 What is the XMATCH Function in Excel?

    The XMATCH function is a modern alternative to MATCH, introduced in Excel 365 and Excel 2021. It returns the relative position of an item in a row or column. Unlike MATCH, it supports reverse search, wildcard matching, and exact or approximate search modes.

    🔧 Syntax of XMATCH

    =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
    ParameterDescription
    lookup_valueThe value you want to search for
    lookup_arrayThe range or array to search in
    match_mode(Optional) 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
    search_mode(Optional) 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending

    ✅ Key Features of XMATCH

    • Supports vertical and horizontal lookups
    • Can search from last to first (search_mode = -1)
    • Allows wildcard characters (match_mode = 2)
    • Works with arrays and spilled ranges
    • Better compatibility with dynamic arrays

    🧪 Examples

    🔹 Example 1: Basic Exact Match

    =XMATCH("Priya", A2:A10)

    Searches for “Priya” in the list and returns the position where it’s found.

    ✅ If “Priya” is in cell A5 (4th position in A2:A10), the result is 4.

    🔹 Example 2: Wildcard Match

    =XMATCH("P*", A2:A10, 2)

    Returns the first item starting with “P”.

    ✅ Useful for partial string lookups.

    🔹 Example 3: Reverse Search

    =XMATCH("Complete", A2:A10, 0, -1)

    Searches bottom-up for “Complete”.

    🔹 Example 4: Approximate Match

    If you have numbers like 50, 60, 70, and you’re looking for 65:

    =XMATCH(65, A2:A10, 1)

    Returns the position of the next larger number (70).

    🔹 Example 5: Use with INDEX for Advanced Lookup

    =INDEX(B2:B10, XMATCH("Ravi", A2:A10))

    Finds Ravi in column A and returns corresponding value from column B.

    ✅ Powerful alternative to VLOOKUP or INDEX+MATCH.

    🎓 Common Use Cases

    • Find row/column numbers dynamically
    • Combine with INDEX for 2D lookups
    • Reverse search to find last matching item
    • Match using wildcards like "*Report" or "Jan???"
    • Create dynamic dashboards or filters

    ❓ 5 Interview-Based Questions on XMATCH

    1. What is the key difference between XMATCH and MATCH in Excel? (Expected: XMATCH supports reverse search, wildcards, exact/approximate modes, and works with dynamic arrays.)
    2. How would you find the last occurrence of a value in a list using XMATCH? (Hint: Use search_mode = -1)
    3. What does the following formula return? =XMATCH(75, A2:A6, -1) (Expected: Returns the position of the largest number less than or equal to 75.)
    4. Can XMATCH be used with INDEX to replicate VLOOKUP? Provide an example. (Yes, e.g., =INDEX(B2:B10, XMATCH("ItemName", A2:A10)))
    5. Explain how to use XMATCH for partial matches using wildcards. (Set match_mode = 2, e.g., =XMATCH("Jan*", A2:A10, 2))

    📌 Final Thoughts

    XMATCH is more powerful and flexible than MATCH and a great fit for modern Excel tasks involving dynamic lookups. If you’re preparing for interviews or building advanced dashboards, mastering XMATCH can save time and simplify your logic.

    🚀 Want to Master Excel 365 Lookups?

    Enroll in my in-depth Excel training course covering:

    • XMATCH, XLOOKUP, INDEX-MATCH, FILTER, LET, LAMBDA
    • Dashboards, automation, case studies

    👉 Mastering MS Excel – A Comprehensive Training Course

    On sale 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

  • ISOMITTED Function in Excel 365 – Complete Guide

    The ISOMITTED function is a new and specialized function available in Excel 365 that works exclusively within LAMBDA functions. It’s designed to check if an argument has been omitted when the LAMBDA function is called.

    📘 What is ISOMITTED in Excel?

    ISOMITTED checks whether a specific parameter in a LAMBDA function was provided or left out when the function was called.

    🔧 Syntax:

    =ISOMITTED(argument)
    • argument — A parameter defined in a LAMBDA.
    • Returns TRUE if the argument is omitted, FALSE if provided.

    🧠 Why is it Useful?

    It allows you to:

    • Define optional parameters in your custom functions.
    • Create default values when a parameter is not supplied.
    • Add dynamic behavior depending on whether a user provided an input.

    ✅ Example 1: Optional Discount Argument

    Let’s define a function that calculates the total price after an optional discount.

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100)

    💡 Since discount is omitted, it returns 100 — the original price.

    But:

    =LAMBDA(price, discount,
    IF(
        ISOMITTED(discount),
        price,
        price - price * discount
    )
)(100, 0.2)

    💡 Returns 80 after applying the 20% discount.

    ✅ Example 2: Creating a Named Function

    You can also create a reusable function:

    1. Go to Formulas > Name Manager > New
    2. Name: SmartDiscount
    3. Refers to:
    =LAMBDA(price, discount,
    IF(ISOMITTED(discount), price, price - price * discount)
)

    Now you can use:

    =SmartDiscount(200)        → returns 200  
=SmartDiscount(200, 0.1)   → returns 180

    🛑 Limitations

    • Can only be used inside a LAMBDA function
    • Not available outside that context
    • Only works in Excel 365 and Excel for the Web

    🎯 Use Cases

    • Building reusable Excel mini-apps
    • Creating optional inputs in custom functions
    • Creating smarter calculators with defaults

    🎓 Want to Learn More Excel 365 Advanced Features?

    If you want to explore LAMBDA, ISOMITTED, LET, and more dynamic Excel tools, check this out:

    🔗 Mastering MS Excel – A Comprehensive Training Course

    ✅ Includes:

    • Excel 365-exclusive functions
    • LET, LAMBDA, XLOOKUP, FILTER
    • Real-life examples and automations
    • Projects and templates for professionals

    🎯 Click Here to Enroll Now

    On sale products