Tag: Functions

(click here for Part 1)

Paste Formulas

Another way that you can autofill a formula across multiple cells is to Paste Formulas.

Here we have a projected amount of sales of $5,000 per day along with the actual total sales for that day. We want to know the difference. Here are the steps:

• Copy cell G2
• Select G3:G20
• Right click
• Paste Special
• Paste Formulas

We now have the total difference between the two cells for each day. Some days, we didn’t hit our mark, while some days we did. Let’s clean up the formatting to make this easier to read.

To do this, select all cells in the column,

• Format Cells
• Currency
• Two decimal points
• Show negative numbers in red with parentheses

Every negative value is in parentheses and represents a day when we did not meet our expected goal.

The Autofill button is one of the simplest, yet greatest tools in Excel. There are a variety of different ways to use the autofill feature depending on how you are using your data. Here we’ll look at the most common uses.

Increase Each Cell by One Number

Here we’re filling out the day of the month in March, starting with one. Let’s drag the autofill down and add a second column to note the department we’re in:

We’re eventually going to make a pivot table with data from every department, including engineering, R & D, and others, but for now we’re compiling data from Sales. Dragging the autofill cross hairs down column B worked to fill in “Sales”, but its giving us the number “1” for every row in column A. That’s a simply fix:

Excel can pick up a variety of different patterns when you want to use autofill to your advantage. For number sequencing, all you have to do is type in a pattern that will increase by one number in each cell, as shown above.

Next, select both B1 and B2 and drag the autofill handle down.

Now that it recognized our sequence, it will continue adding by 1.

…Ok, I know you’re not that impressed by Autofill just yet, but we’re just getting started.

Autofill Dates

Here we have almost the same example. We are adding in dates from March along with the department. Same rules, right? Nope.

Here we drag the autofill button down and by default, it increases by one day in each cell because Excel knows this is a date.

Let’s make it even easier:

By double clicking the autofill button on B1, it will autofill all the way down to where it still finds a value in Column C

This feature is really fun to use across thousands of rows when you have formulas, QA checks, or other types of data that need to be auto filled. The caveat, however, is that if you have a very large data set and there is a GAP in the column next to your autofill column, the formula will stop. Keep this in mind when using large data sets.

(cont. in part 2)