In today's video I want to show you how you can overlay graphs in Excel to create a more detailed analysis. In our example I made a bar graph of U.S troops in Vietnam between 1964-1972; I then created a second bar graph that showed the percentage of total troops the U.S account for each year. In the second graph I removed the majority of the formatting and placed it on top of the bar graph. This allowed me to show two important statistics in an easy to understand visual. The hard part is then explaining the changes in the data.
Scatter plots are designed to help answer the question: "How much does variable x impact variable y?" They are not very difficult to make and can make drawing relationships much faster than simply looking at the numbers. In our example we ask the question "How much do absences impact ACT scores?" By creating the scatter plot in Excel we can quickly do a visual analysis to see if students with lower absences performed better on the ACT than students who had a higher number of absences.
If you have ever run a data query you have inevitably had to deal with duplicates at some point. In this video I will show you how to transpose data to turn column records into row (and vice versa) as well as how to remove duplicate data entries to make your Excel usage more efficient.
In our last part on creating a dashboard we look at how to clean everything up. Using the View tab we can eliminate the gridlines and hide certain parts of the menu in order to focus user attention on the dashboard. We will also look at the online version which is how we want our users to view the dashboard.
In this video we look at how we can add multiple graphs to our dashboard that tie back to the one drop-down list. This allows our users to simply change the test and then receive several pieces of visual data for analysis.
Part three of Creating a Dashboard is to create the visual analysis and tie it to the drop-down. What is great about setting up a dashboard using this method is that we can show a large amount of data in a smaller space and focus it to the needs of the viewer. The dashboard does work in online Office apps and can be updated with more information fairly easily.
In part two of this week long project we use =vlookup to pull the scores from our table into our dashboard using the drop--down list. This allows us to change the lookup value in one location and the current data to fill our score column. Populating the score column in this fashion allows us to focus on just one test at a time.
This week I am going to walk you through the process of creating a single page dashboard. The purpose of a dashboard is to save space and prevent users from having to scroll down constantly by using a drop-down list to change the data on the screen.
In this lesson I will show you how to create the drop-down list which triggers the rest of the changes on the screen. A drop-down list is also an excellent tool to ensure that users only type specific pieces of data into a cell. Any data that is not a part of the list cannot be entered into a cell with a list. This prevents users from entering data you don't want them to. For example if all I wanted entered in the cell were the codes S, H, AH or IB then I could use the list. This would prevent a user from entering Honors.
This video culminates the week we spend on charts in graphs in Excel. Today we look at how to add the visual we create in Excel to PowerPoint, how to ensure the data is updated, and how to animate the graphs. One of the reasons I prefer to present Excel data in PowerPoint is because it maintains all functionality and can be re-sized to ensure everyone in the room can clearly see it. Lastly placing graphs in PowerPoint allows us to animate then so that they appear as individual pieces of data and finally the whole.
In today's video I will show you how to use a double line graph to compare similar pieces of data over time. Double line graphs are a great way to visualize the difference when just looking at the numbers may not show how different the data really can be. The key to this type of graph is the use of data that is truly comparable.