Have you ever tried to get rid of a table but keep the data and did not know where to start? I have on a few occasions. So today I will show you how to take a table disappear while keeping the all important data. You may not use this often but if you ever need to, now you will know where to look.
0 Comments
If you have every download data from TVAAS then you will want to watch this video. What I am going to show you is how to take multiple rows of data for the same person and transpose (move from vertical to horizontal or vice versa) them so that you have a single row with multiple columns of data. Imagine downloading data and having 10 rows for each student and there are 500 students. You could copy and paste (transpose) and take forever doing it. Or, you could use this formula to automate the process. I got this formula from mrexcel.com where a user named Domenic posted it, and it is pure genius. The formula will take the data and tranpose it for you, taking what would have been a long day's work and turning it into about 1020 min of work.
Today's video is a follow up to yesterday's blog on how to find the mean, median, and mode in Excel. In this video we look at how to find the distribution of scores and how to graph them for an easy visual analysis of the data.
In today's blog we do a math lesson and look at the difference in these three terms, how to calculate them in Excel, and when to use one over another. My information in the video on the terms themselves is from Laerd Statistics, and they have some excellent explanations of which measurement to use. I worked simply to show you how to write the formulas to find the data in Excel.
Macros are recorded tasks in Excel that can be run to do repetitive tasks. In our video we are going to create a macro that when we use the shortcut keys will automatically sort our spreadsheet by Last Name, First Name. This is very helpful when we have students that we need to add to the database. Instead of finding the correct row and then inserting a row and their data, we can put it at the bottom and then run the macro and it will do the task for us.
Today's video shows you how to freeze panes in Excel, the different ways to freeze them, and when you should choose each type. Freeze panes is a must for working with large amounts of data because it allows you to freeze rows and/or columns so they can always be seen.
Today our lesson begins with a problem, with some scores missing from our database the growth columns are no longer calculating. This poses a problem because we need to know the class growth average. To solve our problem we use the formula =iferror and nest our growth formula inside it. Doing so allows us to say if there is an error in the formula, then leave the cell blank. Then our growth column will average even if some scores are missing. Our problem is then solved.
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 19641972; 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.

Jason SloanInstructional Technology Coach at Oakland High School and general Tech Geek Archives
January 2018
