Services4People

  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...