Training By Himanshu

Tag: Excel Training

  • 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

  • Power Query for Data Cleaning in Excel: Complete Guide with Examples

    ⚡ Power Query in Excel: Automate Data Cleaning

    🔹 What is Power Query?

    • Power Query is an ETL (Extract, Transform, Load) tool in Excel (also in Power BI).
    • It helps you:
      • Import data from multiple sources (Excel, CSV, SQL, Web, etc.).
      • Clean and transform data (remove blanks, split columns, merge tables, etc.).
      • Automate repetitive tasks — once you build steps, you can refresh anytime to reapply them.

    Shortcut to open: Data Tab → Get & Transform Data → Launch Power Query Editor.

    🔹 Why Use Power Query for Data Cleaning?

    1. Reproducible → Steps are recorded, no need to repeat manually.
    2. Error Reduction → Automates processes, avoids human mistakes.
    3. Time-Saving → One-click refresh updates transformed data.
    4. Handles Large Data → Better than formulas for huge datasets.

    🔹 Common Data Cleaning with Examples

    1️⃣ Remove Duplicates

    • Scenario: You have a sales list with repeated customer IDs.
    • Power Query Step: Home → Remove Rows → Remove Duplicates.
    • ✅ Result: Only unique records remain.

    2️⃣ Remove Blank/Null Values

    • Scenario: A dataset has missing entries in “Email” column.
    • Step: Home → Remove Rows → Remove Blank Rows.
    • ✅ Result: All empty records deleted.

    3️⃣ Change Data Types

    • Scenario: Date column imported as text.
    • Step: Transform → Data Type → Date.
    • ✅ Result: Column correctly recognized for calculations.

    4️⃣ Split Column

    • Scenario: “Full Name” column → “Himanshu Dhar”.
    • Step: Home → Split Column → By Delimiter (Space).
    • ✅ Result: First Name = Himanshu, Last Name = Dhar.

    5️⃣ Merge Queries (Joins)

    • Scenario: Two tables:
      • Table 1 → Customer details
      • Table 2 → Sales transactions
    • Step: Home → Merge Queries → Match on Customer ID.
    • ✅ Result: Combined dataset (like VLOOKUP but more powerful).

    6️⃣ Append Queries

    • Scenario: Monthly sales files Jan.xlsx, Feb.xlsx, Mar.xlsx.
    • Step: Home → Append Queries → Stack them into one table.
    • ✅ Result: One consolidated dataset.

    7️⃣ Remove Columns / Keep Columns

    • Scenario: You only need Customer Name & Sales Amount from 10-column table.
    • Step: Home → Choose Columns → Select relevant ones.
    • ✅ Result: Dataset trimmed to necessary info.

    8️⃣ Unpivot Columns

    • Scenario: Sales report: ProductJanFebMarLaptop100150120
    • Step: Transform → Unpivot Columns.
    • ✅ Result: ProductMonthSalesLaptopJan100LaptopFeb150LaptopMar120

    9️⃣ Replace Values

    • Scenario: Customer field has “NA” instead of blank.
    • Step: Transform → Replace Values (“NA” → null).
    • ✅ Result: Clean data with standard blanks.

    🔟 Group Data (Summarization)

    • Scenario: Sales by Region.
    • Step: Home → Group By → Region → Sum of Sales.
    • ✅ Result: Pivot-like summary inside Power Query.

    🔹 Real-Life Example (End-to-End)

    👉 Imagine you receive monthly sales files from different branches:

    • Step 1: Import all files (Folder option).
    • Step 2: Append Queries to combine them.
    • Step 3: Remove duplicates and null values.
    • Step 4: Split “Customer Name” into First/Last name.
    • Step 5: Merge with Customer Master file for full details.
    • Step 6: Unpivot Month columns for analysis.
    • Step 7: Group data by Region → Total Sales.

    Now, whenever new monthly files are added → just Refresh All → Data updates automatically. 🚀

    🎯 10 Interview Questions & Answers on Power Query

    Q1. What is Power Query in Excel?
    👉 Power Query is a data connection and transformation tool that helps automate importing, cleaning, and reshaping data.

    Q2. How is Power Query different from Excel formulas?
    👉 Formulas work inside sheets, but Power Query builds step-by-step transformations that are refreshable and can handle large datasets more efficiently.

    Q3. Can Power Query handle multiple file imports at once?
    👉 Yes, using the Folder option you can import all Excel/CSV files from a directory and consolidate them.

    Q4. What is the difference between Merge and Append in Power Query?
    👉 Merge = Combine tables side by side (like JOIN/VLOOKUP).
    👉 Append = Stack tables on top of each other (like UNION).

    Q5. What is “Unpivot” in Power Query?
    👉 Unpivot converts column headers into rows, making data tidy for analysis.

    Q6. How do you handle missing or null values in Power Query?
    👉 By removing rows, replacing null with default values, or filling down/up.

    Q7. Can Power Query perform calculations?
    👉 Yes, you can create Custom Columns using formulas in M language (Power Query’s scripting).

    Q8. What is the difference between Power Query and Power Pivot?
    👉 Power Query = Data Cleaning & Shaping.
    👉 Power Pivot = Data Modeling & Analysis with DAX.

    Q9. Is Power Query case sensitive?
    👉 Yes, transformations and M language functions are case sensitive.

    Q10. Give a practical example where you used Power Query.
    👉 Example: Consolidating 12 monthly sales reports, cleaning customer names, and preparing a pivot-ready dataset that refreshes automatically.

    ✅ With this, you can confidently explain Power Query in interviews and also showcase practical knowledge.

    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

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

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

    👤 Who Should Use This?

    This list is ideal for:

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

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

    🟩 Section 1: Basic Excel Skills

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

    🟨 Section 2: Formulas and Functions

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

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

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

    🟦 Section 4: Charts and Visualizations

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

    🟫 Section 5: Pivot Tables & Data Analysis

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

    🟥 Section 6: Advanced Excel

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

    🔵 Section 7: Macros and VBA

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

    🟣 Section 8: Macros and VBA (Continued)

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

    🔶 Section 9: Error Handling and Troubleshooting

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

    🔷 Section 10: Excel Productivity Tips

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

    ⚫ Section 11: Scenario-Based & Practical Questions

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

    🟤 Section 12: Bonus & Conceptual Questions

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

    🎓 Final Tips for Excel Interview Preparation

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

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


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

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

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

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

    Featured products

  • How to Use the FILTER Function for Complete and Partial Text Match in Excel

    The FILTER function in Excel 365 is a powerful tool that allows you to extract rows from a dataset that meet specific criteria. It dynamically spills the matching results into adjacent cells — and is ideal for creating reports, dashboards, and searchable tables.

    🧠 FILTER Function Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])
    • array: The range you want to filter.
    • include: A logical expression that determines which rows to return.
    • if_empty: (Optional) Value to return if no match is found.

    ✅ 1. Complete Match of a Text String

    📘 Scenario:

    You have a list of employees and want to extract all rows where the department is exactly “HR”.

    📊 Sample Data:

    NameDepartment
    RohanHR
    MeenaSales
    AartiHR
    NikhilIT

    📄 Formula:

    excelCopyEdit=FILTER(A2:B5, B2:B5 = "HR", "No match found")

    🟢 Output:

    NameDepartment
    RohanHR
    AartiHR

    ✅ Exact/complete matches only — cells must exactly equal "HR".

    🔍 2. Partial Match of a Text String

    📘 Scenario:

    You want to filter all rows where the department contains the word “Sales”, including partial terms like “Sales – North”, “Sales Team”, etc.

    📊 Sample Data:

    NameDepartment
    RohanHR
    MeenaSales – East
    AartiHR
    NikhilSales Team

    📄 Formula:

    excelCopyEdit=FILTER(A2:B5, ISNUMBER(SEARCH("Sales", B2:B5)), "No match")

    🟢 Output:

    NameDepartment
    MeenaSales – East
    NikhilSales Team

    ✅ SEARCH enables case-insensitive partial matching, and ISNUMBER checks whether the text was found.

    💡 Tip:

    • Use SEARCH("text", cell) for partial, case-insensitive match.
    • Use FIND("text", cell) for partial, case-sensitive match.

    🎯 When to Use Complete vs Partial Match?

    Use CaseMatch TypeFormula Logic
    Filter all “IT” records onlyCompleteB2:B10 = "IT"
    Filter names that contain “Raj”PartialISNUMBER(SEARCH("Raj", A2:A10))

    🚀 Take Filtering Further with Excel VBA

    If you’re impressed by what Excel formulas can do, imagine being able to automate these filters, build custom forms, and generate filtered reports with one click.

    With Excel VBA, you can do all this and more — turning hours of manual work into minutes.

    🎓 Learn to Automate Excel with VBA – Without Prior Coding

    📘 Mastering Excel Automation – Excel VBA Training Course

    ✅ What You’ll Learn:

    • Automate repetitive tasks
    • Build dynamic reports
    • Create custom filters, buttons, and user forms
    • Use loops, conditions, and functions to control your spreadsheets

    🎥 42 structured videos
    🕒 4 hours 8 minutes of hands-on content
    💰 Limited-time price: ₹441 (was ₹1,299)
    📚 Lifetime access – Learn at your own pace

    🌟 Who Should Join?

    • Excel users ready to upgrade their skills
    • Professionals needing smart reporting
    • Beginners looking to enter automation

    🔗 👉 Enroll today and transform the way you use Excel

    Best selling products

  • Excel Mastery: Your Gateway to Data Empowerment

    Excel Mastery: Your Gateway to Data Empowerment

    Dear Excel Enthusiasts,

    Are you ready to embark on a transformative journey through the realm of Excel mastery? Allow me, Himanshu Dhar, your dedicated MIS trainer, to guide you through the intricate landscape of spreadsheets and data management.

    Unveiling the 30-Day Excel Odyssey:

    Our meticulously crafted Excel course spans 30 enriching days, comprising 22 power-packed sessions designed to elevate your Excel proficiency from basic to advanced levels. Let’s delve into what awaits you in this immersive experience:

    Foundation Stones:

    • Introduction to Excel: From unraveling the history of Excel to understanding its object model, we lay the groundwork for your Excel expedition.
    • Autofill & Formatting: Master the art of autofilling data and delve into advanced formatting techniques to make your spreadsheets visually appealing and intuitive.

    Data Manipulation & Analysis:

    • Filtering & Sorting: Learn to wield the power of filters and sorting techniques to swiftly navigate through vast datasets.
    • Conditional Formatting: Transform your data visualization skills with dynamic conditional formatting and unearth insights hidden within your spreadsheets.

    Formula Wizardry:

    • Working with Formulas: From basic arithmetic to complex logical functions, discover the magic of Excel formulas and unleash their full potential in data analysis.
    • Pivot Tables & Charts: Elevate your data analysis game with Pivot Tables and Charts, enabling you to distill meaningful insights effortlessly.

    Fortifying Data Security:

    • Data Protection Techniques: Safeguard your sensitive information with advanced data protection measures and learn to control access to specific ranges within your workbook.

    Print & Presentation:

    • Printing & Viewing Worksheets: Perfect your print layouts and presentations with precision, from adjusting margins to incorporating watermarks and print titles.

    Beyond the Basics:

    • Data Validation, Hyperlinks & What If Analysis: Explore advanced Excel functionalities including data validation, hyperlinks, and powerful what-if analysis tools.

    Automation & Efficiency:

    • Recording Macros: Unleash the power of automation by recording and running macros, streamlining repetitive tasks and boosting productivity.

    Collaboration & Consolidation:

    • Data Outline & Consolidate: Harness the power of data grouping, subtotaling, and consolidation to streamline your data management processes.

    Personalized Learning Experience:

    • Live Sessions & Projects: Engage in interactive live sessions where theoretical concepts seamlessly merge with real-world projects, ensuring a hands-on learning experience.

    Benefits of Enrolling:

    • Expert Guidance: Benefit from over 14 years of industry expertise distilled into comprehensive training modules.
    • Real-world Application: Gain practical insights and techniques directly applicable to diverse business scenarios.
    • Certification: Receive a prestigious certification upon course completion, validating your Excel proficiency.
    • Networking Opportunities: Connect with like-minded professionals and expand your network within the data management sphere.

    Join the Excel Revolution Today!

    Unlock the doors to unparalleled data empowerment and chart your course towards Excel mastery. Join me, Himanshu Dhar, and an esteemed community of learners at I Turn Institute, where knowledge knows no bounds.

    Enroll now and embark on a journey that will redefine your relationship with data forever!

    Sincerely,

    Himanshu Dhar
    Your Excel Mentor