Advanced Excel Course

  SCAN function Excel for Microsoft 365   Excel for Microsoft 365 for Mac   Excel for the web Here's your free trial of Copilot Pro Unlock now Scans an array by applying a  LAMBDA  to each value and returns an array that has each intermediate value.   Syntax =SCAN ([initial_value], array, lambda(accumulator, value, body)) The SCAN function syntax has the following arguments and parameters: [initial_value]       Sets the starting value for the accumulator. array       An array to be scanned. lambda       A LAMBDA that is called to reduce the array. The LAMBDA takes three parameters: accumulator     The value totaled up and returned as the final result. value      The current value from the array. body      The calculation applied to each element in the array. Remarks Use the initial_value ar...

Using COUNTIF and COUNTIFS Functions in Excel 📌 Overview - COUNTIF: Counts the number of cells that meet a single condition. - COUNTIFS: Counts the number of cells that meet multiple conditions. 🧮 1. COUNTIF Function 🔹 Syntax: COUNTIF(range, criteria) 🔹 Parameters: - range: The range of cells you want to apply the criteria to. - criteria: The condition that determines which cells to count. 🔹 Step-by-Step Example: Data (A1:A7): Apple Banana Apple Orange Apple Banana Grapes Goal: Count how many times 'Apple' appears. 🪜 Steps: 1. Click on the cell where you want the result (say B1). 2. Type the formula:    =COUNTIF(A1:A7, "Apple") 3. Press Enter. Result: 3 (since "Apple" appears 3 times) 🧮 2. COUNTIFS Function 🔹 Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) 🔹 Parameters: - criteria_range1: The first range to evaluate. - criteria1: The condition for the first range. ...

📊 Excel Functions: SUMIF and SUMIFS ✅ 1. SUMIF Function 🔧 Purpose: SUMIF is used to sum values based on a single condition . 📌 Syntax:  SUMIF (range, criteria, [sum_range]) 📋 Parameters: range – The range of cells to evaluate against the criteria. criteria – The condition that determines which cells to sum. sum_range (optional) – The actual cells to sum. If omitted, Excel sums the cells in the range . 🧾 Example: 🗂️ Data: A (Product) B (Sales) Apple 100 Banana 150 Apple 200 🎯 Goal: Sum the sales of "Apple". ✅ Formula: =SUMIF(A2:A4, "Apple", B2:B4) 📤 Result: 300 🪜 Step-by-Step: Type the function =SUMIF( . Select the range to evaluate (e.g., A2:A4 ). Type the condition (e.g., "Apple" ). Select the range to sum (e.g., B2:B4 ). Press Enter to get the result. ✅ 2. SUMIFS Function 🔧 Purpose: SUMIFS is used to sum values based on multiple conditions . 📌 Syntax:  SUMIFS (sum_range, criteria_range1, criteria1, [criteria_r...

  🧾 Objective: To filter a data table in Excel using a Slicer , and format it for easy and visual filtering by options like Market (e.g., Delhi, Goa, etc.) ✅ Step-by-Step Process Flow 🟩 Step 1: Prepare the Data Table 📌 Action: Select the entire dataset including headers. 📌 Example from Image: Select from: A1: E14 (Market, City Code, Date , Product Name, Sales QTY) 🛠 Convert to Table: Press Ctrl + T Ensure "My table has headers" is checked. Click OK Now, your data is in Excel Table format , which enables advanced features like slicers. 🟦 Step 2: Insert a Slicer 📌 Action: Go to the Insert tab in the Ribbon. In the Filters group, click Slicer . A dialog box appears with all table column names. ✅ Choose the Column for Filtering: Select “Market” (as shown in your image). Click OK 🖼️ A slicer object now appears with buttons for each market (e.g., ABC, Andhra, Delhi, Goa). 🟧 Step 3: Format the Slicer 🔄 Right-click the ...

  🔁 PROCESS FLOW TO ALIGN SHAPES & INSERTED PDF FILES IN EXCEL Step 1: Insert Shapes and PDF Objects 🔹 For Shapes: Go to Insert > Illustrations > Shapes . Select the shape (e.g., rectangle, circle, arrow) and draw it on your sheet. 🔹 For PDF Files: Go to Insert > Text > Object . In the dialog box: Click Create from File tab. Click Browse and select the PDF file. Tick Link to file or Display as icon if required. Click OK to insert. Step 2: Enable Alignment Tools Select all objects (hold Ctrl and click each item). Go to Shape Format / Picture Format / Object Format tab (appears after selecting objects). Use the Arrange group of tools: Align Group Rotate Bring Forward / Send Backward Step 3: Use Align Options After selecting multiple objects, go to Shape Format > Align to use: Option What It Does Align Left Aligns all objects to the leftmost Align Center         ...

  Arranging objects Note : You can pass this course and all graded assessments without access to the downloadable version of PowerPoint. How to complete this exercise To complete this exercise, you will need access to Microsoft PowerPoint.  Note: Keep in mind that if you are using free Office for the web or another version of the Microsoft PowerPoint application, some features covered in this exercise may not be available. Free Office for the web If you do not have access to Microsoft PowerPoint, you can use Free Office for the web. This version of Office allows you to view and edit files in apps like Word, Excel, and PowerPoint. This free service is available to anyone with a Microsoft account. Access Microsoft Account Log in to your Microsoft account. Type the email, phone number, or Skype sign-in that you use for other services (Outlook, Office, etc.), then select Next. If you don't have a Microsoft account, you can select No account? Create one! Let's g...