Over time I have found that the following five formulas are some of the most useful for teachers when working with data and spreadsheets. Each of these formulas can automate something teachers use often or provide them with a more detailed analysis of data.
1) Averageif – allows teachers to create a condition to a range of data that is going to be averaged. Example: Average on the scores that fall between 60-69.9
2) Countif – allows teachers to count a range of data based on a condition. Example: count only those students who made basic.
3) Concatenate – combines data from cells into one cell. Example: you could add @student.rcschools.net to end of student usernames to quickly generate their email addresses.
4) Proper – allows teachers to change the text to first initial uppercase and the rest of the name lowercase. Example: Export from iNow makes all names capital and you want them to be proper. JASON SLOAN becomes Jason Sloan.
5) If/Then Statements – allows teachers to make conditional statements based on data in a cell. Example: If you wanted to have all scores between 80-85 be proficient you could use an if then statement to automate this process.
In this video I show you how to use the Alignment section in the Home tab. The key features here are wrapping text in a cell, text alignment, and text orientation. Excel allows you to determine where text will be positioned in a cell both vertically and horizontally and also the orientation of the text. Text can be aligned horizontally at 0 degrees or vertically at 180 degrees in the Alignment section. Lastly, it is in this section of the Home tab that you will find how to merge cells.
Welcome back to school! In this blog I want to show you how you can import external data from a webpage. This is a simple process that can save you time when trying to sets of data from the internet. Importing the data is different from a copy and paste because when you import the data Excel creates a connection that allows the data to be updated when you open the spreadsheet.
In each Office product there exists a quick access toolbar in the upper left hand corner that is customizable. In this video I will show you how the toolbar works and how to customize it, making common commands easily accessible.
Having email accounts for all students in a school is a very handy feature in Office 365. However sending stuff in mass to them can be tricky if you try to do it in your normal methods. In this video we take the data we built in Excel yesterday and used it to fill a word document with student specific information and then mail it to them. Using the mail merge function in Word we wrote an email and then populated it with our Excel data and mailed it easily. This is a great method when trying to send emails to hundreds or even thousands of people.
In this video I will show you how we can create simple usernames by concatenating users first initial, last name, and 2 digit birth date. To do this we combine concatenate with a second formula that will pull just the first character in a cell and combine it with other data. In the next blog I will take the information and build it into a mail merge using Word and Excel.
The Watch Window is a handy tool that allows you to monitor a cell or a range of cells as you are working in Excel. The watch window will stay on top and constantly update as you work. It is located in under the Formulas Tab and can be adjusted in it's height and width. when you are working with large amounts of data and want to be able to see something specific it is a good tool to work with.
A Pareto Analysis is an analytical technique used to find what few causes lead to the greatest number of problems. The analysis is based on the "80/20 Rule" which states that 80% of the problems are caused by 20% of the possible causes. In our example I will show you how to use it to determine which areas of the TEA M Rubric a faculty see as their greatest weakness. The 20% of the indicators they see as their greatest weakness would then be the areas to focus your teacher training on. For further study you can go to mindtools.com
Sparklines are charts contained within a single cell for a row of data. They create a unique visual analysis for each row of data rather than for the data as a whole. They can be formatted and re-sized to give that quick visual analysis I have mentioned over and over as part of your data analysis model. So in this video I will show you how to create, format, and use them to improve your data analysis .
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.