Training By Himanshu

Tag: Excel Tutorial

  • 100 Excel Shortcuts to Boost Productivity and Save Hours at Work

    If you want to save hours every week and become the fastest Excel user in your office, then mastering keyboard shortcuts is the smartest move. They not only speed up your work but also make you look like a true Excel pro.

    100 Excel Shortcuts with Explanations

    Here’s a categorized list so you can learn easily:

    🔹 Basic Shortcuts

    1. Ctrl + N – Create a new workbook instantly.
    2. Ctrl + O – Open an existing workbook.
    3. Ctrl + S – Save the current file.
    4. F12 – Save As dialog box.
    5. Ctrl + P – Print your sheet.
    6. Ctrl + W – Close the current workbook.
    7. Ctrl + F4 – Close Excel completely.
    8. Ctrl + Z – Undo the last action.
    9. Ctrl + Y – Redo the last undone action.
    10. Ctrl + C – Copy selected cells.

    🔹 Navigation Shortcuts

    1. Ctrl + Arrow Keys – Jump to the last filled cell in that direction.
    2. Ctrl + Home – Go to the first cell (A1).
    3. Ctrl + End – Go to the last used cell.
    4. Page Up/Page Down – Move one screen up/down.
    5. Alt + Page Up/Down – Move one screen left/right.
    6. Tab/Shift + Tab – Move right/left in a row.
    7. Ctrl + G (F5) – Go to a specific cell.
    8. Ctrl + F – Find anything in the sheet.
    9. Ctrl + H – Replace text or values.
    10. Ctrl + Backspace – Show active cell.

    🔹 Data Entry & Editing

    1. F2 – Edit the active cell.
    2. Alt + Enter – Insert a line break inside a cell.
    3. Ctrl + D – Fill down from the above cell.
    4. Ctrl + R – Fill right from the left cell.
    5. Ctrl + ; – Insert today’s date.
    6. Ctrl + Shift + : – Insert current time.
    7. Ctrl + Shift + “+” – Insert new row/column.
    8. Ctrl + “-“ – Delete selected row/column.
    9. Ctrl + Space – Select entire column.
    10. Shift + Space – Select entire row.

    🔹 Formatting Shortcuts

    1. Ctrl + B – Bold text.
    2. Ctrl + I – Italic text.
    3. Ctrl + U – Underline text.
    4. Alt + H + O + I – Auto-fit column width.
    5. Alt + H + O + A – Auto-fit row height.
    6. Ctrl + 1 – Format cells dialog box.
    7. Ctrl + Shift + $ – Apply currency format.
    8. Ctrl + Shift + % – Apply percentage format.
    9. Ctrl + Shift + # – Apply date format.
    10. Ctrl + Shift + @ – Apply time format.

    🔹 Selection Shortcuts

    1. Ctrl + A – Select all cells in sheet.
    2. Ctrl + Shift + Arrow Keys – Select range to last filled cell.
    3. Shift + Arrow Keys – Select cells one by one.
    4. Ctrl + Shift + End – Select from current cell to last used cell.
    5. Ctrl + Shift + Home – Select from current cell to A1.
    6. Ctrl + * (asterisk) – Select current data region.
    7. Shift + Space + Ctrl – Select entire worksheet.
    8. F8 – Extend selection mode.
    9. Shift + F8 – Add non-adjacent cells to selection.
    10. Alt + ; – Select only visible cells.

    🔹 Formula Shortcuts

    1. Alt + = – AutoSum quickly.
    2. Shift + F9 – Calculate selected cells.
    3. F9 – Calculate all sheets.
    4. Ctrl + ` (grave accent) – Show formulas instead of results.
    5. Ctrl + Shift + Enter – Enter array formula.
    6. Ctrl + Shift + A – Insert function arguments.
    7. Shift + F3 – Insert function window.
    8. Ctrl + Shift + L – Apply/remove filters.
    9. Alt + Down Arrow – Open filter dropdown.
    10. Ctrl + [ – Trace dependent cells.

    🔹 Worksheet Shortcuts

    1. Ctrl + Page Up – Move to previous sheet.
    2. Ctrl + Page Down – Move to next sheet.
    3. Shift + F11 – Insert new worksheet.
    4. Alt + E + L – Delete current worksheet.
    5. Ctrl + 9 – Hide selected rows.
    6. Ctrl + Shift + 9 – Unhide rows.
    7. Ctrl + 0 – Hide selected columns.
    8. Ctrl + Shift + 0 – Unhide columns.
    9. Alt + O + H + R – Rename sheet.
    10. Ctrl + Drag Sheet Tab – Copy worksheet.

    🔹 Advanced & Miscellaneous

    1. Alt + F1 – Create a chart in same sheet.
    2. F11 – Create chart in new sheet.
    3. Ctrl + K – Insert hyperlink.
    4. Ctrl + Alt + V – Paste Special dialog box.
    5. Ctrl + Shift + V – Paste values only.
    6. Ctrl + Alt + T – Insert table.
    7. Ctrl + Shift + O – Select cells with comments.
    8. Shift + F2 – Edit comment.
    9. Ctrl + Alt + F9 – Recalculate all worksheets.
    10. Alt + F8 – Open macro dialog box.

    🔹 Time-Saving Favorites

    1. Ctrl + T – Create table from data.
    2. Alt + H + S + I – Insert sparkline.
    3. Ctrl + Shift + K – Insert hyperlink quickly.
    4. Ctrl + Shift + U – Expand/Collapse formula bar.
    5. Alt + H + V + S – Paste Special with options.
    6. Alt + H + D + C – Delete column.
    7. Alt + H + D + R – Delete row.
    8. Alt + A + M – Remove duplicates.
    9. Alt + N + P – Insert pivot table.
    10. Alt + F + T – Excel options.

    🔹 Final 10 Power Shortcuts

    1. Ctrl + Alt + Shift + F9 – Force full calculation.
    2. Alt + F11 – Open VBA editor.
    3. Alt + Q – Close VBA editor.
    4. Ctrl + Shift + F3 – Create named ranges.
    5. Ctrl + F3 – Name manager.
    6. Alt + A + T – Apply text to columns.
    7. Alt + H + O + I – Auto-fit column width.
    8. Ctrl + Shift + ! – Apply number format.
    9. Alt + D + F + F – Freeze panes.
    10. Alt + W + F + F – Toggle freeze panes.

    ✅ Final Tip

    Don’t try to memorize all 100 shortcuts in one go. Start with 10 most useful ones (like Copy, Paste Special, AutoSum, Filters, and Navigation). Once they become second nature, add 5–10 more every week. Within a month, you’ll be working twice as fast as before—and easily become the “Excel Champion” in your office.

    Subscribe to our newsletter!

    [newsletter_form type=”minimal”]

    Featured products

  • 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

  • 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 Highlight Entire Rows Based on Multiple Conditions in Excel

    To highlight entire rows based on multiple cell values in Excel, you can use Conditional Formatting with a custom formula. This is especially useful when you want to visually differentiate rows meeting specific conditions.

    ✅ Example Scenario:

    You have a table with columns: Name, Department, and Status.
    You want to highlight entire rows where:

    • Department is “Sales”
      AND
    • Status is “Active”

    🔍 Step-by-Step Guide:

    1. Select Your Data Range

    For example, if your data is in A2:C100, select A2:C100
    (Always start from the top-left cell of your data range.)

    2. Go to Conditional Formatting

    • Click on the Home tab.
    • Click Conditional Formatting → New Rule.
    • Choose “Use a formula to determine which cells to format.”

    3. Enter the Formula

    Assuming:

    • Department is in Column B
    • Status is in Column C
    • The first row of data starts from Row 2

    Use this formula:

    =AND($B2="Sales", $C2="Active")

    ✅ Explanation:

    • $B2 locks the column so Excel evaluates the correct column as it scans across the row.
    • The row number 2 matches the top row of your selection.
    • AND() ensures both conditions are satisfied.

    4. Set the Format

    • Click Format, choose a fill color (e.g., light yellow), bold text, or border.
    • Click OK.

    5. Apply and Done!

    Now all rows where Department = Sales and Status = Active will be highlighted.

    🧠 Tip:

    You can modify the logic:

    • To use OR instead of AND: =OR($B2="Sales", $C2="Active")
    • For number-based conditions, like: =AND($B2="Sales", $C2>80)

    Best selling products

  • How to Highlight Odd or Even Numbers in Excel Using Conditional Formatting

    To highlight odd or even numbers in Excel, you can use Conditional Formatting with a formula. Here’s how:

    ✅ Steps to Highlight Odd Numbers:

    1. Select the range of cells you want to check.
    2. Go to the Home tab → click Conditional Formatting → choose New Rule.
    3. Select “Use a formula to determine which cells to format”.
    4. Enter the formula: =ISEVEN(A1)=FALSE (Replace A1 with the top-left cell of your selection.)
    5. Click Format, choose a color (e.g., light green), and press OK.

    ✅ Steps to Highlight Even Numbers:

    Follow the same steps, but use this formula:

    =ISEVEN(A1)=TRUE

    🧠 Explanation:

    • ISEVEN(number) returns TRUE if a number is even.
    • ISODD(number) returns TRUE if a number is odd.
    • Conditional formatting applies the format when the formula returns TRUE.

    You can use ISODD(A1) instead of ISEVEN(A1)=FALSE if you prefer.

    Top rated products

  • Excel Tables Masterclass: 14 Powerful Tips to Organize, Analyze & Automate Your Data

    Excel Tables are an often-overlooked but game-changing feature for anyone working with structured data. Whether you’re managing sales reports, employee databases, or project trackers, turning your data into a table gives you clarity, structure, automation, and style—all in a few clicks.

    Let’s dive into what makes Excel Tables so powerful and explore 13 expert tips to help you become a Data Guru.

    🔹 1. Instantly Format Your Data with Built-In Table Styles

    Creating a well-styled table is effortless in Excel. Select your data and press Ctrl + T to convert it into a table. Then, go to the Home → Format as Table section to choose from various pre-built styles.

    🎨 Want something custom? Head to the Table Design tab, where you can create your own color themes for headers, alternating rows, and more.

    🔹 2. Zebra Striping Without Extra Work

    Alternating row colors—commonly called “zebra lines”—are automatically applied when you use Excel Tables. This improves readability and removes the need for manual formatting or conditional formatting rules.

    To toggle this feature:

    • Go to the Table Design tab
    • Check or uncheck Banded Rows or Banded Columns

    🔹 3. Built-In Filters and Sorting Per Table

    Each table comes with independent filter and sort buttons at the top of every column. Even if you have multiple tables on the same sheet, each one gets its own filter set—something standard ranges can’t offer.

    🔍 Use these filters to analyze specific segments of your data in seconds.

    🔹 4. Add Slicers for Visual Filtering

    Slicers aren’t just for PivotTables. You can also use them with Excel Tables for interactive filtering.

    To add a slicer:

    • Select the table → Go to Insert or Table Design → Insert Slicer
    • Choose the field you want to filter by (e.g., Department)

    Now, your table updates dynamically as you click through the slicer buttons.

    🔹 5. Say Goodbye to A1:B10, Hello to Structured References

    One of the biggest advantages of Excel Tables is structured referencing. Instead of cryptic cell references like =B2*C2, you can use meaningful formulas like:

    excelCopyEdit=[@Quantity]*[@Price]

    Structured references are self-updating—when you add or remove rows, your formulas remain accurate.

    🔹 6. Effortless Calculated Columns

    Need a new column for a bonus, tax, or score calculation? Just type your formula into the first cell of the column. Excel will:

    • Automatically fill the rest
    • Apply formatting
    • Adjust if the table grows or shrinks

    Example:

    excelCopyEdit=[@Salary]*0.10

    Boom—you just created a Bonus column!

    🔹 7. Total Row for Quick Summaries

    Want a quick SUM, AVERAGE, MAX, or COUNT? Turn on the Total Row from the Table Design tab. A new row appears at the bottom where you can choose the summary type for each column.

    This is a non-destructive way to analyze data on the fly.

    🔹 8. Need to Revert? Convert Table Back to Range

    If you ever want to convert your table back to a normal range:

    • Go to the Table Design tab
    • Click Convert to Range

    Excel will retain your data and formatting but remove table behavior and structured references.

    🔹 9. Create PivotTables in One Click

    Excel Tables are PivotTable-ready. Select any cell in the table, go to Insert → PivotTable, and you’re ready to analyze your data.

    As your table grows, the PivotTable will stay connected—no need to manually update ranges.

    🔹 10. Publish Tables to SharePoint (For Corporate Use)

    Working in an enterprise setting? You can publish your table to a SharePoint List for organization-wide sharing. This is great for leaderboard displays, project trackers, or shared employee directories.

    📌 Requires SharePoint integration with Excel.

    🔹 11. Print Only the Table—Not the Whole Sheet

    Want to print just the table and nothing else?

    • Select any cell in the table
    • Press Ctrl + P
    • Under Print Settings, choose “Print Selected Table”

    Perfect for clean printouts without adjusting margins or page breaks.

    🔹 12. Transform Tables with Power Query

    Want to clean, reshape, or merge table data from multiple sources? Just click:
    Data → Get & Transform → From Table/Range

    Power Query will treat your table as a data source. You can:

    • Remove duplicates
    • Split columns
    • Filter, group, and aggregate
    • Merge with other tables

    It’s a visual way to perform advanced data manipulation without formulas or VBA.

    🔹 13. Link Multiple Tables via Relationships

    Excel allows you to connect multiple tables (like relational databases) using the Data Model. Once linked, you can:

    • Build complex PivotTables using fields from different tables
    • Avoid using VLOOKUP or XLOOKUP
    • Create cleaner, more modular workbooks

    Use the Relationships button under the Data tab to define your connections.

    🔹 14. Use Excel Tables as Dynamic Data Validation Lists

    Excel Tables can power drop-down menus that automatically update when you add or remove list items.

    👉 Scenario:

    You have a table named ProductList with a column called ProductName. You want to create a drop-down list that always reflects the current list of products.

    🛠️ Steps:

    1. Define a named range using: excelCopyEdit=ProductList[ProductName]
    2. Use Data → Data Validation
      Choose “List” and enter: excelCopyEdit=ProductList[ProductName]

    ✅ Now your dropdown menu stays in sync with your table — no manual updates needed!

    🔁 Great for forms, dynamic dashboards, or preventing data entry errors.

    🧠 Final Thoughts: Why Tables Should Be Your Default Structure

    Excel Tables offer:

    • Clean formatting
    • Dynamic ranges
    • Auto formulas
    • Seamless integration with charts, pivots, and slicers
    • Stronger data modeling

    Yet many users ignore them. Don’t be that user.

    Tables are your gateway to Excel mastery—and when combined with tools like Power Query and VBA, they become even more powerful.

    🚀 Take It to the Next Level with Excel VBA Automation

    If you’re enjoying the structure and automation of Excel Tables, you’ll love what VBA (Visual Basic for Applications) can do. Imagine:

    • Creating tables from raw data automatically
    • Adding calculated columns with one click
    • Exporting filtered reports via email or PDF
    • Automating Power Query tasks and refreshing PivotTables

    🎓 Mastering Excel Automation – Excel VBA Training Course

    ✅ Course Highlights:

    • 42 concise and practical videos
    • 4 hours 8 minutes of hands-on training
    • Beginner-friendly, project-based approach
    • Lifetime access for just ₹441 (original price ₹1,299)

    🔗 👉 Enroll Now and Unlock Excel’s Full Potential

    Best selling products

  • How to Use BYROW and BYCOL Functions in Excel 365 with Practical Examples

    🧠 What Are BYCOL and BYROW Functions in Excel 365?

    BYCOL and BYROW are part of the Lambda helper functions in Excel 365. These functions allow you to apply custom logic across columns or rows of a range or array, making them incredibly useful for dynamic and reusable calculations.

    🔹 1. BYROW Function

    ✅ Purpose:

    Processes data row by row, applying a specified Lambda function to each row.

    📘 Syntax:

    excelCopyEdit=BYROW(array, lambda(row))
    • array: The data range you want to process.
    • lambda(row): A custom calculation to perform on each row.

    🧪 Example: Sum each row in a range

    You have this data in cells A2:C4:

    ABC
    235
    142
    627

    👉 Formula:

    excelCopyEdit=BYROW(A2:C4, LAMBDA(r, SUM(r)))

    ✅ Output:

    Sum
    10
    7
    15

    Each row is summed individually and spilled vertically.

    🔹 2. BYCOL Function

    ✅ Purpose:

    Processes data column by column, applying a specified Lambda function to each column.

    📘 Syntax:

    excelCopyEdit=BYCOL(array, lambda(column))
    • array: The data range you want to process.
    • lambda(column): A custom calculation to perform on each column.

    🧪 Example: Find the average of each column

    Same data in A2:C4:

    ABC
    235
    142
    627

    👉 Formula:

    excelCopyEdit=BYCOL(A2:C4, LAMBDA(c, AVERAGE(c)))

    ✅ Output:

    Average
    3.0
    3.0
    4.67

    Each column’s average is calculated and spilled horizontally.

    🔁 When to Use BYROW and BYCOL?

    Use CaseUse Function
    Sum or average of each rowBYROW
    Custom logic applied to each columnBYCOL
    Conditional check row-wiseBYROW + IF
    Min/max/median by columnBYCOL

    💡 More Practical Examples

    🎯 Count how many values > 3 in each row:

    excelCopyEdit=BYROW(A2:C4, LAMBDA(r, COUNTIF(r, ">3")))

    🎯 Find max value in each column:

    excelCopyEdit=BYCOL(A2:C4, LAMBDA(c, MAX(c)))

    ⚠️ Requirements

    • Available in Excel 365 and Excel 2021 only
    • Must use LAMBDA function inside

    🚀 Want to Automate This Logic?

    If you’re excited by what BYCOL and BYROW can do with formulas, imagine how much more powerful Excel becomes when you can automate this logic using VBA macros.

    Instead of manually applying formulas, you could:

    • Automatically summarize each row/column with a button click
    • Dynamically format top values
    • Export row/column summaries to reports

    🎓 Master Excel Automation with VBA (Beginner-Friendly)

    📘 Mastering Excel Automation – Excel VBA Training Course

    🔑 Why Learn VBA?

    • Eliminate repetitive tasks
    • Build powerful Excel tools
    • Automate complex logic (like BYROW/BYCOL) programmatically

    🎬 Course Highlights:

    • 42 easy-to-follow videos
    • 4 hours 8 minutes total
    • ₹441 only (Limited-time offer, originally ₹1,299)
    • Lifetime access

    🎯 Designed for non-programmers and Excel enthusiasts alike!

    🔗 👉 Enroll today and start automating Excel your way

    On sale products

  • Understanding Simpson’s Rule in Excel – With Practical Example

    In the world of data analysis, engineering, and applied mathematics, integration is often required to calculate areas under curves. When dealing with complex functions or raw tabulated data, traditional calculus may not be feasible — and that’s where Simpson’s Rule comes in.

    Excel provides a great platform to apply this technique using formulas, even without using advanced programming.

    🔍 What is Simpson’s Rule?

    Simpson’s Rule is a numerical method that approximates the definite integral of a function by estimating the area under the curve using parabolic arcs rather than straight lines (as in the trapezoidal rule). It provides higher accuracy, especially when the data or function changes curvature.

    ✅ Simpson’s Rule Formula

    For a function f(x)f(x) defined on interval [a,b][a, b], divided into n even sub-intervals, Simpson’s Rule is: ∫abf(x)dx≈h3[f(x0)+4f(x1)+2f(x2)+4f(x3)+⋯+4f(xn−1)+f(xn)]\int_a^b f(x)dx \approx \frac{h}{3} \left[ f(x_0) + 4f(x_1) + 2f(x_2) + 4f(x_3) + \dots + 4f(x_{n-1}) + f(x_n) \right]

    Where:

    • h=b−anh = \frac{b – a}{n}
    • nn is even
    • x0,x1,…,xnx_0, x_1, …, x_n are equally spaced data points

    💼 Real-World Example in Excel

    Let’s apply Simpson’s Rule to estimate the following integral: ∫0411+x2dx\int_0^4 \frac{1}{1 + x^2} dx

    This is the integral of the arctangent function, which cannot be easily integrated manually.

    🧮 Step-by-Step in Excel

    1. Create the x values (A2:A6)
      You divide the interval [0, 4] into 4 equal parts (n = 4):
      0, 1, 2, 3, 4
    2. Create the corresponding f(x) values in column B
      Formula: =1 / (1 + A2^2), then fill down.
    A (x)B = f(x) = 1/(1+x²)
    01.0000
    10.5000
    20.2000
    30.1000
    40.0588
    1. Calculate h
      Formula: = (A6 - A2) / 4 Result: 1
    2. Apply Simpson’s Rule in Excel = (1/3) * (B2 + 4*B3 + 2*B4 + 4*B5 + B6) Result: Approx. 1.3255

    This value is very close to the actual integral of arctangent(4) ≈ 1.3258, showcasing Simpson’s accuracy.

    ✨ Where Can You Use Simpson’s Rule in Excel?

    • When working with experimental data from labs or sensors
    • To approximate areas under curves in physics, finance, biology, and statistics
    • When analytical integration is too complex or not possible
    • For students and professionals who need quick, reliable estimations

    💡 Going Further with Excel Automation

    If you’re finding it powerful to use Excel for such mathematical tasks, imagine how much more you can achieve by automating calculations, creating custom functions, and building interactive tools within Excel itself.

    This is where learning Excel VBA (Visual Basic for Applications) makes a real difference.

    🎓 Learn to Automate Excel with Ease

    Take the next step in your Excel journey with
    Mastering Excel Automation – Excel VBA Training Course

    This online course is a practical guide to Excel automation, helping you eliminate repetitive tasks and build smart Excel solutions.

    🎯 Course Overview:

    • 💻 42 structured video lessons
    • 🕒 4 hours 8 minutes of focused training
    • 🔰 No prior programming needed
    • 📈 Learn variables, loops, conditions, user forms, and more

    🌟 Ideal for:

    • Data professionals
    • Business analysts
    • Students
    • Anyone who wants to enhance productivity in Excel

    💸 Special Price: ₹441 (Originally ₹1,299)
    🎓 Learn at your own pace, with lifetime access

    🔗 Explore the course and unlock your automation potential

    Top rated products

  • How to Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in Excel 365 with Real-World Examples

    📘 Overview of the Functions

    These text functions are new in Excel 365 and Excel 2021, part of the dynamic array functions family. They are useful for splitting or extracting parts of text based on delimiters (like commas, spaces, hyphens, etc.).

    🔹 1. TEXTBEFORE

    ✅ Purpose:

    Extracts the text before a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    You have email addresses in a list and want to extract usernames (text before @).

    🧪 Example:

    excelCopyEdit=TEXTBEFORE("john.doe@gmail.com", "@")

    ➡️ Result: john.doe

    🔹 2. TEXTAFTER

    ✅ Purpose:

    Extracts the text after a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    From an email, you want to extract just the domain name.

    🧪 Example:

    excelCopyEdit=TEXTAFTER("john.doe@gmail.com", "@")

    ➡️ Result: gmail.com

    🔹 3. TEXTSPLIT

    ✅ Purpose:

    Splits a text string into rows or columns using one or more delimiters.

    📘 Syntax:

    excelCopyEdit=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

    🔧 Scenario:

    You have full names like "John,Doe" and want to split them into first name and last name in two columns.

    🧪 Example:

    excelCopyEdit=TEXTSPLIT("John,Doe", ",")

    ➡️ Result:

    AB
    JohnDoe

    🔄 Combined Real-World Example

    🎯 Scenario:

    You have a product list like this:

    arduinoCopyEdit"SKU123-Apple-Fruit"
"SKU456-Banana-Fruit"

    And you want to extract:

    ABCD
    SKU456-Banana-FruitSKU456BananaFruit

    🧪 Formulas:

    To get the SKU:

    excelCopyEdit=TEXTBEFORE(A1, "-")

    To get the Fruit Name:

    excelCopyEdit=TEXTSPLIT(TEXTAFTER(TEXTBEFORE(A1,"-Fruit"), "-"), "-")

    To get the Category:

    excelCopyEdit=TEXTAFTER(A1, "-", 2)

    📝 Summary Table

    FunctionUse Case ExampleDescription
    TEXTBEFORETEXTBEFORE("file.docx", ".")Returns "file" before .
    TEXTAFTERTEXTAFTER("file.docx", ".")Returns "docx" after .
    TEXTSPLITTEXTSPLIT("John,Doe", ",")Splits into "John" and "Doe"

    ✅ Bonus: Why Use These?

    • Avoids complex combinations of LEFT, RIGHT, FIND, and LEN
    • Works dynamically on arrays and ranges
    • Simplifies text cleaning and parsing in data analysis

    Top rated products

  • Excel 365 VALUETOTEXT Function Explained: Syntax, Examples, and Use Cases

    🔤 VALUETOTEXT Function in Excel 365 – Explained in Detail

    ✅ What is VALUETOTEXT?

    The VALUETOTEXT function in Excel 365 converts any value — number, text, logical value, or error — into a text string.

    It is particularly useful when you want to ensure data types are consistent, especially when working with dynamic arrays, formulas, or combining different data types into text outputs.

    📘 Syntax

    =VALUETOTEXT(value, [format])
    ArgumentDescription
    valueThe value or range you want to convert to text
    format (optional)Format type: 0 for concise (default), 1 for strict

    🧩 Format Options

    • 0 (Concise) – Outputs text without quotes, more human-readable.
    • 1 (Strict) – Outputs text with quotes, useful for programming/debugging.

    📌 Examples

    ✅ Example 1: Convert a number to text

    =VALUETOTEXT(123)

    Result: "123"

    ✅ Example 2: Convert boolean to text

    =VALUETOTEXT(TRUE)

    Result: "TRUE"

    ✅ Example 3: Convert a text value (with default format)

    =VALUETOTEXT("Excel")

    Result: "Excel" (No quotes in the result because default format is concise)

    ✅ Example 4: Use strict formatting

    =VALUETOTEXT("Excel", 1)

    Result: "\"Excel\"" (Quotes included)

    ✅ Example 5: Convert a formula result

    =VALUETOTEXT(A1+B1)

    If A1 = 10 and B1 = 20, result: "30"

    🧠 Usage with Arrays

    If you use VALUETOTEXT on an array, it returns each item as a text string, making it useful for debugging array formulas.

    =VALUETOTEXT({1,2,3})

    Result: { "1", "2", "3" } (array of text)

    🎯 When Should You Use VALUETOTEXT?

    • ✅ When building dynamic labels, tooltips, or messages with text + values
    • ✅ When converting numeric output to text format for export
    • ✅ For debugging dynamic array formulas
    • ✅ To standardize data type for further text manipulation or functions like TEXTJOIN, CONCAT, etc.

    ⚠️ Important Notes

    • VALUETOTEXT is available only in Excel 365 and Excel 2021.
    • It’s similar to TEXT, but simpler and doesn’t require a number format.
    • Different from VALUE, which converts text to a number (opposite functionality).

    🔁 Comparison: VALUETOTEXT vs TEXT

    FeatureVALUETOTEXTTEXT
    Converts to text?✅ Yes✅ Yes
    Requires format?❌ No✅ Yes (number format)
    Works on arrays?✅ Yes✅ Yes (limited)
    Output formattingBasic text conversionCustom number/text format

    Best selling products

  • XLOOKUP Function in Excel 365: Complete Guide with Examples and Top 20 Interview Questions

    🔍 How to Use the XLOOKUP Function in Excel 365 — Detailed Guide

    ✅ What is XLOOKUP?

    XLOOKUP is a powerful lookup function introduced in Excel 365 and Excel 2021 to replace older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH. It can search horizontally or vertically, supports approximate/partial matches, and even returns custom messages when no match is found.

    📌 Syntax:

    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
    ArgumentDescription
    lookup_valueThe value to search for
    lookup_arrayThe range or array to search in
    return_arrayThe range or array to return data from
    if_not_found(Optional) Value to return if no match is found
    match_mode(Optional) 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
    search_mode(Optional) 1 = search from first to last (default), -1 = search from last to first

    🧪 Basic Example:

    You have the following data:

    AB
    ProductPrice
    Apple100
    Banana60
    Mango80

    To find the price of Mango:

    =XLOOKUP("Mango", A2:A4, B2:B4)

    ➡️ Result: 80

    🧪 Example with if_not_found:

    =XLOOKUP("Orange", A2:A4, B2:B4, "Not Available")

    ➡️ Result: Not Available (because “Orange” doesn’t exist)

    🧪 Example using wildcard match:

    =XLOOKUP("*man*", A2:A4, B2:B4, , 2)

    ➡️ This matches any product containing “man” (e.g., “Mango”)

    🧪 Reverse Lookup (Bottom to Top):

    =XLOOKUP("Mango", A2:A4, B2:B4, , 0, -1)

    ➡️ Searches from bottom to top. Useful if the latest entry is preferred.

    🧠 20 Interview-Based Questions on XLOOKUP with Answers

    Q1. What is XLOOKUP in Excel?
    A1. XLOOKUP is a modern lookup function that replaces older functions like VLOOKUP and HLOOKUP. It can search vertically or horizontally and offers more flexibility.

    Q2. How is XLOOKUP better than VLOOKUP?
    A2. XLOOKUP allows lookup to the left, supports default return on no match, wildcards, and reverse searches, which VLOOKUP cannot do.

    Q3. Can XLOOKUP search horizontally?
    A3. Yes. You can use it like HLOOKUP by selecting rows instead of columns.

    Q4. What happens if the lookup value is not found?
    A4. If you specify the if_not_found parameter, that value is returned. Otherwise, Excel returns a #N/A error.

    Q5. How can you use XLOOKUP for an exact match?
    A5. Either omit the match_mode (default is exact) or explicitly set it to 0.

    Q6. Can XLOOKUP return an entire row or column?
    A6. Yes, it supports dynamic arrays, so it can return multiple values from a row or column.

    Q7. What does match_mode = 2 mean?
    A7. It enables wildcard matching using * (any number of characters) or ? (single character).

    Q8. What is the purpose of the search_mode parameter?
    A8. It controls the search direction: 1 = top to bottom (default), -1 = bottom to top.

    Q9. Is XLOOKUP case-sensitive?
    A9. No, XLOOKUP is not case-sensitive by default.

    Q10. Can XLOOKUP replace INDEX + MATCH?
    A10. Yes, and it’s simpler to write and understand.

    Q11. What’s the difference between XLOOKUP and LOOKUP?
    A11. LOOKUP is an older function requiring sorted data; XLOOKUP doesn’t and is more robust.

    Q12. What is returned if multiple matches are found?
    A12. XLOOKUP returns the first match, unless search_mode is set to -1 (then it returns the last match).

    Q13. Can XLOOKUP handle blank cells?
    A13. Yes. It will match blank cells if "" is used as the lookup_value.

    Q14. Can XLOOKUP be nested with other functions?
    A14. Yes, it works well inside other functions like IF, SUM, etc.

    Q15. How does XLOOKUP behave in arrays with errors?
    A15. It stops at the first error unless error handling (like IFERROR) is added.

    Q16. Is XLOOKUP available in Excel 2016 or 2019?
    A16. No. XLOOKUP is only available in Excel 365 and Excel 2021.

    Q17. Can XLOOKUP search from right to left?
    A17. Yes, it’s not restricted by column order like VLOOKUP.

    Q18. How to use XLOOKUP for range lookups (approximate match)?
    A18. Set match_mode to -1 (for next smaller) or 1 (for next larger).

    Q19. Can you perform two-way lookups using XLOOKUP?
    A19. Yes. Combine two XLOOKUPs — one for row and one for column.

    Q20. How does XLOOKUP handle dynamic named ranges or structured tables?
    A20. It works seamlessly with dynamic arrays, tables, and named ranges.

    Top rated products

  • Top New Functions in Excel 2021 Explained with Examples: XLOOKUP, FILTER, SORT & More

    🆕 New Functions Introduced in Excel 2021 — Explained with Usefulness

    Microsoft Excel 2021 brought a significant upgrade by introducing several dynamic array functions and smarter lookup and filtering tools. These functions were previously exclusive to Microsoft 365 users but are now part of the perpetual Excel 2021 version, helping users automate tasks, reduce formula complexity, and improve data analysis.

    🔍 1. XLOOKUP

    Purpose: To search for a value in a column or row and return a corresponding value from another column or row.

    Syntax:

    excelCopyEdit=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    Usefulness:

    • Replaces older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH.
    • No need to worry about column numbers or data being sorted.
    • Supports exact, approximate, and wildcard matching.
    • Works both vertically and horizontally.

    Example:

    excelCopyEdit=XLOOKUP("Apple", A2:A100, B2:B100, "Not Found")

    This looks for “Apple” in column A and returns the value from column B in the same row.

    🔢 2. XMATCH

    Purpose: Returns the relative position of a value within a range, similar to MATCH but more versatile.

    Syntax:

    excelCopyEdit=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

    Usefulness:

    • Supports reverse search and wildcard matching.
    • Useful for locating the position of a value in arrays, which can then be used with INDEX or CHOOSE.

    Example:

    excelCopyEdit=XMATCH(50, A1:A10)

    Returns the position of 50 in the range A1:A10.

    🧹 3. FILTER

    Purpose: Extracts only the data that meets certain criteria from a range.

    Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])

    Usefulness:

    • Dynamically displays filtered data in a separate area.
    • Great for dashboards, reporting, or conditional data extraction.
    • Automatically expands or contracts based on the filter results.

    Example:

    excelCopyEdit=FILTER(A2:B10, B2:B10="North")

    Returns only rows where the second column has “North” as the value.

    🔢 4. SORT

    Purpose: Sorts a range or array in ascending or descending order.

    Syntax:

    excelCopyEdit=SORT(array, [sort_index], [sort_order], [by_col])

    Usefulness:

    • Unlike traditional sort, it doesn’t affect the original data.
    • Automatically updates when the source data changes.
    • Useful in dynamic dashboards and data tables.

    Example:

    excelCopyEdit=SORT(A2:B10, 2, -1)

    Sorts the range A2:B10 by the second column in descending order.

    🔢 5. SORTBY

    Purpose: Sorts a range or array based on the values in another array.

    Syntax:

    excelCopyEdit=SORTBY(array, by_array1, [sort_order1], ...)

    Usefulness:

    • More flexible than SORT, as it lets you sort by related fields not in the output.
    • Excellent for sorting one table based on another column or lookup.

    Example:

    excelCopyEdit=SORTBY(A2:B10, C2:C10, 1)

    Sorts A2:B10 based on values in C2:C10 in ascending order.

    🔄 6. UNIQUE

    Purpose: Extracts a list of unique values from a range or array.

    Syntax:

    excelCopyEdit=UNIQUE(array, [by_col], [exactly_once])

    Usefulness:

    • Removes duplicates quickly and dynamically.
    • Especially useful for creating drop-down lists or summary views.

    Example:

    excelCopyEdit=UNIQUE(A2:A100)

    Returns a list of unique values from column A.

    🔢 7. SEQUENCE

    Purpose: Generates a list of sequential numbers in an array format.

    Syntax:

    excelCopyEdit=SEQUENCE(rows, [columns], [start], [step])

    Usefulness:

    • Helpful for creating index numbers, date sequences, or testing data structures.
    • Can generate both 1D and 2D arrays.

    Example:

    excelCopyEdit=SEQUENCE(5,1,10,2)

    Generates 5 numbers starting from 10, increasing by 2 (i.e., 10, 12, 14, 16, 18).

    🎲 8. RANDARRAY

    Purpose: Returns an array of random numbers.

    Syntax:

    excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [integer])

    Usefulness:

    • Generate sample data for testing or simulations.
    • Can return decimal or whole numbers.
    • Recalculates with every workbook change unless frozen with F9.

    Example:

    excelCopyEdit=RANDARRAY(5, 2, 1, 100, TRUE)

    Generates a 5×2 array of random whole numbers between 1 and 100.

    📦 9. LET

    Purpose: Assigns names to calculation results to reuse within a formula, improving performance and readability.

    Syntax:

    excelCopyEdit=LET(name1, value1, calculation)

    Usefulness:

    • Makes complex formulas more readable.
    • Optimizes performance by computing once and reusing.

    Example:

    excelCopyEdit=LET(x, A1+10, x*2)

    Calculates A1 + 10 once, stores it as x, and then returns x * 2.

    📘 Bonus: Other Useful Functions from Excel 2019 Now Common in Excel 2021

    While not brand-new to Excel 2021, the following were improved and widely adopted:

    • TEXTJOIN – Joins multiple text items with a delimiter.
    • IFS – Replaces complex nested IF formulas.
    • SWITCH – Easier alternative to multiple IF statements for fixed-value cases.

    ⚠️ Not Available in Excel 2021

    Some advanced functions are only available in Microsoft 365 (not Excel 2021), such as:

    • TEXTSPLIT
    • DROP, TAKE, VSTACK, HSTACK
    • WRAPROWS, WRAPCOLS
    • TOCOL, TOROW
    • MAP, REDUCE, SCAN, BYROW, BYCOL

    These are part of the Lambda family and advanced array manipulations introduced later in Excel 365.

    Top rated products