Updated:
Original:

How to "Set It & Forget It" in Microsoft Excel

Learn how to "set it and forget it" in this Howcast Excel tutorial with expert Shir Moscovitz.

Transcript

Hi, my name is Shir and I'm the founder and CEO of shirconsulting.com where we focus on converting the existing data from your business into massive savings and extraordinary profits.
Today we're going to learn the basics of Excel. Let's get started.

How to "Set It and Forget It" just like Ron Popeil has instructed for us to do. Essentially, I always tell my clients that if you ever find yourself doing something manually more than once, you're doing it wrong. What that means is there's always a better way to have the computer automatically do all of the repetitive tasks for us if we set it up properly. So I'm going to take you through some examples of this.

Here we have a spreadsheet with a little bit of information on it. SO if I wanted to actually COPY some of this stuff and include it somewhere else, I'm going to click on the CELL I want. I'm going to hit CTRL+C for COPY and I'm going to do CTRL+V down here to PASTE it. Now I could do the same thing over and over again in this fashion but as you'll notice this is very manual and very repetitive. SO let's do it a faster way. I'm going to delete that. Let's take all this COLUMN at once, COPY it all and PASTE it all and as you see it's nicely put down here.

Let's take this concept one step further. We're going to take the same data but this time all of the data and we're going to COPY the entire thing at once over here. Here's another example of this concept at work. Let me just delete that for us. We can take the First Name and the Last Name and we can add a new COLUMN here for a Full Name. And if I wanted to I could manually go in and put in everyone's First Name and Last Name but as you can see this is manual. This is taking a long time. So instead I'm going to use a Formula which we'll go over in a later video called CONCATENATE. We're going to take the first element. We're going to add a space and then we're going to add the second element and we're going to get both of those combined. Once I have that I can COPY it all the way down and all the work is done for me automatically.

Another example is to evaluate each of these CELLS. Let's do a Pass/Fail COLUMN over here. And let's say I want to see if the amount donated is above $5,000 - it Passes and if it's below - it Fails. So I can manually look at it and say "Okay, this is not quite high enough so it Fails, this Passes." Right, this is a longer way to go about it. Instead let's make a Formula. We're going to use an IF Formula to evaluate this and see if it's greater than $5,000 and if it is let's write the word "Pass" and if it is not let's write the word "FAIL." Let's close that up.
So now not only is it determined for me immediately but it is also going to change based on the changing numbers. We can even go one step further and do a Conditional Formatting to allow us to see it very clearly. If I do a "Fail," it will show dark red text. If it does a "Pass," we will go to Conditional Formatting and do green for "Pass." Oops, here we go. And this way, we can see visually everything and it changes dynamically. This is part of the beauty of EXCEL.

One other example is to do a Macro. For any series of repetitive tasks that you have, let's go to the Developer tab which you have to enable using the Options. We'll go over that later. And I'm going to do Record Macro. I'm going to name it "Macro 2." Hit CTRL+Q as the shortcut. And write a little description here - "Repetitive Tasks." And I'm going to hit OK. And I'm going to click over here - everything I do is now recorded - I'm going to right click and INSERT a new COLUMN. I'm going to name this COLUMN - Full Name and I'm going to do another Formula called CONCATENATE. I'm going to choose C2 which is simply referencing the CELL to the right of it. I'm going to hit an ampersand (&) to add or CONCATENATE the space which is indicated here by the space between the two quotation marks. I'm going to add onto that the next bit which is the Last Name which is COLUMN D and row 2. I'm going to close that up. It's going to combine both elements. I'm going to expand this so we can see what's going on. And I'm going to take this and COPY it all the way down over here. You can expand it a little further. I will now Stop Recording. And I will delete this. Pretend like we never did it. And hit CTRL+Q on the keyboard to bring up that Macro. Great! And there you have it - Macros and learning how to "Set It and Forget It."

Popular Categories