Training By Himanshu

Tag: Excel Productivity

  • 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 25 Excel Formulas Every Accountant Should Know (With Clear Examples)

    In today’s business world, accountants rely heavily on Microsoft Excel to manage financial data, prepare reports, and analyze numbers quickly. While anyone can enter data into Excel, mastering the right formulas is what makes an accountant truly efficient and accurate. From simple calculations like SUM and AVERAGE to advanced ones like VLOOKUP, IF, and INDEX-MATCH, these formulas save time, reduce errors, and improve decision-making.

    In this guide, we’ll explore the Top 25 Excel formulas every accountant must know, along with practical examples to help you apply them in real-life accounting tasks.

    Below, I use a simple sample table called Transactions (Excel Table) with columns:
    Date | Voucher | Account | Customer | Amount | Tax | Status | Salesperson

    Tip: Turn your data into a Table with Ctrl + T and use structured references (e.g., Transactions[Amount]).

    Employee_Sample_DataDownload

    1) SUM

    What it does: Adds numbers.

    =SUM(Transactions[Amount])

    Quickly totals all amounts.

    2) SUMIFS

    What it does: Sum with multiple conditions (e.g., date range + account).

    =SUMIFS(Transactions[Amount], Transactions[Account], "Sales", Transactions[Date], ">="&DATE(2025,4,1), Transactions[Date], "<="&DATE(2025,6,30))

    Use case: Q1 sales only; or sum by customer & status.

    3) COUNTIFS

    What it does: Counts rows meeting multiple criteria.

    =COUNTIFS(Transactions[Status], "Paid", Transactions[Account], "Sales")

    How many paid sales invoices?

    4) AVERAGEIFS

    What it does: Average with multiple criteria.

    =AVERAGEIFS(Transactions[Amount], Transactions[Account], "Sales", Transactions[Status], "Paid")

    Average paid invoice value.

    5) IF

    What it does: Logical test → value if true/false.

    =IF([@Status]="Overdue","Follow-up","OK")

    Flags overdue invoices.

    6) IFS

    What it does: Chain multiple conditions neatly.

    =IFS([@Amount]>=100000,"High",[@Amount]>=25000,"Medium",TRUE,"Low")

    7) IFERROR

    What it does: Handles errors gracefully.

    =IFERROR([@[Amount]]/[@[Tax]],0)

    Avoids #DIV/0! when tax is zero.

    8) XLOOKUP

    What it does: Modern, flexible lookup (left/right, exact by default).

    =XLOOKUP("CUST-007", Customers[CustID], Customers[GSTIN], "Not found")

    Also return multiple columns by selecting a multi-column return array.

    9) VLOOKUP (Legacy but common)

    What it does: Vertical lookup (be careful with column index).

    =VLOOKUP("CUST-007", Customers!A:H, 5, FALSE)

    Prefer XLOOKUP where available.

    10) INDEX + MATCH

    What it does: Powerful two-step lookup (works leftward; great for 2D lookups).

    =INDEX(Rates[Rate], MATCH([@Account], Rates[Account], 0))

    Two-way example (row & column):

    =INDEX(PivotArea, MATCH("Sales", RowLabels, 0), MATCH("Apr-2025", ColLabels, 0))

    11) SUMPRODUCT

    What it does: Conditional math without helper columns; weighted averages.

    =SUMPRODUCT((Transactions[Account]="Sales")*(Transactions[Status]="Paid")*Transactions[Amount])

    Weighted average tax rate:

    =SUMPRODUCT(Transactions[Amount], Transactions[Tax]) / SUM(Transactions[Amount])

    12) ROUND, ROUNDUP, ROUNDDOWN

    What they do: Control rounding for reports, invoices, GST.

    =ROUND([@Amount]*1.18, 0)      // nearest rupee
=ROUNDUP([@Amount]*1.18, 0)    // always up
=ROUNDDOWN([@Amount]*1.18, 0)  // always down

    13) ABS

    What it does: Absolute value—useful for variance and adjustments.

    =ABS([@Amount]-[@Budget])

    14) EOMONTH & EDATE

    What they do: Month math—closing, aging buckets.

    =EOMONTH([@Date], 0)            // month-end of transaction month
=EDATE([@Date], 3)              // +3 months

    15) DATE, YEAR, MONTH, DAY

    What they do: Build and dissect dates (reporting, grouping).

    =DATE(2025,4,1)
=YEAR([@Date])    // 2025
=MONTH([@Date])   // 4
=DAY([@Date])     // 1

    16) DATEDIF

    What it does: Precise gaps (undocumented but reliable).

    =DATEDIF([@JoiningDate], TODAY(), "Y")   // years of service

    Other units: "M", "D", "YM" (months ignoring years), "MD".

    17) NETWORKDAYS / NETWORKDAYS.INTL

    What they do: Business days between dates (exclude weekends/holidays).

    =NETWORKDAYS([@InvoiceDate], [@DueDate], HolidayList[Date])

    NETWORKDAYS.INTL lets you define weekend pattern (e.g., Friday–Saturday).

    18) WORKDAY / WORKDAY.INTL

    What they do: Add business days to a date (promised date / SLAs).

    =WORKDAY([@InvoiceDate], 7, HolidayList[Date])   // due date after 7 workdays

    19) TEXT

    What it does: Format numbers/dates to text (report labels, exports).

    =TEXT([@Date], "dd-mmm-yyyy")
=TEXT([@Amount], "₹#,##0.00")

    20) TEXTJOIN / CONCAT

    What they do: Build strings (invoice titles, addresses).

    =TEXTJOIN(", ", TRUE, [@Customer], [@City], [@State])

    Skips blanks with the TRUE argument.

    21) FILTER (Dynamic arrays)

    What it does: Extract rows matching criteria—live query!

    =FILTER(Transactions, (Transactions[Account]="Sales")*(Transactions[Status]="Paid"))

    Great for creating dynamic sub-ledgers.

    22) UNIQUE

    What it does: Distinct lists (customers, accounts) for validation and pivots.

    =UNIQUE(Transactions[Customer])

    23) SUBTOTAL

    What it does: Aware of filters; ignores hidden rows (choose function code 9/109 for SUM).

    =SUBTOTAL(109, Transactions[Amount])   // SUM visible only

    24) NPV, IRR, PMT (Finance Trio)

    What they do: Core finance math for accountants.

    • NPV – Net Present Value:
    =NPV(10%, C2:C7) + C1

    (10% discount rate; C1 is initial outflow if entered as a positive value—add it separately.)

    • IRR – Internal Rate of Return:
    =IRR(C1:C7)
    • PMT – Loan EMI:
    =PMT(10%/12, 60, -500000)

    (10% annual, 60 months, ₹5,00,000 principal.)

    For irregular timings, use XNPV/XIRR.

    25) SORT

    What it does: Sort ranges dynamically (often used with FILTER/UNIQUE).

    =SORT(FILTER(Transactions, Transactions[Status]="Unpaid"), 1, 1)

    Sorts by first column ascending.

    Practical Mini-Scenarios

    A) Aging Bucket (30/60/90+)

    =IFS([@DaysDue]<=30,"0–30",[@DaysDue]<=60,"31–60",[@DaysDue]<=90,"61–90",TRUE,"90+")

    B) Month-End Provisioning

    =IF(EOMONTH([@Date],0)=TODAY(),"Provision","")

    C) Sales by Rep (Dynamic report)

    =LET(
 data, Transactions,
 sales, FILTER(data, data[Account]="Sales"),
 SUMIFS(sales[Amount], sales[Salesperson], H2)
)

    (Using LET to make it readable; optional but powerful.)

    Common Pitfalls & Pro Tips

    • Dates: Use DATE(yyyy,mm,dd) inside criteria (avoid text dates).
    • SUMIFS text criteria: Use operators with & → ">="&DATE(2025,4,1).
    • Rounding: Always round before tax filings/exports to prevent paise mismatches.
    • Dynamic Arrays: If results “spill,” ensure cells below/right are empty.
    • Lookups: Prefer XLOOKUP with a clear not-found message: =XLOOKUP(A2, Map[Code], Map[Name], "No match")

    Quick Reference (What to use when)

    • Conditional totals/counts: SUMIFS, COUNTIFS, SUMPRODUCT
    • Lookups: XLOOKUP (or INDEX+MATCH)
    • Dates & working days: EOMONTH, EDATE, NETWORKDAYS, WORKDAY
    • Cleanup/formatting: TEXT, TEXTJOIN, rounding functions
    • Dynamic reporting: FILTER, UNIQUE, SORT, SUBTOTAL
    • Finance: NPV, IRR, PMT

    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

  • Mastering Excel Formulas and Tables with ChatGPT

    🧑‍💼📊 Meet Jov — The Efficiency Champion

    Jov had a reputation in his office: the “Excel Wizard.” But lately, with increasing workloads, new data formats, and tight deadlines, even Jov’s fingers on Ctrl+C and Ctrl+V weren’t fast enough.

    One evening, while sipping chai and wrestling with a nested IF statement, Jov’s colleague whispered,
    “Why not ask ChatGPT?”

    1️⃣ Using ChatGPT with Excel Formulas

    So Jov opened ChatGPT and typed:
    🗣️ “Help me write an Excel formula that calculates a 10% bonus if sales exceed ₹1,00,000, else 0.”

    Boom! In a second, ChatGPT replied:

    =IF(A2>100000, A2*10%, 0)

    Jov’s eyes lit up. Not only was the formula correct, but it also came with an explanation.

    He realized: ChatGPT wasn’t just a chatbot — it was his new formula assistant.

    Now Jov started doing more:

    • Extracting first names from full names → =LEFT(A2, FIND(" ", A2)-1)
    • Finding last day of a month → =EOMONTH(A2, 0)
    • Creating dropdowns using Data Validation (ChatGPT even explained where to click!)

    2️⃣ Ask ChatGPT for a Simple Excel Formula

    Jov didn’t overthink. He began typing casually:

    🗣️ “Write a formula to calculate total with tax if tax is 18%”

    ChatGPT returned:

    =A2 * (1 + 18%)

    But it also added context:

    “This assumes A2 contains the base price. The formula multiplies it by 1.18 to add 18% tax.”

    And if Jov asked:
    🗣️ “Can you explain this like I’m new to Excel?”

    ChatGPT would simplify it:

    “Sure! This formula takes your number and increases it by 18%. It’s like saying: ‘Give me the price plus 18% more.’”

    That’s when Jov understood — ChatGPT isn’t just a formula writer. It’s a trainer, tutor, and troubleshooter in one.

    3️⃣ Using ChatGPT with Excel Tables

    One Monday, Jov had a messy table:
    Sales data for 10 branches, each with quarterly sales across columns.

    He typed:

    🗣️ “How do I turn this data into a structured Excel table with filters and totals?”

    ChatGPT replied with step-by-step instructions:

    1. Select your data.
    2. Press Ctrl+T to insert a Table.
    3. Use the Table Design tab to enable Total Row.
    4. Use built-in filters for any column.

    Jov followed it and was shocked — no formulas, no fuss — just clean, smart data.

    Then Jov asked:

    🗣️ “How do I write a formula inside a table to calculate growth from Q1 to Q2?”

    ChatGPT responded:

    =[@Q2]-[@Q1]

    “This formula subtracts Q1 from Q2 within the same row. The @ symbol refers to the current row.”

    He smiled — structured references were no longer a mystery.

    4️⃣ Getting More Advanced + Fixing Errors and Adding Comments

    One day, Jov made a mistake. His VLOOKUP returned #N/A.

    He typed:

    🗣️ “Why is my VLOOKUP showing error?”

    ChatGPT analyzed:

    “It could be due to:

    • The lookup value not found in the first column.
    • The data not being sorted correctly.
    • Extra spaces in the cell.”

    It then suggested:

    =IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not Found")

    💡 Jov also asked:
    🗣️ “Can I comment formulas for others to understand?”

    ChatGPT suggested using cell comments or structured explanations in adjacent columns like:

    ="Bonus if sales > 100K: " & IF(A2>100000, "Eligible", "Not eligible")

    Or use Excel Notes:

    Right-click the cell → Insert Note → Explain the logic

    Now Jov not only fixed errors, he also made his sheets understandable for others.

    🏁 Final Act — Jov’s Promotion

    By now, Jov had:

    • Reduced reporting time by 40%
    • Trained teammates using ChatGPT’s plain-English explanations
    • Automated repetitive reports

    One Friday, his manager called him in and said:

    “You’ve made the team more efficient than ever. We want you to lead the new automation unit.”

    All thanks to ChatGPT + Excel.

    ✅ Summary of What Jov Learned

    TaskChatGPT Helped With
    Basic FormulasTax, IF, SUM, AVERAGE
    TablesStructured references, filters, totals
    Errors#N/A, #VALUE!, fixed with IFERROR
    ClarityCommenting, explanations, cell notes
    SpeedFast, natural-language formula creation

    Top rated products

  • How to Use the VSTACK Function to Combine Multiple Sheets in Excel

    The VSTACK function in Excel (available in Microsoft 365 and Excel 2021+) allows you to vertically stack arrays or ranges. It’s especially powerful when you want to combine data from multiple sheets into a single list for reporting, analysis, or dashboards.

    🧠 Function Syntax:

    VSTACK(array1, [array2], …)
    • array1, array2, … are the ranges or arrays you want to stack vertically.
    • All ranges must have the same number of columns.

    📘 Scenario-Based Example: Combine Sales Data of Multiple Cities

    Let’s say you’re maintaining monthly sales data for your retail business across 3 different cities – Delhi, Mumbai, and Kolkata. Each city has its own worksheet with the same structure.

    📄 Sheet: Delhi

    NameProductSales
    RajeshLaptop55000
    AnjaliPhone30000

    📄 Sheet: Mumbai

    NameProductSales
    VikramTablet20000
    NehaPhone25000

    📄 Sheet: Kolkata

    NameProductSales
    ArjunLaptop60000
    PriyaPhone28000

    🛠️ Step-by-Step: Combine All Sheets Using VSTACK

    1. Go to a new sheet called “AllData”.
    2. In cell A1, enter this formula:
    =VSTACK(Delhi!A2:C3, Mumbai!A2:C3, Kolkata!A2:C3)

    ✅ This will vertically combine the data from the three sheets into one continuous table.

    📌 With Header Row Included

    If you also want the headers, you can do:

    =VSTACK({"Name","Product","Sales"}, Delhi!A2:C3, Mumbai!A2:C3, Kolkata!A2:C3)

    This adds a custom header at the top.

    🎯 Tips for Real-World Use

    • Dynamic Ranges: Use Excel Tables or LET function with named ranges for flexibility.
    • Error Handling: Use IFERROR inside nested formulas if some ranges might be empty.
    • Tracking Source Sheet: Add a column with the sheet name:
    =VSTACK(
  CHOOSE({1,2,3,4},
    "Delhi", Delhi!A2:A3, Delhi!B2:B3, Delhi!C2:C3),
  CHOOSE({1,2,3,4},
    "Mumbai", Mumbai!A2:A3, Mumbai!B2:B3, Mumbai!C2:C3),
  CHOOSE({1,2,3,4},
    "Kolkata", Kolkata!A2:A3, Kolkata!B2:B3, Kolkata!C2:C3)
)

    This adds the city name as a column, useful for filtering and pivoting.

    📣 Promote Your Excel Skills

    Want to learn more Excel automation and dynamic functions like VSTACK, LET, FILTER, etc.?

    👉 Mastering MS Excel – A Comprehensive Course
    Build job-ready Excel skills with real-world business scenarios and Indian datasets.

    Top rated products

  • How to Generate QR Codes in Excel and Google Sheets (Step-by-Step Guide)

    You can generate QR codes in Excel (Microsoft 365) and Google Sheets easily using built-in features or free add-ons. Here’s a detailed guide for both platforms:

    ✅ In Microsoft Excel (Microsoft 365)

    🔸 Method 1: Using Excel Add-in – “QR4Office”

    📌 Steps:

    1. Open Excel and go to the Insert tab.
    2. Click on “Get Add-ins” (or Office Add-ins).
    3. Search for “QR4Office” and click Add.
    4. Once added, go to Insert → My Add-ins → QR4Office.
    5. A QR code generator pane will appear on the right.

    🎯 To Generate a QR Code:

    • Enter the text or URL you want to convert.
    • Adjust size, color, and error correction level.
    • Click Insert — the QR code will appear in your sheet as an image.

    🔸 Method 2: Using a Web API (Google Chart API)

    You can generate QR codes dynamically using a formula with an image from an online API.

    📌 Steps:

    1. Use this formula in a cell:
    =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & A2)

    ✅ Replace A2 with the cell that has the text or link you want to turn into a QR code.

    📝 chs=150x150: Size of the QR code
    📝 chl=: The data encoded in the QR code

    Note: Excel’s IMAGE function is available in Microsoft 365 versions only.

    ✅ In Google Sheets

    📌 Steps:

    1. In a cell, enter this formula:
    =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & A2)

    ✅ Replace A2 with the reference cell containing the text or URL you want in the QR code.

    The QR code will appear in the cell as an image.

    🧠 Extra Tips:

    • You can drag the formula down to generate QR codes for an entire list.
    • You can use ENCODEURL(A2) inside the formula to safely encode special characters:
    =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=" & ENCODEURL(A2))

    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

  • What Is the Quick Access Toolbar in Excel?

    The Quick Access Toolbar (QAT) is a small, customizable toolbar located above or below the Ribbon in Microsoft Excel. It allows you to add your most-used commands so they’re always easily accessible, no matter which tab you’re on.

    📍 Where to Find It

    By default, you’ll find it at the top-left corner of the Excel window, right above the File tab and Ribbon.

    You can move it below the Ribbon if you prefer.

    🛠️ Why Use the Quick Access Toolbar?

    • Saves time by giving 1-click access to commonly used actions
    • Works in all Excel tabs (you don’t need to switch tabs to find a command)
    • Fully customizable — you can add, remove, or reorder commands

    ✅ How to Customize the Quick Access Toolbar

    🔹 Step 1: Open the Customization Menu

    • Click the downward arrow icon at the right end of the Quick Access Toolbar
    • Or right-click on any command in the Ribbon and choose “Add to Quick Access Toolbar”

    🔹 Step 2: Choose Built-in Commands

    From the dropdown list, you can quickly add:

    • Save
    • Undo
    • Redo
    • Quick Print
    • Email
    • Sort Ascending/Descending
    • More Commands…

    🔹 Step 3: Add More Commands (Advanced)

    1. Click on “More Commands…”
    2. In the Excel Options window that opens:
      • On the left side: Choose commands to add
      • On the right side: See your current toolbar items
    3. You can choose from:
      • Popular Commands
      • Commands Not in the Ribbon
      • All Commands
      • Macros (if you have any)
    4. Select a command and click Add >>
    5. Click OK to apply

    🔄 Move or Reset the Toolbar

    🔁 Move Below the Ribbon:

    • Click the dropdown arrow → Show Below the Ribbon

    🔁 Reset to Default:

    • Right-click on the toolbar → Reset Quick Access Toolbar

    🎯 Practical Examples

    Example 1: Add “Sort A to Z” Button

    • Go to the Data tab → Right-click “Sort A to Z” → Choose “Add to Quick Access Toolbar”

    Example 2: Add a Macro Button

    • Customize → Choose “Macros” from dropdown → Select your macro → Add it
    • Optionally, change the icon or name for clarity

    🔐 Tip: Use Keyboard Shortcuts with QAT

    Each Quick Access Toolbar command gets a keyboard shortcut like:

    Alt + 1, Alt + 2, etc.
    (depending on the position of the item in the toolbar)

    So if “Save” is the first item, you can press Alt + 1 to save instantly.

    📝 Summary

    FeatureBenefit
    Add Custom Commands1-click access to frequent tools
    Always VisibleNo need to switch Ribbon tabs
    Keyboard FriendlyUse Alt + Number shortcuts
    Supports MacrosAdd your own automated tasks

    💡 Pro Tip

    You can export your Quick Access Toolbar settings and import them on another PC. This is useful if you use Excel across devices or in teams.

    Top rated products

  • ChatGPT for Excel: Complete Productivity Toolkit

    ChatGPT for Excel: Complete Productivity Toolkit

    🔧 Method 1: Using the ChatGPT Excel Plugin via Microsoft Office Add-ins

    📌 Prerequisites:

    🪜 Steps:

    1. Open Excel

    Launch Excel and open a workbook where you want to use ChatGPT.

    2. Insert the ChatGPT Add-in

    • Go to Insert > Get Add-ins (or Home > Add-ins).
    • Search for “ChatGPT for Excel” or “GPT for Sheets and Docs” (some are cross-compatible).
    • Click Add to install it.

    You may see several third-party add-ins that integrate ChatGPT. Choose one with high ratings, or GPT for Sheets and Docs by Talarian if you’re using Excel Online with Google integrations.

    3. Configure the Add-in

    • Open the add-in side panel.
    • Paste your OpenAI API Key.
    • Test the connection to confirm it’s working.

    4. Use GPT Functions

    Once configured, you can use functions like:

    =GPT("Explain the difference between VLOOKUP and XLOOKUP")
=GPT(A1)   'Where A1 contains a question'

    Or structured prompts:

    =GPT("Summarize the following: " & A1)

    💻 Method 2: Using OpenAI API with Excel via VBA

    This method gives you full control by integrating directly with OpenAI’s API.

    📌 Prerequisites:

    • Excel 2016 or later
    • Internet access
    • OpenAI API Key

    🪜 Steps:

    1. Press ALT + F11 to open the VBA Editor

    2. Insert a Module

    • Right-click on VBAProject (YourWorkbook)
    • Select Insert > Module

    3. Paste the VBA Code

    Function GetGPTResponse(prompt As String) As String
    Dim http As Object
    Dim JSON As Object
    Dim apiKey As String
    Dim body As String

    apiKey = "sk-..." ' Replace with your API key

    Set http = CreateObject("MSXML2.XMLHTTP")
    Set JSON = CreateObject("Scripting.Dictionary")

    body = "{""model"":""gpt-3.5-turbo"",""messages"":[{""role"":""user"",""content"":""" & prompt & """}]}"

    With http
        .Open "POST", "https://api.openai.com/v1/chat/completions", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & apiKey
        .send body
    End With

    Dim result As String
    result = http.responseText
    GetGPTResponse = ExtractContent(result)
End Function

Function ExtractContent(response As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = """content"":\s*""(.*?)"""
    regex.Global = False
    regex.IgnoreCase = True
    regex.MultiLine = False

    If regex.Test(response) Then
        ExtractContent = regex.Execute(response)(0).SubMatches(0)
        ExtractContent = Replace(ExtractContent, "\n", vbNewLine)
    Else
        ExtractContent = "Error parsing response."
    End If
End Function

    4. Use the Function in Excel

    =GetGPTResponse("Write a short poem about rain.")

    ⚙️ Method 3: Office Scripts for Excel Online

    If you use Excel Online, Office Scripts can be another way to call the API.

    Steps:

    • Go to Automate > New Script
    • Use TypeScript code to call OpenAI API.
    • You’ll need to use fetch() to call the endpoint (like in a browser).

    (Let me know if you’d like this code snippet too.)

    💡 Tips for Better Usage

    Use CaseExample
    Text summarization=GPT("Summarize: " & A1)
    Data cleaning=GPT("Correct spelling in: " & A1)
    Code generation=GPT("Generate Excel formula for: " & A1)
    Insights & explanation=GPT("Explain why this error occurs in Excel: " & A1)
    Email drafts=GPT("Draft a polite email: " & A1)
    Translation=GPT("Translate to French: " & A1)

    🔒 Security & Limitations

    • Your API key should be kept private.
    • The API has usage limits depending on your OpenAI plan.
    • Responses are limited by token size (max ~4096 tokens for gpt-3.5).
    • VBA solutions may run slower than built-in add-ins.

    📦 Bonus: Build a Custom Ribbon Button for GPT

    You can add a macro button to call GetGPTResponse directly from the Ribbon. Let me know if you want help doing that!

    Absolutely, Himanshu! Here’s a comprehensive and detailed list of everything you can do with ChatGPT in Excel, using plugins or API/VBA integration — complete with practical examples, formulas, and use cases across domains like data analysis, business, education, writing, programming, finance, and more.

    💡 Complete List of Things You Can Do with ChatGPT Plugin in Excel

    🧠 1. Natural Language Q&A

    Ask questions in plain English and get direct answers.

    🔸 Example:

    =GPT("What is compound interest?")

    📤 Output:
    “Compound interest is interest calculated on the initial principal and also on the accumulated interest of previous periods.”

    📊 2. Data Analysis & Interpretation

    Summarize data, extract insights, explain trends, or describe anomalies.

    🔸 Example:

    A
    “Sales dropped in Q2, rose in Q3, peaked in Q4.”
    =GPT("Summarize and suggest a strategy for: " & A1)

    📤 Output:
    “Sales recovered after a Q2 dip. Focus on Q4 strategies such as promotions and bundle offers to maintain momentum.”

    📚 3. Summarization

    Summarize lengthy texts, emails, reports, or customer reviews.

    🔸 Example:

    =GPT("Summarize this feedback: " & A1)

    Use Case:

    • Summarize customer support tickets
    • Executive summary of financial reports
    • Meeting notes into bullet points

    ✍️ 4. Text Generation

    Generate creative or professional text.

    🔸 Examples:

    =GPT("Write a professional apology email for delayed shipment")
=GPT("Create a motivational quote about teamwork")

    Use Case:

    • Email drafts
    • Social media posts
    • Taglines
    • SMS messages for marketing

    🌐 5. Translation

    Translate any text into multiple languages.

    🔸 Example:

    =GPT("Translate to Spanish: " & A1)

    📤 Output:
    Input: “Welcome to our store”
    Output: “Bienvenido a nuestra tienda”

    📝 6. Grammar & Spelling Correction

    Fix common English grammar or spelling issues.

    🔸 Example:

    =GPT("Correct this sentence: " & A1)

    📤 Input: “He go to office everydays”
    📤 Output: “He goes to the office every day.”

    📌 7. Paraphrasing / Rewriting

    Rephrase for tone, clarity, or professionalism.

    🔸 Example:

    =GPT("Paraphrase this to be more professional: " & A1)

    Use Case:

    • Make casual emails more formal
    • Avoid plagiarism in academic texts
    • Simplify complex sentences

    💬 8. Explaining Excel Formulas or Errors

    Get plain English explanations of Excel functions or errors.

    🔸 Example:

    =GPT("Explain this formula: " & A1)

    Where A1 contains:

    =IFERROR(VLOOKUP(B2, D2:E10, 2, FALSE), "Not Found")

    📤 Output:
    “This formula searches for the value in B2 in the first column of D2:E10. If found, it returns the value from the second column. If not found, it displays ‘Not Found’.”

    🧮 9. Generating Excel Formulas

    Describe what you want, and let ChatGPT generate the Excel formula.

    🔸 Example:

    =GPT("Generate Excel formula to calculate percentage change from A1 to B1")

    📤 Output:
    =(B1-A1)/A1

    🔣 10. Converting Pseudocode to Excel Formula

    🔸 Example:

    =GPT("If score is over 90, return 'Excellent', else 'Improve'")

    📤 Output:
    =IF(A1>90, "Excellent", "Improve")

    🧾 11. Summarizing Financial Data

    Give GPT raw financial data, and let it summarize or comment.

    🔸 Example:

    =GPT("Analyze this trend: Revenue = 10k, 12k, 9k, 15k over 4 quarters")

    📤 Output:
    “Revenue was volatile but overall upward. Q3 drop may indicate seasonal weakness or market disruption.”

    🔢 12. Creating Sample Data

    Generate sample names, emails, numbers, cities, etc.

    🔸 Example:

    =GPT("Generate 10 fake Indian names with email addresses")

    📤 Output (Table):

    NameEmail
    Raj Malhotraraj.malhotra@email.com
    Priya Sharmapriya.sharma@email.com

    👨‍💼 13. HR & Resume Support

    Generate job descriptions, performance reviews, or interview questions.

    🔸 Example:

    =GPT("Write a performance review for an Excel trainer")

    🧩 14. Creating Conditional Rules

    Generate formulas for conditional logic or data validation.

    🔸 Example:

    =GPT("Excel formula: if score > 90 then 'A+', if 80-90 then 'A', else 'Fail'")

    📤 Output:
    =IF(A1>90,"A+",IF(A1>=80,"A","Fail"))

    📅 15. Date Calculations

    Ask ChatGPT to create date/time formulas.

    🔸 Example:

    =GPT("Calculate age from birthdate in A1")

    📤 Output:
    =DATEDIF(A1, TODAY(), "Y")

    📈 16. Chart Explanation

    Paste chart description or data, and ask GPT to describe insights.

    🔸 Example:

    =GPT("Sales in Jan=500, Feb=700, Mar=450. Describe the trend.")

    📤 Output:
    “Sales peaked in February and dropped in March. January was moderate.”

    🧾 17. Invoice / Document Text Drafting

    Automatically draft invoice text, headers, footers, terms, etc.

    🔸 Example:

    =GPT("Write payment terms for a freelance invoice")

    🧠 18. Flashcards / Quiz Questions Generation

    Generate study materials from topic keywords.

    🔸 Example:

    =GPT("Make 5 quiz questions about Excel VLOOKUP")

    📤 Output:

    1. What does VLOOKUP stand for?
    2. How many arguments does VLOOKUP require?
      …

    🧮 19. Math Problem Solving

    Solve or explain math problems.

    🔸 Example:

    =GPT("Solve: 3x + 2 = 11")

    📤 Output:
    “x = 3”

    🧑‍💻 20. Code Writing / VBA Scripting Help

    Generate or debug VBA or Python code.

    🔸 Example:

    =GPT("Write a VBA macro to highlight duplicate values in column A")

    📈 21. Financial Calculations

    Ask for loan EMI, IRR, NPV, etc. formula creation or explanations.

    🔸 Example:

    =GPT("Excel formula to calculate monthly EMI for loan of ₹5L @ 8% over 5 years")

    📤 Output:
    =PMT(8%/12, 60, -500000)

    🗃️ 22. Data Categorization or Tagging

    Automatically classify free text into categories.

    🔸 Example:

    =GPT("Classify this feedback: 'The price was too high' into Positive, Negative, Neutral")

    📤 Output:
    “Negative”

    📦 23. Product Descriptions & E-commerce Content

    Generate product titles, SEO tags, descriptions.

    🔸 Example:

    =GPT("Write an Amazon product title for a stainless steel water bottle")

    🎯 24. Goal & Habit Tracking Support

    Ask GPT to help you build a tracking model for daily goals.

    🔸 Example:

    =GPT("Suggest Excel columns to track gym routine with progress")

    📤 Output:
    | Date | Exercise | Sets | Reps | Weight | Duration | Notes |

    📌 25. Miscellaneous Utility Tasks

    • Generate hashtags from a phrase
    • Extract names/locations from text
    • Format phone numbers uniformly
    • Convert units (kg to lbs)
    • Explain acronyms

    🧭 Final Thoughts

    The ChatGPT plugin in Excel isn’t just a chatbot—it becomes your:

    • Formula assistant
    • Language tutor
    • Code generator
    • Financial analyst
    • Business writer
    • Learning buddy

    Here’s your Excel workbook with a detailed list of ChatGPT plugin use cases in Excel:

    ChatGPT_Excel_UseCasesDownload
    Install the No 1 Free Training App

    On sale products

  • Understanding Autofill Series and Justify Option in Excel with Examples

    Understanding Autofill Series and Justify Option in Excel with Examples

    ✅ Autofill Series in Excel

    🔍 What is Autofill?

    Autofill is a feature in Excel that allows users to automatically fill cells with data that follows a pattern or series, such as numbers, dates, days, months, or even custom lists.

    🔹 How to Use Autofill:

    1. Type the starting value in a cell.
    2. Drag the fill handle (small square at the bottom-right of the cell) across or down to fill other cells.
    3. Excel detects the pattern and fills accordingly.

    🔄 Common Series You Can Autofill:

    TypeExample InputAutofill Result
    Numbers1, 21, 2, 3, 4, …
    Dates1-Jan1-Jan, 2-Jan, 3-Jan, …
    DaysMondayMonday, Tuesday, …
    MonthsJanJan, Feb, Mar, …
    Text + NumbersItem1Item1, Item2, …

    🛠️ Customizing Series:

    • Go to Home > Fill > Series for more control.
    • Options: Linear, Growth, Date, AutoFill, etc.

    ✅ Example 1: Linear Series

    • Type 2 in A1, then 4 in A2.
    • Select A1:A2 and drag down.
    • Excel will fill: 2, 4, 6, 8, 10…

    ✅ Example 2: Days of the Week

    • Type Monday in A1, drag down.
    • Excel fills: Monday, Tuesday, Wednesday…

    ✅ Example 3: Custom List

    • Go to File > Options > Advanced > Edit Custom Lists
    • Add a custom list like: “Bronze, Silver, Gold, Platinum”
    • Now you can Autofill this sequence.

    ✅ Justify Option in Excel

    🔍 What is Justify?

    The Justify feature in Excel is used to realign and reflow long text entries across multiple rows so that it fits within a specified column width.

    🔹 How to Use Justify:

    1. Type a long sentence or paragraph in one cell.
    2. Select a range of empty cells in a single column (vertical).
    3. Go to Home > Fill > Justify.

    Excel breaks the text and distributes it across the selected rows, wrapping the words neatly.

    📌 Important Notes:

    • Works only with text in one column.
    • The column must be wide enough, and the destination cells must be empty.
    • It doesn’t wrap inside a cell but spreads across multiple cells vertically.

    ✅ Example:

    Let’s say A1 contains:

    "Excel Justify option is useful for breaking long text into multiple lines within one column."

    Select A1:A4 → Go to Home > Fill > Justify.

    Result:

    A1: Excel Justify option is
A2: useful for breaking long
A3: text into multiple lines
A4: within one column.

    This is useful for cleaning up or displaying long data entries in a more readable format.

    🧠 Summary:

    FeaturePurposeExample Use Case
    AutofillFill cells automatically in a patternFill dates, numbers, or custom lists
    JustifyReflow long text across rows in one columnCleanly break long text into readable parts

    Watch the Video for Autofill Series and Justify options

    On sale products

  • Autofill Date Feature in Excel

    Autofill Date Feature in Excel

    The Autofill feature in Excel is a powerful tool that helps users automatically fill cells with data that follows a pattern or is based on existing data. When working specifically with dates, Autofill can save time by quickly generating series of dates in various formats and intervals.

    🔧 How Autofill for Dates Works

    When you enter a date in a cell and drag the fill handle (a small square at the bottom-right corner of the selected cell), Excel detects the pattern and fills the cells accordingly.

    📅 Common Examples of Autofill with Dates

    1. Daily Increment

    • Start Date: 01-Jan-2025
    • Drag Down → Excel fills:
      • 02-Jan-2025
      • 03-Jan-2025
      • 04-Jan-2025
      • …

    2. Weekday Increment (Excludes Weekends)

    • Type two dates manually: 03-Jan-2025 (Friday), 06-Jan-2025 (Monday)
    • Select both, then drag down.
    • Excel fills:
      • 07-Jan-2025 (Tuesday)
      • 08-Jan-2025 (Wednesday)
      • (skipping weekends)

    3. Weekly Increment

    • Type two dates a week apart: 01-Jan-2025, 08-Jan-2025
    • Select both, drag down:
      • 15-Jan-2025
      • 22-Jan-2025
      • 29-Jan-2025
      • …

    4. Monthly Increment

    • Type two dates a month apart: 01-Jan-2025, 01-Feb-2025
    • Select both, drag down:
      • 01-Mar-2025
      • 01-Apr-2025
      • …

    5. Yearly Increment

    • Type two dates a year apart: 01-Jan-2025, 01-Jan-2026
    • Select both, drag:
      • 01-Jan-2027
      • 01-Jan-2028
      • …

    6. Custom Interval (e.g., Every 2 Days)

    • Type two dates: 01-Jan-2025, 03-Jan-2025
    • Select both, drag:
      • 05-Jan-2025
      • 07-Jan-2025
      • …

    7. Using Fill Series (Advanced Control)

    • Go to Home > Fill > Series
    • Choose options:
      • Series in: Columns or Rows
      • Type: Date
      • Date unit: Day, Weekday, Month, Year
      • Step Value: (e.g., 2 for every 2 days)
      • Stop Value: (optional)

    8. Autofill Day Names

    • Type: Monday
    • Drag:
      • Tuesday
      • Wednesday
      • …
    • Wraps around after Sunday

    9. Autofill Month Names

    • Type: January
    • Drag:
      • February
      • March
      • …
      • December → loops back to January

    10. Custom Date Formats

    • If you format a date as "ddd, dd-mmm-yyyy" and autofill, Excel still understands it’s a date and continues the correct series, maintaining the format:
      • Wed, 01-Jan-2025
      • Thu, 02-Jan-2025
      • Fri, 03-Jan-2025
      • …

    ⚠️ Notes and Tips

    • You must type a valid Excel date (not just text).
    • To copy the same date without incrementing, hold Ctrl while dragging.
    • Autofill works horizontally and vertically.
    • Autofill can also be customized using the “Custom Lists” feature for non-standard sequences.

    Watch Video for Autofill Date

    Download FREE Training App

  • Mastering Autofill in Excel: Fill Values, Text, and Formulas Effortlessly

    In Excel, Autofill is a powerful feature that allows you to automatically fill cells with a series of values, formulas, or formatting. It helps save time and effort, especially when working with large data sets.

    Let’s break down Autofill in detail:

    🔹 What is Autofill?

    Autofill allows you to quickly fill cells with repetitive or sequential data like numbers, dates, days of the week, months, formulas, and custom lists by dragging the fill handle (a small square at the bottom-right corner of a selected cell or range).

    🔹 Types of Values and Text You Can Autofill

    1. Numeric Values

    • Example: If you type 1 in a cell and drag down, Excel fills the same value (1) by default.
    • If you type 1 in A1 and 2 in A2, and select both and drag, Excel detects the pattern and continues (3, 4, 5…).

    2. Text

    • If you type text like "Item" and drag down, Excel repeats "Item" in all the cells.
    • If the text contains a number (e.g., "Item1"), Excel can increment the number part (Item2, Item3…) only if it detects a pattern.

    3. Dates

    • Type 01-Jan-2023, drag down — Excel continues with 02-Jan-2023, 03-Jan-2023, etc.
    • Works for days, months, years.

    4. Days and Months

    • If you enter "Monday" or "January", Excel recognizes it as part of a built-in list and autofills the rest (Tuesday, Wednesday… or February, March…).

    5. Formulas

    • Autofill can copy formulas with relative references.
    • Example: =A1+B1 will become =A2+B2, =A3+B3, etc. as you drag down.

    🔹 How to Use Autofill

    🧭 Method 1: Drag Fill Handle

    1. Enter the starting value(s) in one or more cells.
    2. Select the cell(s).
    3. Move your mouse to the bottom-right corner until the fill handle (a small black square) appears.
    4. Drag it down, right, up, or left to autofill the cells.

    🧭 Method 2: Double-Click Fill Handle

    • If there is adjacent data (like a column next to it filled), double-click the fill handle to autofill down automatically to match the adjacent data’s length.

    🔹 Custom Autofill Lists

    You can create your own autofill list. Example: if you regularly type Step 1, Step 2, Step 3…

    🔧 Steps:

    1. Go to File > Options > Advanced.
    2. Scroll to General > Click Edit Custom Lists.
    3. Add your list (e.g., Step 1, Step 2, Step 3) and click Add.

    🔹 Autofill Options (Smart Tag)

    After using Autofill, a small box appears (Autofill Options). Click it to choose how the data is filled:

    • Copy Cells – Repeats the same value.
    • Fill Series – Continues the pattern.
    • Fill Formatting Only – Applies the same formatting, not values.
    • Fill Without Formatting – Copies values only, not formatting.
    • Flash Fill – Smart fill based on detected patterns (e.g., splitting names).

    🔹 Flash Fill (Smart Autofill)

    Example:

    • If Column A has John Smith, and you type John in Column B and Smith in Column C, Excel can automatically fill the rest of the rows by pattern.
    • Use Ctrl + E or go to Data > Flash Fill.

    🔹 Important Notes

    • Autofill detects patterns, not just values.
    • It works differently for text-only entries and mixed (text + number).
    • Works with both horizontal and vertical ranges.
    • Relative and absolute cell references affect how formulas are autofilled.

    🔚 Summary Table

    Input TypeResult by AutofillNotes
    1, 23, 4, 5...Detects numeric pattern
    JanFeb, Mar...Recognizes built-in list
    MondayTuesday...Day names auto-filled
    Item1Item2, Item3...Text with number = smart pattern
    =A1+B1=A2+B2...Formula with relative reference
    HelloHello, Hello...Repeats text

    Watch the Video