Advanced Excel Course

1- Trim Process Flow to Use the TRIM Function in Excel 1. Open Your Workbook Open the Excel file where you want to clean text data. 2. Identify the Data to Trim Locate the cell or column containing the text that has extra spaces. Example: Column A contains names with irregular spaces. 3. Insert a Helper Column Insert a new column next to the text column (if needed). Example: Add a column titled "Trimmed Text" next to Column A. 4. Enter the TRIM Formula In the first cell of the helper column (e.g., B2), enter the formula: =TRIM(A2) Replace A2 with the reference to the cell you want to clean. 5. Apply the Formula Drag the fill handle (small square at the bottom-right corner of the selected cell) down to apply the formula to other rows. 6. Replace the Original Data (Optional) If you want to replace the original data with the cleaned version: Copy the cells with the TRIM formula. Right-click on the original column and choose Paste Special > Values . 7. Verify the Result Chec...

Process Flow: Convert Text formatted number in number value  Method 1: Use Shortcut Keys Select Range: Highlight the cells containing numbers stored as text. Shortcut Key Sequence: Type the shortcut  Alt + D + E + F . Result: The text is converted to numeric values. Method 2: Use Paste Special and Multiply Preparation: Select a blank cell that does not have this issue. Type the number 1 into the blank cell and press Enter . Copy the Value: Press Ctrl + C to copy the cell containing the number 1 . Select the Range: Highlight the cells that have numbers stored as text. Apply Paste Special: Go to the Home tab. Click Paste > Paste Special . In the Paste Special dialog box, select Multiply , then click OK Result:  Excel multiplies each cell by 1 , effectively converting the text to numbers. Method 3: Use Value Number Function Add a New Column: Insert a new column adjacent to the data range. Apply Formula: In the first cell of the new column, type the formula =VALUE(A1) ...

  Conditional Formatting in Outlook:  Automatically change incoming message colors and fonts based on sender, subject, or recipients Conditional formatting is a way to make messages that meet defined conditions stand out in the message list by using color, fonts, and styles. You specify conditions that an incoming message should meet, such as a sender’s name or email address, and then conditional formatting is applied only to those messages. For example, a conditional rule can specify that all messages sent from your manager appear in red text in the message list. To make further changes to the body of your email messages,  change the default font or text color for email messages  . Note:   In Microsoft Outlook 2007, the  Organize  feature enabled you to create conditional text formatting rules for email messages. In Microsoft Outlook 2010 and later, conditional text formatting is now accessed in the  Advanced View Settings  dialog box. ...

  First, please save your Excel as a .csv file. Outlook can import comma separated value (.csv) files, but not workbooks with multiple sheets. ln your workbook, click the worksheet with the calendar information you want to import. Click  File  >  Save As . Choose where to save your file. In the  Save as type  box, choose  CSV (Comma delimited) (*.csv) , and click  Save . Excel tells you “The selected file type does not contain workbooks that contain multiple sheets.” This refers to a limitation of the CSV file; nothing will happen to your original workbook (the .xlsx file). Click  OK . Excel tells you “Some features in your workbook might be lost if you save it as CSV (Comma delimited).” This refers only to limitations of the CSV file and can be ignored.  Click  Yes  to have Excel save the current worksheet as a CSV file. The original workbook (the .xlsx file) closes.  Close the CSV file. You're done with Excel. Now, yo...

✅ XLOOKUP : Pull data from the "Data sheet" for multiple employees based on Employee Code No across 30 date columns . 🛠️ XLOOKUP Syntax Refresher:  XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found] , [match_mode] , [search_mode] ) 🧭 Step-by-Step Process Flow: 1. Prepare Your Sheets Sheet 1 (Main Sheet): This is where you're writing the XLOOKUP function (like the image shown). Sheet 2 ("Data sheet"): Contains employee data with Employee Code No in column B and corresponding values from column C to AF (30 columns). 2. Identify Key Columns B3 in Main Sheet: Contains Employee Code No (used as the lookup_value ). 'Data sheet'!B:B : Column B in the Data sheet (lookup_array). 'Data sheet'!C:AF : Columns C to AF hold the data you want to return (30 columns). 3. Set Up First XLOOKUP Formula In cell D3 on the Main Sheet: =XLOOKUP(B3, 'Data sheet'!B:B, 'Data sheet'!C:C) This pulls the value from th...

  The Show Report Filter Pages Feature The Show Report Filter Pages feature: Creates a copy of an existing pivot table for each unique item in a field. The new pivot tables are created on individual worksheets. Each sheet is renamed to match the item name. A filter is applied to the field in the Filters Area of each pivot table for the item. All this is done with a click of a button.  Your field can have 5 or 500 unique items.   Show Report Filter Pages will create a sheet for each item and replicate the pivot table report . Here are the steps to use Show Report Filter Pages: Step 1 – Add the field to the Filters Area The first step is to create a pivot table.  Then add the field that contains the list of items to the Filters Area of the pivot table. In this example we want to create one pivot table for each salesperson in the organization.  So, we are going to add the Salesperson field to the Filters Area of the pivot table. Step 2 – Run the Show Report Filter ...

  Add or remove add-ins in Excel       Add a COM add-in Click the  File  tab, click  Options , and then click the  Add-Ins  category. In the  Manage  box, click  COM Add-ins , and then click  Go . The  COM Add-Ins  dialog box appears. In the  Add-Ins available  box, select the check box next to the add-in that you want to install, and then click  OK . Tip     If the add-in that you want to use is not listed in the  Add-Ins available  box, click  Add , and then locate the add-in. Remove a COM add-in Click the  File  tab, click  Options , and then click the  Add-Ins  category. In the  Manage  box, click  COM Add-ins , and then click  Go . The  COM Add-Ins  dialog box appears. In the  Add-Ins available  box, clear the check box next to the add-in that you want to remove, and then click  OK . Note ...

✅ Excel Paste Special – Summary with Examples 1. Access Paste Special Copy data → Ctrl + C Right-click on destination cell → choose Paste Special Or press Ctrl + Alt + V to open Paste Special dialog 2. Paste Options – With Examples Option What It Does Example All Pastes formulas, formatting, comments Cell A1 (=B1+C1, green fill) → full paste Formulas Pastes only the formula A1 (=B1+C1) → pasted without green color Values Pastes just the result A1 (=2+2) → pastes 4 Formats Only pastes formatting Green cell → destination becomes green Comments/Notes Pastes just notes Adds comment bubble only Validation Copies dropdowns/rules Source has dropdown → pasted cell does too All using Source Theme Retains font/colors...