Summary All Sessions PDF

Mobina Razmara – Data Management Session 1: 1. Open Excel: Click on Excel and choose "Blank Workbook." 2. Columns/Rows/Cell: Columns are vertical, rows are horizontal, and cells are the intersections (e.g., A1). 3. Change Name of Cell:...

Mobina Razmara – Data Management Session 1: 1. Open Excel: Click on Excel and choose "Blank Workbook." 2. Columns/Rows/Cell: Columns are vertical, rows are horizontal, and cells are the intersections (e.g., A1). 3. Change Name of Cell: Click the cell, go to the Name Box on the left of the formula bar, and type the new name. 4. Type of Data (Number, Date, Currency, etc.): o Windows: Select the cell, right-click, choose Format Cells, and pick the desired data type. o Mac: In older versions, the Format Cells option may not appear with a right-click. You can access it via the top menu (Format > Cells). o For both Windows and Mac, you can also use the Ribbon: Select the cell(s). / Go to the Home tab. / In the Number group, use the dropdown to select data types (e.g., General, Number, Currency, Date). 5. Automatic in Excel: o AutoFill: Drag the bottom-right corner of a selected cell to fill formulas or values across other cells. 6. Copy/Paste Cell: o Windows: Use Ctrl + C (copy) and Ctrl + V (paste). o Mac: Use Cmd + C (copy) and Cmd + V (paste). 7. Save Excel File/Template: o Windows: Click File → Save As, choose the location, and select the file type (Excel Workbook or Template). o Mac: The process is similar but the File menu might have slight visual differences in older versions. 8. Set Data Validation: 1. Select the cell(s) where you want to apply data validation. 2. Go to the Data tab. 3. In the Data Tools group, click Data Validation. 4. In the dialog box that appears: § Allow: Choose the type of data you want to allow (e.g., Whole Number, Decimal, List, Date, Time, Text Length, or Custom). § Data: Set specific conditions based on the type of data selected (e.g., between, greater than, less than). § Source: If you're validating based on a list, enter the values (separated by commas) or select the range containing the list. 5. Click OK to apply the validation. o Remove Data Validation: 1. Select the cell(s) where you want to remove validation. 2. Go to the Data tab, click Data Validation. 3. In the dialog box, click Clear All, and then click OK. Mobina Razmara – Data Management Session 2: 1. Import Data (Text, CSV, Web): o Windows: Go to the Data tab → Get Data → Choose options like "From Text/CSV" or "From Web." Power Query is fully integrated. o Mac: In the Data tab, select Get Data for options like "Text/CSV" or "Web," but Web import may not be available in older versions, and Power Query integration is limited. Workarounds like importing from saved HTML files may be needed. P.s: Import Data (Web): Power Query and Web import are more robust on Windows; Mac has fewer options may lack direct Web import in older versions.or 2. Conditional Formatting: o Windows: Offers more granular control over customizing Data Bars and Icon Sets, including fine-tuning minimum/maximum values and having more icon options. o Mac: Fewer preset icon choices, and customization (e.g., adjusting minimum/maximum in Data Bars) is less flexible compared to Windows. 3. Sort/Filter/Group/Freeze/Find/Replace: Sort: 1. Select the data you want to sort. 2. Go to the Data tab. 3. Click Sort (you can choose Sort Ascending or Sort Descending based on your preference). 4. For custom sorting, click Sort → In the dialog box, choose the column, sort by Values, Cell Color, Font Color, or Custom List. Filter: 1. Select the data range or table you want to filter. 2. Go to the Data tab. 3. Click Filter (you will see drop-down arrows in your header row). 4. Click the drop-down arrow for the column you want to filter and choose the criteria (e.g., by text, number, or date). Group: 1. Select the rows or columns you want to group. 2. Go to the Data tab. 3. Click Group to organize data into collapsible sections. 4. To ungroup, select the grouped rows/columns, then click Ungroup. Freeze Panes: 1. Go to the View tab. 2. Click Freeze Panes. 3. Choose one of the following options: § Freeze Panes: Locks rows above and columns to the left of the selected cell. § Freeze Top Row: Locks the first row in place. § Freeze First Column: Locks the first column in place. 4. To unfreeze, go back to the View tab and click Unfreeze Panes. Find/Replace: Windows: 1. Press Ctrl + F to open the Find dialog box. 2. Type the text or value you want to find and press Enter. 3. For Replace, press Ctrl + H, enter the value you want to replace, and the replacement value. Mac: 4. Press Cmd + F to open the Find dialog box. 5. Type the text or value to find and press Enter. 6. For Replace, press Cmd + H and follow the same steps as in Windows. 4. Merge/Split Cells: Merge Cells: 1. Select the cells you want to merge (for example, A1). 2. Go to the Home tab. 3. In the Alignment group, click the Merge & Center button (you can also click the dropdown arrow next to it for other options like Merge Across, Merge Cells, or Unmerge Cells): § Merge & Center: Combines the selected cells and centers the content. § Merge Across: Merges the selected cells across rows without centering. § Merge Cells: Combines the selected cells without centering. § Unmerge Cells: Splits merged cells back into individual cells. Mobina Razmara – Data Management Session 3 : 1. What-if analysist: Goal Seek: o Windows and Mac: Navigate to Data > What-If Analysis > Goal Seek, input the cell with the formula, the target value, and the cell to change. Purpose: Use it to find the input value required to achieve a specific result in a formula. For example, find the sales needed to hit a target profit. Data Table: o Windows and Mac: Go to Data > What-If Analysis > Data Table, and select a cell with formulas affected by your variables. Set row and column inputs for one- or two-variable tables. Purpose: Use this tool to see how changes in one or two variables affect the outcome of a formula (e.g., how different interest rates affect loan payments). Scenario Manager: o Windows and Mac: Navigate to Data > What-If Analysis > Scenario Manager, create different scenarios with varying inputs, and generate a summary of results. Purpose: Create and compare different scenarios (sets of input values) to see how they affect outcomes (e.g., best-case, worst-case scenarios for a budget). 2. Consolidate: o Windows and Mac: 1. Navigate to Data > Consolidate. 2. Choose a Function (e.g., Sum, Average, etc.) from the dropdown. 3. Add References: Select and add the ranges you want to consolidate from different sheets or workbooks. 4. Check "Top row" or "Left column" if your data has labels. 5. Click OK to consolidate and see the results. Purpose: Use the Consolidate tool to combine data from multiple ranges (sheets or workbooks) into a single summary, often for summarizing totals, averages, or other functions. Mobina Razmara – Data Management Session 4 : 1. Solver: Enable Solver (if not already enabled): o Windows: Go to File > Options > Add-ins, select Solver Add-in, and click Go. Check the box for Solver and click OK. o Mac: Go to Tools > Excel Add-ins, check Solver Add-in, and click OK. Using Solver: o Windows and Mac: 1. Go to Data > Solver. 2. Set the objective cell (the target you want to maximize, minimize, or set to a specific value). 3. Define the variable cells (the cells Solver can change to achieve the objective). 4. Add any necessary constraints (e.g., budget limits, resource availability). 5. Click Solve to find the optimal solution. Purpose: Solver is used to find the optimal solution for a problem by changing certain variables, subject to constraints (e.g., maximizing profits or minimizing costs). 2. Pivot Table: 1. Insert Pivot Table: o Windows and Mac: 1. Select your data range. 2. Go to Insert > PivotTable. 3. Choose whether to place the Pivot Table on a new worksheet or the current one. 2. Build the Pivot Table: o In the PivotTable Field List: 1. Drag and drop fields into Rows, Columns, Values, and Filters areas. 2. For example, put "Product" in Rows and "Sales" in Values to sum sales by product. 3. Customize: o Filter data, change value settings (e.g., sum, average), and sort results as needed. Mobina Razmara – Data Management Session 5: What-If Analysis in Excel 1. IF, SUMIF, COUNT, COUNTA, COUNTIF, AVERAGEIF Functions: § IF: Use when you need to test a condition and return different results based on whether it's true or false. Example: Pass or Fail based on a score. § SUMIF: Use to sum values that meet a specific condition. Example: Add up sales for "Red" products. § COUNT: counts only numbers. § COUNTA: counts everything that isn’t blank (including numbers, text, and special characters). § COUNTIF: Use to count how many cells meet a condition. Example: Count how many students scored above 50. § AVERAGEIF: Use to calculate the average of values that meet a condition. Example: Find the average score of students who passed. 2. Data Validation (Creating Drop-Down Lists): The process is nearly identical on both platforms: 1. Select cells → Go to the Data tab → Click Data Validation. 2. Set the Allow option to "List" and provide the list items. Slight differences: o On older Mac versions, the Data Validation option might not appear directly in the toolbar. Instead, access it via the Tools or Data menus. o On Windows, the Data Validation dialog box often pops up more consistently and can sometimes have a different layout. 3. Formatting Cells: Both platforms allow you to format cells (e.g., changing data types to Number, Date, Currency). Differences: o Windows: Right-click on a cell → Select Format Cells directly. o Mac: In older versions, you may need to access formatting through the top Format menu or Ribbon instead of right-clicking. 4. AutoFill: Windows and Mac: Drag the fill handle (small square at the bottom-right corner of a selected cell) to apply formulas or copy data. 5. Keyboard Shortcuts: Windows: o Copy: Ctrl + C o Paste: Ctrl + V o AutoSum: Alt + = Mac: o Copy: Cmd + C o Paste: Cmd + V o AutoSum: Cmd + Shift + T 6. File Saving: Windows and Mac: Windows: Go to File → Save As and select the file type (e.g., Workbook or Template). Mac: The File menu layout may look slightly different, especially in older Excel versions. 7. Visual Differences in Ribbon/Menu: Newer versions of Excel on Mac and Windows have almost identical Ribbons. In older versions, Mac Excel had fewer features or slightly different layouts (e.g., no File tab—users needed to rely on the Mac menu bar instead). e Mac menu bar instead). Mobina Razmara – Data Management Session 6: Date Functions and Lookup Tools in Excel 1. Basic Date Functions: o TODAY(): Displays the current date. o NOW(): Displays the current date and time. 2. Extracting Date Components: o DAY(date), MONTH(date), YEAR(date): Extract day, month, or year from a date. 3. Creating Dates: o DATE(year, month, day): Combine year, month, and day into a date. 4. Adjusting Dates: o Add/Subtract Days: Perform arithmetic on date cells. o EDATE: Add/Subtract months (e.g., =EDATE(date, 5) adds 5 months). o NETWORKDAYS: Calculate workdays between two dates, excluding weekends and holidays. o NETWORKDAYS.INTL: Specify custom weekends (e.g., Thursday-Friday) for calculating workdays. 5. INDEX and MATCH Functions: o INDEX: Returns a value from a specific row and column in a range. o MATCH: Finds the position of a value in a row/column. o Combined Use: Look up values in large datasets (e.g., Ohio sales for a specific month). 6. VLOOKUP and HLOOKUP Functions: o VLOOKUP: Searches for data in a vertical table. § Example: =VLOOKUP(lookup_value, table_array, col_index, FALSE). o HLOOKUP: Searches for data in a horizontal table. § Example: Use after transposing a table using Paste Special > Transpose. Mobina Razmara – Data Management Session 8 & 9 1. Accessing the Developer Tab: Windows: Go to File → Options → Customize Ribbon, then enable the Developer tab. Mac: Go to Excel → Preferences → Ribbon & Toolbar, then check the Developer tab. 2. Recording a Macro: The process is similar: o Windows: Developer → Record Macro. o Mac: Tools → Macros → Record Macro or via the Developer tab (if enabled). 3. Running a Macro: Windows: Go to Developer → Macros, select the macro, and click Run. Mac: Same steps as Windows, but the menu might look slightly different. Alternatively, use Tools → Macros → Run Macro. 4. Visual Basic for Applications (VBA): Windows: Access VBA with Alt + F11 or through Developer → Visual Basic. Mac: Access VBA via Tools → Macros → Visual Basic Editor or Developer → Visual Basic (if enabled). 5. Shortcut Keys: Shortcut assignment works similarly, but you might need to use Cmd instead of Ctrl on Mac for shortcuts (e.g., Cmd + Option + a key). 6. Saving Macro-Enabled Workbooks: The file format (.xlsm) is the same for both platforms: o Save via File → Save As → Excel Macro-Enabled Workbook. The interface to save might differ slightly. 7. Macro Security Settings: Windows: Adjust via File → Options → Trust Center → Trust Center Settings → Macro Settings. Mac: Adjust via Excel → Preferences → Security & Privacy → Macro Settings.