Advanced MS Office & AI Tools

  Step-by-Step Guide to Using the HLOOKUP Function in Excel What is the HLOOKUP Function? The HLOOKUP function (Horizontal Lookup) searches for a value in the first row of a table and returns data from a specified row below it. It is useful when: ✅ Searching for values in a row-based format. ✅ Retrieving information based on column headers. ✅ Looking up data in a horizontal structure. HLOOKUP Function Syntax  =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) lookup_value → The value to search for (e.g., a date). table_array → The range that contains the data (including headers). row_index_num → The row number (relative to the selected range) to return the value from. [range_lookup] → Set to FALSE for an exact match, TRUE for an approximate match. Step-by-Step Process to Use HLOOKUP in the Sample Data Scenario: We want to find an employee’s status (G, A, etc.) on a specific date (e.g., 02-Jan-17 ). Step 1: Select the Cell for the Lookup Result Click on t...

  Detailed Process Flow to Use the YEARFRAC Function in Excel What is the YEARFRAC Function? The YEARFRAC function in Excel calculates the fraction of the year between two dates. It is useful for: ✅ Calculating age from the date of birth (as seen in the image). ✅ Determining the exact number of years between two dates. ✅ Financial calculations like interest accruals or service periods. Syntax of the YEARFRAC Function  =YEARFRAC(start_date, end_date, [basis]) start_date → The beginning date (e.g., Date of Joining). end_date → The ending date (e.g., TODAY() for current age). [basis] (optional) → Determines the method of calculation. Default is 0 (US 30/360). Basis Description 0 US (NASD) 30/360 1 Actual/Actual 2 Actual/360 3 Actual/365 4 European 30/360 To calculate age, we use basis 1 (Actual/Actual). Step-by-Step Process to Use YEARFRAC in Excel Step 1: Select the Cell for the Age Calculation Click on the first cell in the "Age" column where the result should appear (e...

  Detailed Process Flow to Use the COUNTA Function in Excel What is the COUNTA Function? The COUNTA function in Excel is used to count the number of non-empty cells in a given range. It counts: ✅ Numbers ✅ Text ✅ Logical values (TRUE/FALSE) ✅ Errors and formulas returning a value However, it does not count blank cells. Purpose of Using the COUNTA Function To count the total number of non-empty entries in a dataset. To determine the number of filled cells in a column. To verify data completeness (checking missing values). Syntax of the COUNTA Function  =COUNTA(range) range → The group of cells to count. Step-by-Step Process to Use COUNTA Function in Excel Step 1: Select the Cell for the Result Click on the cell where you want to display the count. (In the given image, the result is displayed in D16 under "CountA"). Step 2: Enter the COUNTA Formula To count the total number of non-empty rows in Column A (SL numbers):  =COUNTA(A2:A15) or To count the total number of ...

  Detailed Process Flow to Use the RANK Function in Excel What is the RANK Function? The RANK function in Excel assigns a rank to a number within a list, based on its value relative to other numbers. It is useful for ranking students, employees, or competitors based on scores, percentages, or other numerical values. Purpose of Using the RANK Function To quickly rank numerical values (e.g., percentages, scores, sales figures). To analyze performance by identifying top and bottom ranks. To sort and compare values without manually reordering data. Benefits of the RANK Function ✅ Saves Time – Automates the ranking process. ✅ Accurate & Dynamic – Updates ranks automatically when values change. ✅ Flexible – Can rank in ascending or descending order. Syntax of the RANK Function  =RANK(number, ref, [order]) number → The value to rank. ref → The range of values. order → 0 for descending (highest rank = 1), 1 for ascending (lowest rank = 1). Step-by-Step Process to Use R...

  Detailed Process Flow to Use the MOD Function in Excel What is the MOD Function? The MOD function in Excel returns the remainder after dividing one number by another. It is mainly used to separate integer and decimal values, cycle through repeating sequences, or perform time-related calculations. Purpose of Using the MOD Function To extract the decimal part of a number. To handle time calculations , such as converting decimal hours into standard time format (HH:MM). To cycle through periodic sequences , like assigning shifts or processing recurring patterns. Benefits of the MOD Function ✅ Extracts Decimal or Remainder Values – Helps in breaking down numbers into useful parts. ✅ Simplifies Time Calculations – Converts decimal hours into minutes easily. ✅ Helps in Repeating Patterns – Useful in cyclic processes like scheduling. ✅ Enhances Formatting of Time Data – Useful for making work hours more readable. Syntax of the MOD Function  =MOD(number, divisor) number → The ...

  Detailed Process Flow to Use the ABS Function in Excel What is the ABS Function? The ABS (Absolute Value) function in Excel returns the absolute (non-negative) value of a number. It converts negative numbers into positive ones while leaving positive numbers unchanged. Purpose of Using the ABS Function To remove negative signs from values while keeping their magnitude. To calculate absolute differences in datasets where only positive values are needed. To standardize data when dealing with values that should always be positive. Benefits of the ABS Function ✅ Removes Negative Values – Ensures that all numbers are positive. ✅ Maintains Data Integrity – Keeps values unchanged except for their sign. ✅ Useful for Calculations – Especially helpful in distance, deviation, and variance calculations. ✅ Prevents Errors in Comparisons – Some calculations require only positive values. Syntax of the ABS Function excel Copy Edit =ABS(number) number → The value or cell reference from wh...

  Detailed Process Flow to Use the SORTBY Function in Excel What is the SORTBY Function? The SORTBY function in Excel sorts a range or array based on values in a corresponding range or array. Unlike the traditional SORT function, SORTBY allows sorting based on another column while keeping the original dataset intact. Purpose of the SORTBY Function To sort data dynamically based on one or more columns. To avoid altering the original dataset while still organizing data efficiently. To apply multi-level sorting based on various criteria such as highest working days, department names, or employee codes. Benefits of Using SORTBY Function ✅ Dynamic Sorting – When data updates, the sorting automatically adjusts. ✅ Maintains Original Data – Unlike manually sorting, this function keeps the raw dataset unchanged. ✅ Multi-Level Sorting – You can sort by multiple criteria (e.g., working days first, then department). ✅ Eliminates Manual Work – Reduces the need for frequent manual re-so...

  Step-by-Step Process to Use CONCAT and TEXTJOIN Functions 🔹 Method 1: Using CONCAT to Merge Data Without a Separator Scenario: Combining First and Last Names 🔹 Sample Data (A2:B6) First Name Last Name Rahul Sharma Meera Gupta Amit Verma Sanya Singh Mohan Kapoor 🔹 Formula to Combine First and Last Names Without a Space  =CONCAT(A2,B2) ➡ This merges "Rahul" and "Sharma" into "RahulSharma" . ✔ Solution to Add a Space Manually  =CONCAT(A2, " ", B2) ➡ This results in "Rahul Sharma" . 🔹 Method 2: Using TEXTJOIN to Merge Data with a Separator Scenario: Creating a List of Email Addresses with a Separator 🔹 Sample Data (A2:A6) Name Rahul Meera Amit (Empty) Sanya 🔹 Formula to Combine Names with a Comma Separator and Ignore Blanks  =TEXTJOIN(", ", TRUE, A2:A6) ➡ The result will be: Rahul, Meera, Amit, Sanya (ignores empty cells). ✔ Alternative Example: Joining Full Names from Two Columns  =TEXTJOIN(" - ", TRUE, A2:...

  Step-by-Step Process to Use IFNA and IFERROR Functions 🔹 Method 1: Using IFNA to Handle #N/A Errors Scenario: Lookup values that might not exist Let's say we have a Product List and we want to check the price using VLOOKUP . 🔹 Sample Data (A1:B5) Product Price (₹) Shoes 500 Bags 300 Shirts 400 Jeans 600 🔹 Formula to Lookup Price in Cell D2  =VLOOKUP(D2, A1:B5, 2, FALSE) If we search for a product that doesn't exist (e.g., "Hats"), we get #N/A . ✔ Solution: Using IFNA to Replace #N/A  =IFNA(VLOOKUP(D2, A1:B5, 2, FALSE), "Not Found") ➡ If the product is not found , it will return "Not Found" instead of #N/A . 🔹 Method 2: Using IFERROR to Handle Multiple Errors Scenario: Preventing Division Errors Let's say we have Total Sales and Number of Customers . 🔹 Sample Data (A1:B5) Sales (₹) Customers 10000 50 5000 25 12000 0 8000 40 🔹 Formula to Calculate Average Sales Per Customer  =A2/B2 If B3 contains 0 , Excel returns a #DIV/0! error . ...

What is a Dynamic Screenshot in Excel? A dynamic screenshot is a live, linked image of a selected range of data in Excel. Unlike a regular screenshot, this image updates automatically whenever the source data changes. This is done using the Camera Tool in Excel. Purpose of Using Dynamic Screenshot The dynamic screenshot helps users: ✔ Capture and display live changes in selected data. ✔ Create real-time dashboards without duplicating data. ✔ Maintain a clear and professional visual report. Benefits of Using Dynamic Screenshot ✅ Auto-updates : The image refreshes when the original data changes. ✅ Improves reporting : Helps in creating dynamic dashboards without using VBA or macros. ✅ Enhances visibility : View large datasets in compact areas. ✅ Easy to resize and move : Unlike normal cells, this image can be resized without affecting data formatting. Step-by-Step Process to Use Dynamic Screenshot in Excel Step 1: Add the Camera Tool to the Quick Access Toolbar 1️⃣ Open Excel and...

  Example 1: Simple SEQUENCE Function (Fixed List) This method generates sequential numbering automatically based on the number of rows. If you add new names, the sequence extends automatically. Dataset Example (Before Applying SEQUENCE) Name Percentage Sequence-1 Singh Bisht 95% Kumar Arya 94% Singh Kanyal 93% Singh Mehta 83% Singh Bajetha 78% Nandan Singh 67% Naryan Dutt 62% Govind Ram 56% Singh Mehta 53% Chandan Singh 49% Formula in C2 (Apply in First Row Under "Sequence-1")  =SEQUENCE(ROWS(A2:A11), 1, 1, 1) Dataset Example (After Applying SEQUENCE) Name Percentage Sequence-1 Singh Bisht 95% 1 Kumar Arya 94% 2 Singh Kanyal 93% 3 Singh Mehta 83% 4 Singh Bajetha 78% 5 Nandan Singh 67% 6 Naryan Dutt 62% 7 Govind Ram 56% 8 Singh Mehta 53% 9 Chandan Singh 49% 10 📌 Key Benefits: If you add more rows , the numbering automatically extends. No need to manually type numbers. Ensures accurate sequencing without manual errors. Example 2: SEQUENCE with Sorting (Dynamic Ranking by Per...

  Process Flow to Insert Checkboxes and Lock Cells Using Data Validation in Excel This process allows you to insert checkboxes for evaluation criteria (Punctuality, Job Knowledge, etc.) and lock cells using Data Validation with the formula =$G3<>1 . 🔹 Step 1: Insert Checkboxes Enable Developer Tab : Open Excel → Go to File → Options → Customize Ribbon → Check Developer → Click OK . Insert Checkboxes : Go to Developer Tab → Click Insert → Select Checkbox (Form Control) . Click on the cell where you want the first checkbox (e.g., B2 ). Resize and adjust the position. Right-click → Edit Text → Delete text (optional). Copy and paste checkboxes into other cells under Punctuality, Job Knowledge, etc. 🔹 Step 2: Link Checkboxes to Cells Right-click on each checkbox → Select Format Control . Go to the Control tab → Set Cell Link to the adjacent cell (e.g., H2 for B2 checkbox). Repeat for all checkboxes under the relevant columns. When checked, the linked cell will show ...

  Process Flow to Use the TRANSPOSE Function in Excel The TRANSPOSE function is used to switch (transpose) rows into columns and columns into rows. 📌 Steps to Use the TRANSPOSE Function 1️⃣ Enter Your Data Assume you have the following data in Range A1:C3 : A B C Name Age City Alice 25 Delhi Bob 30 Mumbai 2️⃣ Select an Empty Area to Paste the Transposed Data If your data is in A1:C3 , select an empty range where the transposed version will be displayed (e.g., E1:G3 ). 3️⃣ Apply the TRANSPOSE Function In Cell E1 , enter: excel Copy Edit =TRANSPOSE(A1:C3) Press Ctrl + Shift + Enter (if using older Excel versions) or just Enter (for Excel 365 and 2019). 4️⃣ Result: E F G Name Alice Bob Age 25 30 City Delhi Mumbai 🎯 Note: The transposed data is linked to the original data. If you change the values in A1:C3 , the transposed data in E1:G3 will update automatically. 📌 Alternative: Using Paste Special for Static Data If you want a non-dynamic transposed table , follow these steps: ...

  1. Select Entire Column (Ctrl + Space) 🔹 Steps: Click on any cell within the column you want to select. Press Ctrl + Space → The entire column will be highlighted. 📌 Example: Suppose you have data in Column B , and you want to select it entirely. Click on any cell in Column B and press Ctrl + Space . Now, the whole column is selected. 💡 Use Case: Quickly apply formatting or delete an entire column. 2. Select Entire Row (Shift + Space) 🔹 Steps: Click on any cell within the row you want to select. Press Shift + Space → The entire row will be highlighted. 📌 Example: You have employee records in Row 5 . Click on any cell in Row 5 and press Shift + Space to select the row. 💡 Use Case: Useful when applying styles or inserting/deleting rows. 3. Delete Row or Column (Ctrl + -) 🔹 Steps: Select a row using Shift + Space or a column using Ctrl + Space . Press Ctrl + - (minus key) . Choose "Entire Row" or "Entire Column" and click OK . 📌 Example: Select Row ...

  Process Flow to Use "Show Report Filter Pages" and VBA Code for ThisWorkbook.RefreshAll 1. Using "Show Report Filter Pages" in Pivot Tables This feature allows you to create separate sheets for each filter value in a Pivot Table. Steps to Use "Show Report Filter Pages": Select the Pivot Table Click anywhere inside the Pivot Table. Go to the Analyze/Options Tab (Excel Ribbon) Click PivotTable Analyze (or Options in older versions) . Choose "Show Report Filter Pages" Click "Show Report Filter Pages" under the PivotTable Tools. Select the Filter Field A dialog box will appear listing all filter fields. Select the field for which you want to create separate sheets. Click OK Excel will automatically generate a new sheet for each unique value in the selected filter. 2. VBA Code to Refresh All Data ( ThisWorkbook.RefreshAll ) To refresh all Pivot Tables, Queries, and External Data in the workbook, use the following VBA code: VBA Code: vba ...

  Process Flow to Fill Blank Cells Using "Go To Special" & Ctrl + Enter Select the Data Range Highlight the column or range containing blank cells that need to be filled. Open "Go To Special" Press Ctrl + G → Click Special → Choose Blanks → Click OK . This will select all blank cells within the selected range. Reference the Above Data Type = (up arrow or reference the above cell) to fill blanks with the value above. Apply to All Blanks Press Ctrl + Enter to apply the formula to all selected blank cells at once. Convert to Static Values (Optional) If needed, copy the column → Paste Special → Values to remove formulas and keep fixed values.

  MODE and MODE.MULT Functions in Excel Step 1: Prepare Your Data Create a dataset in Excel. Example: Step 2: Using the MODE.SNGL Function (Single Mode) Click on a cell where you want the mode result (e.g., B1 ). Step 3: Using the MODE.MULT Function (Multiple Modes) Select multiple cells (e.g., B2:B3 if you expect more than one mode). Enter the formula and Hold the Control + Shift key & hit the enter. Press Ctrl + Shift + Enter (for older versions) or just Enter (Excel 365/2019). Result: The function will return 3 in the first selected cell and 5 in the next cell (since both appear most frequently). Key Differences Between MODE and MODE.MULT Feature MODE.SNGL MODE.MULT Functionality Returns a single most frequent value Returns multiple modes if available Formula Type Standard formula Array formula Use Case When only one mode is needed When multiple modes exist in the dataset Error Handling Returns #N/A if no mode exists Returns #N/A if no mode exists Applicable Versions A...