Tools: Paste Special
Level: beginner
Problem
From time to time you will have to save the result of a formula, and sometimes more than a single formula, but rather an array of them.
Someone sent you a range of data that you need to label with line numbers to use as a database. Dragging a fill handle for 1000 lines is a difficult and error-prone solution, so you wrote a simple formula and copied it across the range to create numbers from 1 to 1000. But if you try to sort this table in any way other than its original order, the line numbers will change. How do you make these numbers permanent?
| A | B | |
| Item1 | ||
| =A1+1 | Item2 | |
| =A2+1 | Item3 | |
| =A999+1 | Item1000 |
Solution
- Select the A1:A1000 range.
- Copy.
- Click Paste. Alternatively, you can select Paste Special.
- Select Values.
- Hit Enter.
The formula results will get pasted right over formulas. Mission accomplished.
Similar tasks
Paste Special is a highly flexible and functional command. Among other things, it can let you do the following:
- Paste column widths. This command helps a lot if you want several columns to be of same exact width as a certain column.
- Transpose a matrix. This command will rearrange the data in a matrix so that rows become columns and columns become rows.
- Paste with math operations. You got from someone data where percentages are expressed as 25 instead of.25. After formatting it as "%", you will see "2500%" where "25%" is supposed to be. You will appreciate the possibility of instantly dividing the whole range by 100 without creating an additional range of divided data, saving it as values and pasting it back. Type 100, cut the cell and Paste Special it to this range with a "divide" switch on.
A legible formula
Tools: range names
Level: beginner
Problem
You need to calculate employees' social security contribution. The company pays to social security a fixed fee of 25 dollars per employee per year plus a 6.25% of salary earned during this period.
| A | B | |
| Fixed fee | ||
| Social security rate | 6.25% | |
| Employees | Salary | |
| Jack Sparrow | ||
| Elisabeth Swann | ||
| Will Turner | ||
| Total SS contribution | =$B$1+($B$2*SUM($B$4:$B$6)) |
To understand the formula you will need to look up what is in cells B1, B2 and range B4:B6. As formulas and data sets get bigger, it will be harder and harder to understand the code.
There is a way to make a formula more legible by using names.
Solution
Names are essentially text shortcuts that are assigned to a cell or range in your workbook. Names can also refer to formulas but we will not need this feature so far, even though it will prove very useful later on. There are at least three methods to create names from workbook data.
- Active cell B1.
- Find a Name Box of a formula bar. It is by default the first box from the left and it shows now B1.
- Click in Name Box.
- Type ss_fee.
- Press Enter.
You have created a name ss_fee that stands for whatever is entered in cell B2.
Next, you can use a Define Name command. It is available from Insert-Name-Define menu (03) or Formula ribbon tab (07/10).
- Active cell B2.
- Use Define Name command.
- In the dialog that opens, type ss_rate.
- Press Enter or click OK.
And finally, you can create a name from a text string cell automatically. For this, we will use Create Name menu command (03) or Create from Selection ribbon command (07/10).
- Select range B3:B6
- Use Create Name / Create from Selection command
- Make sure there is a selection check mark in Top Row field.
- Press Enter or click OK.
Range B2:B4 is now named Salary. Note that B3 which actually contained the name is properly excluded from range and will not mess with calculation. If you change the column header, the range name will not change until you redefine in, so do not count on this in your formula writing endeavors.
Now let's use the newly created names in the formula. There are also several ways to do so.
- Active cell B7.
- Type =
- Press F3.
- Select ss_fee from the dialog that opens.
- Press Enter or click OK.
- Type +(
- Use menu command Insert-Name-Paste or ribbon command Use in Formula
- Select ss_rate from the dialog that opens.
- Press Enter or click OK.
- Type *
- Start typing salary. A dropdown list of names that match what you typed will appear.
- Select salary from the dropdown list of names.
- Type)
- Press Enter.
Now your spreadsheet will look as follows.
| A | B | |
| Fixed fee | ||
| Social security rate | 6.25% | |
| Employees | Salary | |
| Jack Sparrow | ||
| Elisabeth Swann | ||
| Will Turner | ||
| Total SS contribution | =ss_fee+(ss_rate*salary) |
Similar tasks
To edit names once they are created, use Insert-Name-Define menu command (03) of Name Manager ribbon command (07/10). Name Manager is a convenient interface to edit many names at once. For Excel 2003, you can install an add-in by Jan Karel Pieterse that creates a Name Manager interface.
It is best to follow certain best practices when using names:
- Names are great if not overused. If you have a variable that is repeatedly called many times over in a sheet of workbook, this is probably a good name target. If you are referring to a cell just once, better leave it as is.
- Names should be brief but descriptive. By looking at a name, you should at least understand what it may mean. Names that are too abstract or too abbreviated will keep confusing you.
- Groups of similar names can be identified by a prefix. For example, if you have several lists, you can start all of their names with l_ - e.g. l_units, l_prices and so on. This way, that will stick together in the list and in the Name Manager.
For other operations with names, refer to Define and use names in formulas | Online Office Help from Microsoft.
A dynamic date range
Tools: functions DATE, YEAR, MONTH, DAY, TODAY
Level: intermediate
Problem
You need to make a project timeline with monthly intervals. You do not know, however, when the project will actually start. It may be this month, next month or even next January. Given that your model will be reviewed in many meetings, you will need to update the timeline every time you take it anywhere. Worse, your boss might take it to meetings too, and you cannot rely on her updating the timeline.
How do you make a formula that automatically starts the project timeline from the first day of the month immediately following this month?
Solution
We will use several built-in Excel date and time functions. In particular, we will need TODAY(), YEAR(), MONTH(), DAY() and DATE(). For simplicity's sake, let's also number future monthly periods – 1,2,3,4 and so on and enter them in cells A6 and down column A.
- Enter function =TODAY() in cell B1. It requires no arguments and only displays the current date. Excel date is a serial number. Excel considers January 1, 1900, to be day number 1, January 2, 1900 day number 2 and so on. The dates from year 2012 already exceed 40 000.
- Enter function =YEAR(B1) in cell B2. YEAR returns the year of a date as number.
- Enter function =MONTH(B1) in cell B3. MONTH returns the month of a date as number.
- Enter function =DAY(B1) in cell B4. DAY returns the day of a date as number. You have now the today's date properly broken into year, month and day. Every day, these figures will change, as TODAY() function that they refer to will return new value.
- Now, let's reconstruct the first of next month from these dynamic variables. Enter function =DATE($B$2,$B$3+A6,1) in cell B6.
- Copy cell B6 down column B. You now have a range of cells in column B with future period dates.
| A | B | |
| Today's date | =TODAY() | |
| Today's year | =YEAR(B1) | |
| Today's month | =MONTH(B1) | |
| Today's day | =DAY(B1) | |
| Period | Period date | |
| =DATE($B$2,$B$3+A6,1) | ||
| =DATE($B$2,$B$3+A7,1) | ||
| =DATE($B$2,$B$3+A8,1) | ||
| =DATE($B$2,$B$3+A9,1) |
We could have also embedded all the intermediate calculation in the DATE() function; the only reason not to do so is make the formula more understandable at this point.
Formula syntax
In generic form, the DATE() function we used uses the following syntax and variables.
| Code | Variable | Comment |
| =DATE( | ||
| YEAR( | Year | Year of the date for which you want a serial number. |
| TODAY() | No variables | We use the same year as today's date. |
| ) | Function YEAR() complete | |
| MONTH | Month | Month of the date for which you want a serial number. |
| (TODAY() | No variables | We take today's month… |
| +1 | …and add 1 to it to make it next month. 1 is actually the period's ID, so we can substitute a fixed number with a value from cell with the project ID. | |
| ) | Function MONTH() complete | |
| Day | Day of the date for which you want a serial number. We use a fixed number – 1, as we want a date for 1st of every month. | |
| ) | Function DATE() complete |
Note that in December our function will have the same year and month 13 for arguments. Which is incorrect (duh). Fortunately, Excel takes this into account and just advances the year by 1, and returns the month to 1 – as it should be.
Similar tasks
Task 1.
Two days later.
Solution
=TODAY()+2
We do not need a DATE functions since every day is +1 to a serial number that TODAY() returns.
Task 2.
Three months before.
Solution
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))
Task 3.
Last day of current month.
Solution
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
A zero day will roll the date one day back from first day of month – i.e. to the last day of previous month.
Check for a condition
Tools: function IF
Level: intermediate
Problem
If the company had positive profit in a certain quarter, it should pay profit tax (30% of reported profit). If the quarter closed at a loss, no tax is due. The company had the following quarterly results (in millions of dollars):
| A | B | C | D | E | F | G | H | I | |
| Quarter | |||||||||
| Profit | -5 | -4 | -6 | -4 | |||||
| Tax |
How do you compute profit tax with just one formula?
Solution
Use IF() function.
- Active cell B2.
- Use Function Wizard or type =IF(B2<=0,0,B2*0.3) and press Enter or click OK.
- Copy formula to C3:I3.
You should now have the following table.
| A | B | C | D | E | F | G | H | I | |
| Quarter | |||||||||
| Profit | -5 | -4 | -6 | -4 | |||||
| Tax | 1.2 | 1.2 | 1.5 | 1.8 |
Formula syntax
| Code | Variable | Comment |
| =IF( | ||
| B2<=0, | Logical_test | Check if B2 contains a profit or a loss? |
| 0, | Value_if_true | IF B2 is negative or zero, then it's a loss and condition is TRUE. In this case, return 0 – no tax due. |
| B2*0.3 | Value_if_false | IF B2 is positive, then it's a profit and profit tax is due, condition is FALSE. Compute tax by multiplying profit by 30% and return the value. |
| ) | Function complete. |
Similar problems
Task 1.
Column A contains party guests, column B - their age. Mark in column C those guests who are and are not allowed to drink. Legal drinking age is 21.
Solution
=IF(B1<21,"No alcohol!","Can drink")
Task 2.
Column A contains sums, column B has a mark for the currency – it's either "dollar" or "euro". Convert all sums market as "euro" to dollars at an exchange rate of 1.4 dollar per euro.
Solution
=if(B1="euro",A1*1.4,A1)
Task 3.
Prevent division by zero.
Solution
=IF(A2=0,"Do not divide by zero!",A1/A2)
