If/then statements are a handy tool to use in Excel because you can use them to increase your efficiency and automate simple tasks. The if statement works to establish a condition that when met will return a value or even perform a mathematical calculation. In the video below, I give you and example of each of these methods. If/then statements can also be nested to provide greater complexity.
People always say work smarter not harder and I would tend to agree with that philosophy. Today's video is on using the formula subtotal which will make your life easier when working with data in tables. Subtotal is designed to perform the action you choose on only the data that is shown on the screen. So when you filter out a particular group the formula will update with the remaining data. For example if you have a spreadsheet of students and their grades you can filter out the male students and see the average of just the female students. So subtotal has many useful applications when working with data in Excel.
Averageifs is a great formula to have in your toolbox because of its versatility. The formula allows you to drill down using criteria such as greater than or less than. One important aspect is understanding when you need to use Averageif or Averageifs. In this video I will show you how to use averageifs and when to use the different formulas.
Welcome to the world of conditions, they can be your best friend in analyzing data. Conditions allow us to quickly and effectively drill down without having to do a lot of work. In this post we are going to start with countifs, which allows you to count based on a set of conditions such as greater than or even text. I use it constantly when I want to know how many of something based on say gender and race. For example, if you wanted to know how many Hispanic females were in a group of 600 you find out using countifs.
Building a database requires a lot of work, so testing to make sure everything work is pretty important. Your time is also valuable, so spending a lot of time typing stuff in just to test formats and formulas is not very efficient. Using the formula =rand and its variations can significantly speed up the time you take checking your work. In this video I will show you how to quickly ensure your work is correct.
Concatenating with partial characters is useful if you want to create usernames or just shorten the amount of records you are viewing by using last name & first initial. It is a handy little formula to have on hand even though you may not use it that often.
Adding a 0 to the beginning of a number string in Excel can be tricky because you have to force Excel to actually see the zero. This is due to the fact that a leading zero is not necessary unless of course you want it to be. In this video I will show you how to force Excel to show the leading zero and the combine it with a randomly generated student id to create a 9 digit password that begins with 0.
Text to columns serves as the functional opposite to concatenate. It is used to take data from a cell and split it into multiple cells. There is no formula for text to columns, instead you highlight the cells you want to split, then click on the Data Tab and got through the steps.
Concatenate is a must have tool for any excel user. It combines data from multiple cells into one and can also be used to add additional text to a cell. If you have ever run a report from any data source you know that different sources will export names out differently. So concatenate allows us to quickly modify those names to look how we want them.
The Week of Text
This week each blog post will focus on how to work with names in Excel. This first video will show you how to change the case of text in Excel. This simply but helpful formula is a must know because when you export data from programs it does not come out nice and clean, instead you will have to clean it up, and these three formulas will help you with that process.