Format Painter: quickly copy formats

In Excel, if you want to copy the format of a group of cells into another, you can use the Copy command and then make a Paste and selecting, on the Paste Options, the Formatting one. This is how most people do. But Excel has a function, called Format Painter, designed for this task. Let’s see an example. We have this table of data on our worksheet:

It' has some font style, color, size and borders applied. Now we make a new table for the 2nd Quarter Sales, like this:

We want to apply the same format to this table that we had for the 1st Quarter Sales one. Select the complete table (in this case cells B2:C6) and on your Home tab, on your Clipboard group, click on the Format Painter button. You will see your cursor change to a plus and a brush icon.

Now click on your B8 cell so that Excel applies the formatting from cells B2:C6 on this new table. This will be the result:

If you have more than one place where you want to copy the formatting, instead of clicking once on the Format Painter button, click it twice. On this example, we want to copy the formatting from cells B2:C6 into the table starting on cells B8 and B14.

So, select the cells B2:C6, click twice on the Format Painter button and click once on each cell (B8 and B14) so that Excel applies the formatting to this two tables. When you finish, just click ESC key to close the Format Painter copy. The result will be this:

Array Formulas

Excel has a powerful feature called Array Formulas. Let’s see an example on how they can be used to improve our worksheets. We have the following data:

We have a list of articles, with it’s unit price and quantity that was sold. If we want to calculate the total sales value, we need to multiply each unit price for the quantity and then sum all of the values. We could add a column to the right of the Qtd column where we put a formula on cell E3 this formula =C3*D3 and then copy down until row 8. Then we need to sum all of the values on column E with a formula like =SUM(E3:E8) to get the sales total, right? Your sheet would look like this:

As you can see, we needed to make the calculation in two steps: first calculate the sales value for each article and then sum all of the sales values. What if we could do this with a single formula? Wouldn’t it be better? With array formulas this is possible. Let’s see how. Let’s get back to our original table, without the Sales Value column. We want to multiply the value of each row on column C by the value on that row on column D. If we put a formula like =SUM(C3:C8*D3*D8) on cell D10 and press Enter, you will get a #VALUE error:

Go back to the formula on cell D10 (press F2 to edit) and, instead of pressing Enter, press Ctrl+Shift+Enter at the same time. Your formula will look like this now:

Excel automatically adds the braces ({ }) to identify that this is an array formula. Don’t put the braces yourself because Excel will not interpret like an array formula this way! You always have to Ctrl+Shift+Enter each time you edit your array formula and Excel will add the braces for you. Now you just need to format the cell D10 value as currency.

Let’s see another example of array formulas use. Imagine that we wanted to see what a 5% increase on the quantity of articles sold would do for our total sales value. So wee need to multiply the Qtd column by 1.05 and then multiply that for the unit price value and sum all of the values for each article. Again, we have multiple operations and we want to make them on a single formula. We will use an array formula for that also, like this:

We start by multiplying each value on column D by the value on cell D11 (the 5% increase), then multiply that value for the value of column C and sum all of the results for each row.

As you can see by this two simple examples, Excel is able to handle multiple operations on a single formula. This will help you simplify your worksheets calculations, avoid using additional columns or rows to perform intermediate calculations, and improve your worksheets performance.

Using Goal Seek

This Excel functionality is very useful when you want to change some input values that give you a final value on a formula. Here’s an example used on our Financial Functions article. We had this formula where we calculate a monthly payment for a certain amount of value, with a certain loan rate, for a certain term:

Using the PMT() function, it’s easy to calculate the monthly payment. But we could need to do the opposite. For instance, we know that we can only afford a monthly payment of \$300. With the same loan rate and the same loan term, how much money can we ask for? That’s when we can use Goal Seek to calculate the loan amount. Just go to your Data tab, then on the Data Tools group click on the What-If Analysis button and select Goal Seek option. A Goal Seek dialog box opens, like this:

Now we just need to fill the dialog box fields. On the “Set cell” field, we need to select our input cell, in this case is cell B5. The “To value” field is where we are going to put the value our input value, in this case, we want to calculate the loan amount to give us a monthly payment if \$300, so fill this with 300. The “By changing cell” field, is where we specify what is the cell that is going to change to give us our final value that we want to calculate, is this case is the loan amount so select cell B1. Just click OK button and we are going to have this result:

As you can see on cell B1, for us to afford a monthly payment of \$300, with this loan conditions, we can only ask for \$18,627.83. As you can see, this is very easy to use and is a very handy function to have to help us find some particular values.

Named Ranges

You can use named ranges on your sheets for lots of things. We will see some good examples here on how you can use them. You can name a cell or a range of cells. You can use the name box near the formula bar to insert names on your sheet, just select the cell(s) and type the name you want on the name box, like this:

This will give cell B5 the name “MonthPayment”. Names cannot have blank spaces, special characters, and operators, and can be up to 255 characters long. In this case, to avoid using spaces, I joined the two words but I could use Month_Payment for instance. Now I can just type like this on a cell:

=MonthPayment

and this will display the value I’ve on cell B5. You can use this named ranges to simplify your formulas. For instance, you can now build a formula like this, if you also give cell B3 the name “Term”:

=MonthPayment*Term

As it was explained on the Cascading Validation Lists article, you can also create named ranges using the Name Manager dialog box.

You can use the Named Ranges to navigate through your sheets. Just select the name on the names box on your formula bar and you will jump to corresponding cell(s). This is very useful if you have big and complex worksheets/workbooks.

As said before, you can name multiple cells, not just a cell. Let’s see an example on how to do this and how useful this can be. In this sheet, I selected cells B2:B4 and putted a name, on the name box, of JanSales:

Now, I can put a formula on my sheet, for instance on cell B5, just like this:

=SUM(JanSales)

and it will give me the sum of the January sales. It’s easier to read the formulas If you use named ranges!

You can create many named cells at once. For instance, with this sheet, just select cells A1:B9 and, on the Formulas tab, on the Defined Names group, click on the “Create from Selection” option.

This will open the “Create Names from Selection” dialog box where the “Left column” option is already checked:

In this case, the names that we want to use for our cells are on the left of the values. If they were on the top, with the values below, we would choose “Top row”. Clck the OK button and you will now have 5 names on your name box, one for each value on column B. Excel replaced the blank space by an underscore so that the names can be valid.

You can manage your names on the Formulas tab, by clicking the Name Manager button. This will open the Name Manager dialog box where you can check the names that you have created, it’s values, the range to where it refers, the scope (either is for the whole workbook or it’s particular to one sheet) and also the comment that you can put to help you identify it. You have also a Filter button that you can use to filter the created names, for instance, if you just want to see the names build for the active worksheet.

Names can also be used to assign a constant value. Go to Data tab, click on the Name Manager button and on the Name Manager window click on the New button to create a new name. Then create a new name for IRS_Tax and on the “Refers to” field, put a 23% tax value, like this:

Now you can use this constant value on this IRS_Tax name on your formula, like this:

=100*IRS_Tax

You can created dynamic named ranges. This is very useful if you have a column with data and this column can receive more inputs. This way, you don’t need to edit your named range to the new range, it will automatically adjust it self. Let’s see an example using the OFFSET() function of Excel. We have a list of clients on column A, like this:

We want to create a dynamic range with the list of clients so that if we had another name on cell A12, the list will expand and consider also this new client name. We will create a new name and on the “Refers to” field, we will put and OFFSET formula, like the one on this image:

After you click OK button, if you click, on the Name Manager window, on the “Refers to” field, you will see that the range A1:A11 is select (with the “marching ants”). Now write a new client name on cell A12 and go to the Name Manager window again to see what range is selected. Is A1:A12, right? As you can see, this is a useful way of creating dynamic Validation Lists.

Finally, you can use Named Ranges to help you speed data entry on your sheets! We have seen that you can create named ranges referring to multiple cells. They don’t need to be contiguous cells. Let’s see a vey simple example on how we can apply Named Ranges to helps us speed data entry. We have this simple form:

We just need to insert data on the yellow cells, starting on cell B1, then B5 and finally cell B9. If we want to select a non contiguous set of cells, we just click on the first, then hold down Ctrl key and click on the rest of the cells. If you do this on this three cells, want the name box. It is showing B9 as our active cell. If you put a name for this range on the name box, and then select him on the name box, you will see that your starting input cell will be B9, then it goes to B1 and finally to B5. This is not the order we want! The trick here is to start clicking  on our second cell (B5), then B9 and end on the cell that we want to be our first input cell, in this case B1. This is our active cell so this is the one where we should put the name on the name box. So, put for instance, NewOrderInput and press Enter.Now select that name on the name box. You will get all of the three cells selected, starting on B1. Now enter some some value and press Enter. You will see your active cell move from B1 to B5. Repeat the same and you will see it move to cell B9. This is a great way of using Named Ranges to help you improve your productivity when it comes to input data into an Excel sheet.

Indent cells

Imagine that you want to build a report on a worksheet like this:

As you can see, the names of the vendors and the names of the expense groups, as well as the totals, are pushed away from the left of the cells. This improves the report look and makes it easier to read it.

Many people use blank spaces to make this kind of text alignment because they don’t know that Excel has an Indent feature right on the Home tab (Excel 2010) that can make this job a lot easier. Just check on the Alignment group of your Home tab for this two buttons:

The one on the left is the Decrease Indent and the one on the right is the Increase Indent. To use this, just select the cells that you want to indent and click on the Increase Indent as many times as the amount of “blank space” that you want to see on the left of your text. The indent will not add blank spaces to your cells content, as you can see on this image:

If you check on the formula bar, cell A5 doesn’t have any blank spaces on it but on the sheet, it looks like it has. This is one of the advantages of using indent instead of inserting blank spaces. If you want to remove the indent, just select the cells and click on the Decrease Indent button. If you had inserted blank spaces on each cells, and you wanted to remove it, you would need to go cell by cell, edit and remove each blank space!

Using What-if

Sometime ago, I wrote an article about Financial Functions in Excel. Using the PMT() function, I wanted to calculate a monthly payment for a loan, during a certain amount of months, at a specific interest rate. It looked something like this:

But what if we wanted to calculate the monthly payment using different loan terms? For instance, for 48, 60 and 84 months, instead of only 72? Sure, we could change the value on cell B3 and check the value for each term or we could copy the formula for each of the term value to see how it would change the monthly payment, but, what if we wanted also to check the monthly payment value if, not only we changed the loan term but also the loan amount? Excel has an easy solution for that! It’s called What-if and can be found on Excel 2010 on the Data tab, on the Data Tools group. Let’s see how we could use it to solve our problem.

Let’s put on column B the loan amounts that we want to use on our simulation and on row 5 the number of months for the term of our loan, like this:

Now we want to fill the rest of this table with the calculation using the formula on cell B5. This is where What-if function get’s in to action. Select your table (range B5:F10), Go to Data tab, and click on the What-If Analysis option. Then select Data Table.

A Data Table dialog box will open. On the “Row input cell”, we want the values on the top row of our table (range C5:F5) to the the input for our loan Term, so we should set this to cell B3. On the “Column input cell”, we want the values on our left most column of our table (range B6:B10) to be the input of our Loan Amount, so we should set this to cell B1, like this:

Click OK button and your will get your data table filled out showing the monthly payments for the loan, with the different loan terms and loan amounts, like this:

As you can see, this is easier than copying the formula to multiple cells or changing the values on the original formula to check what happens if you change any variable.

When making an Excel worksheet, there are some basic rules to follow so that your sheet looks nice and with a professional look. Here are some tips on how to improve your worksheets:

1 – Adjust the rows and columns size to fit your data in. Don’t have columns where your data is not completely visible, like this:

3 – Don’t use row 1 and column A. Your sheets will look nicer if you leave this empty. Take a look at the picture below where I’ve left row 1 and column A empty on the image to the right and compare it to the image on the left to see the difference it makes:

4 – Pay attention to colors. For the sheet background use white and for the text use black. Restrict the number of colors you use on your sheet. Use one or two more colors for pointing out something important on your sheet and you can also use a grey color on parts of your charts, like the labels, gridlines, axis.

5 – Use only one or two fonts on your sheet. It’s also a good design basic rule not to use multiple fonts on your sheets. You can use one for your titles and another for the rest of the data. That’s all!

6 – Align all of the elements on your sheet. Charts should be aligned to the grid and made with the same width and height of the cells where you want to place them. To help you align a chart or picture with the gridlines of your sheet, click on it and on the Format menu tab, on Arrange group, click Align and select Snap to Grip.

7 – Turn-off Gridlines, sheet Headers and Chart borders. When you finish building your sheet, this is a good way of giving it a better look. Go to your View menu tab and on the Show group, uncheck the Gridlines and Headings to remove the gridlines and the headers of your sheet. Right click on the charts on your sheet and select Format Chart Area. On the Border Color option, click on the No Border option to remove the borders of your chart.

8 – Use charts to present your data but avoid 3D charts. 2D charts look more modern and are easier to read than 3D ones.

9 – Put names on everything. You should always name your tabs on your workbook to avoid the default “Sheet1” and better find your data and be easier to interpret your formulas. This is easier to understand:

=SUM(January!B3:B13)

Than this:

=SUM(Sheet1!B3:B13)

Also name your cells range. It also makes it easier to read formulas. Here’s an example:

Just select the range of data (in this case C3:C13) and on the Name Box write the name that you want to give to this range of data, in this case, I’ve named JanSales. Now, I can use this named range on a formula like this:

=SUM(JanSales)

Isn’t this better looking and a better way of reading data on your sheet? If your sheet tends to grow, later it will much easier to read data on it and understand how the values where calculates.

10 – Use image on your sheets. Many of the sheets that you produce, are to be printed in paper, are used to generate reports. They will look much better if you use pictures, like logos for instance, to improve the look of them.