Advanced Excel Module 2 Part 3 PDF

Advanced Excel Module 2 Part 3: Data Analysis Tools  Learning Objectives Participants Sort and filter Use advanced Group data Use data Use conditional should be able data filtering validation and formatting to:...

Advanced Excel Module 2 Part 3: Data Analysis Tools  Learning Objectives Participants Sort and filter Use advanced Group data Use data Use conditional should be able data filtering validation and formatting to: techniques drop-down list Data Analysis Data analysis is the process of inspecting, cleaning, transforming, and interpreting data to discover meaningful insights, patterns, and trends. It involves the application of various techniques and tools to make data more understandable and useful for decision-making. This Photo by Unknown Author is licensed under CC BY-ND  Data Analysis Data analysis in Excel refers to the process of using Microsoft Excel, a popular spreadsheet software, to perform various tasks related to analyzing and manipulating data. Excel provides a wide range of tools and functions that can be used for data analysis, making it a versatile tool for tasks such as data cleaning, summarization, visualization, and basic statistical analysis.  Module 3 Topics 1. Sorting and filtering data 2. Advanced filtering techniques (auto-filter, custom filter) 3. Subtotals and grouping data 4. Data validation and drop-down lists 5. Conditional formatting 6. What-if analysis and scenarios 7. Goal Seek and Solver 8. Data tables and data analysis add-ins 9. PivotTables and PivotCharts 10. Power Query for data extraction and transformation Sorting and Filtering SORTING Sorting is a feature in MS Excel that helps you organize data. – You can sort a text column in alphabetical order (A-Z or Z- A). – We can sort a numerical column from largest to smallest or smallest to largest. – We can also sort a date and time column from oldest to newest or newest to oldest.  Sorting One Column Multiple Columns Custom List Sort by Color One Column  One Column – Your Turn Sort Sales in Descending Order Multiple Columns  Multiple Columns – Your Turn Sort by Quarter, Country, and Sales in ascending order  Custom List Challenge: Sort the dataset by Priority (High, Normal, Low) Custom List Custom List  Sort by Color Challenge: Sort the dataset by Color Sort by Color FILTERING Filter in Excel is an essential tool that helps to display relevant data. – It eliminates the irrelevant entries temporarily from the view. – This tool filters data according to the criteria to help analyze the critical data points. This Photo by Unknown Author is licensed under CC BY-SA  Filtering One Column Multiple Columns Text, Number, and Date Filters Advanced Filter Filtering – One Column Filtering – Your Turn View records that are in USA and in Qtr4 as shown: Filtering – Multiple Columns Filtering – Remove Filter Filtering – Shortcut Number Filters Number Filters – Your Turn Filter Sales records that is greater than 5000 and less than 15000 Sort data by sales in ascending order Text Filters Date Filters  Date Filters – Your Turn Filter last month’s records  Advanced Filter And Criteria | Or Criteria | Formula as Criteria When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range (blue border below for illustration only) above your data set. Use the same column headers. Be sure there's at least one blank row between your Criteria range and data set. Advanced Filters – And Criteria Display the sales in the USA and in Qtr 4 Advanced Filters – Or Criteria Display the sales in the USA in Qtr 4 or in the UK in Qtr 1 Advanced Filters – Formula as Criteria Display the sales in the USA in Qtr 4 greater than $10.000 or in the UK in Qtr 1 Subtotals and grouping data  Subtotals and grouping data Worksheets with a lot of content can sometimes feel overwhelming and can even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet. To group rows or columns: To group rows or columns: To hide and show groups: To hide and show groups:  Creating Subtotals In our example, we'll use the Subtotal command with a T-shirt order form to determine how many T- shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group. Creating Subtotals Creating Subtotals Creating Subtotals - To view groups by level: When you create subtotals, your worksheet it is divided into different levels. You can switch among these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons to the left of the worksheet. In our example, we'll switch among all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight. Creating Subtotals - To view groups by level: Creating Subtotals - To remove subtotals: Groups and Subtotals - Your Turn Groups and Subtotals Video Data validation and drop- down lists Data Validation Data Validation – Create Validation Rule Data Validation – Input Message Data Validation – Error Alert Data Validation – Data Validation Result  Drop-down List Drop-down lists in Excel are helpful if you want to be sure that users select an item from a list, instead of typing their own values. Drop-down List - Create a Drop-down List Drop-down List - Create a Drop-down List Drop-down List - Allow Other Entries Drop-down List - Add/Remove Items Drop-down List - Add/Remove Items Drop-down List - Dynamic Drop-down List Drop-down List - Dynamic Drop-down List Drop-down List - Dependent Drop- down List Drop-down List - Dependent Drop- down List Drop-down List - Dependent Drop- down List  Challenge – Data Validation 1. In Challenge Sheet 1, apply data validation using drop down list 2. In the City column, add a drop-down list referring to the list of city in Sheet 2 3. The user selects a city from a drop-down list. Here is what we are trying to achieve: 4. Then, the user shall choose the barangay based from the city that he selected as shown in the figure below: Conditional formatting  Conditional formatting Let's say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Conditional formatting provides a way to visualize data and make worksheets easier to understand.  To create a conditional formatting rule: In our example, we have a worksheet containing sales data, and we'd like to see which salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so we'll create a conditional formatting rule for any cells containing a value higher than 4000. To create a conditional formatting rule: To create a conditional formatting rule: Conditional formatting presets Excel has several predefined styles—or presets—you can use to quickly apply conditional formatting to your data. They are grouped into three categories: To use preset conditional formatting: To use preset conditional formatting: To remove conditional formatting: To remove conditional formatting: Conditional formatting Video Conditional formatting - Challenge What-if analysis and scenarios What-If Analysis What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. Example: Assume you own a bookstore and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.  Create Different Scenarios But what if you sell 70% for the highest price? And what if you sell 80% for the highest price? Or 90%, or even 100%? Each different percentage is a different scenario. You can use the Scenario Manager to create these scenarios. Note: You can simply type in a different percentage into cell C4 to see the corresponding result of a scenario in cell D10. However, what-if analysis enables you to easily compare the results of different scenarios. Create Different Scenarios Create Different Scenarios Scenario Summary  Scenario Manager – Your Turn 1. You want to rent a house. There are some options for houses. We can consider these options as scenarios. Now, you have to decide which house to decide to save more money. 2. Create 2 scenarios based on the following information below: Then create a scenario summary. Scenario Name: House 2Scenario Name: House 3 Current Data Changing Values Changing Values Scenario Summary Goal Seek and Solver  Goal Seek If you know the result you want from a formula, use Goal Seek in Excel to find the input value that produces this formula result. Example: Use Goal Seek in Excel to find the grade on the fourth exam that produces a final grade of 70. Goal Seek  Goal Seek - Challenge Let's say you're planning an event and want to invite as many people as you can without exceeding a budget of $500. We can use Goal Seek to figure out how many people to invite. In our example below, cell B5 contains the formula =B2+B3*B4 to calculate the total cost of a room reservation, plus the cost per person. How many guests we can invite without spending more than $500?  Solver SOLVER allows you to change multiple variables to find an optimal solution. You can use solver for optimization problems to better allocate scarce resources, analyze and control risk, data visualization, forecasting, text mining, and predictive analytics for large datasets. Solver can be used for more complex “what-if-analysis” which Goal Seek can’t. Goal seek is restricted to changing one variable, whereas with solver you can have many variables. Load the Solver Add-in  Solver Example: Maximize Profit and Simulate scenarios Solver Select Data Tab -> Solver Set the objective Set the changing value Add the constraints Click Solve Solver - Results Data Tables  Data Tables Once you've entered information into your worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, and they'll also help organize your content and make your data easier to use. Excel includes several tools and predefined table styles, allowing you to create tables quickly and easily.  Challenge Format the table in Brown, Table Medium Style 12 Change the Menu Item font into Arial Narrow Sort the Price from Smallest to highest Add design to your Catering Invoice Header Data Tables  Analysis ToolPak Add-ins The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial, statistical and engineering data analysis. Analysis ToolPak Add-ins PivotTables and PivotCharts Pivot Tables Pivot Charts Power Query for data extraction and transformation Power Query for data extraction and transformation Power Query for data extraction and transformation  References https://www.excel-easy.com/search.html?cx=partner-pub- 1987061359047735%3A3934495699&cof=FORID%3A10& ie=UTF-8&q=Subtotals+and+grouping+data&sa=Go https://edu.gcfglobal.org/en/excel/# https://www.exceldemy.com/scenario-analysis-excel/ https://youtu.be/sLW3NbeGDy8 Thank you Maria Crystal E. Orozco, DIT Faculty, College of Computer Studies University of Perpetual Help System