So it’s been a while since I’ve lasted posted as I’ve been away on holiday. During that time, a few friends have sent me some challenges and asked some questions, so my first few posts coming back will deal with them! Firstly, the Excel questions! The first question was from a friend, who hates Excel, literally. If she can use any other software out there, she will. Her question was:
“Is there was a way to do calculations in Excel without actually using formulae?”
I think she expected me to say no, but in actual fact there is a way, so I thought I would post it for everyone to view!
1. Using Excel’s Paste Function to calculate values for you…
Imagine if you will, you want to adjust an existing spreadsheet to calculate what an increase in price would look like on your product range, if you increased all of your prices by say 8%. You could of course, create some formulas to do the mathematics, and then convert the formulas to values, and then copy and paste the new prices over the old prices.
Or, you could…
- Imagine a column of products, say column A
- Imagine column B contains the current price
- In cell D1, enter 1.08 (Excel will use this to calculate the new price increase by 8 percent.
- Select cell D1 and choose Edit > Copy or press Ctrl-C.
- Select column B (which contains the values you wish to change and choose Edit > Paste Special to display the Paste Special dialogue box.
- Choose the Multiply option and click OK.
The values should immediately update and you can then delete the value from cell D1. You can of course paste the values into a different column, for example column C if you want to keep the original values as well. You should also note that the Paste Special dialogue box can carry out other mathematical operations, for example add and subtract, so you can use this technique for a variety of other calculations.
2. Generate a unique list of entries in a column
The second question I received was:
“How can I get a list of unique entries in a new column (i.e. without using filters), so that I can edit the new list”.
After a bit of discussion, I realised that my colleague was simply adding a basic filter to the original column, and of course didn’t want to edit that as she would lose the original data. She wanted a new list created automatically that only contained unique values. Well the best way I know of doing that still uses filters, but uses advanced filters. So again, let’s imagine that you have a list of items shipped to customers during the month. Some of these will be the same product. Let’s imagine that they are in column A of your spreadsheet.
- Highlight the list you wish to create your unique list from, for our example, column A. (For simplicity, you can even just click on the column header)
- Click on the Data tab of the ribbon, and in the Sort & Filter section, click on the Advanced button
- Then adjust the options:
- Copy to another location
- In the “Copy to” section, select the new column
- Choose unique records only
And there you have it a new list with only unique values, which you can sort and change as you wish!
3. Spot duplicate entries
The same colleague also wanted to know:
“Is there was a quick way to spot duplicate entries in an Excel spreadsheet?”
There is a quick way of doing this with conditional formatting that I use a lot and that you also may find useful. In the picture below you will see a list of fruit in column A. If you wanted to spot the duplicate entries, you could…
- Highlight the column
- Click on the Home tab of the ribbon
- Then click on Conditional Formatting > Highlight Cells Rules > Duplicate Values…
- Choose the formatting style you want from the drop down menu box and press the OK button
Hope this helps!