I just love Monty Python! And since I blog, I’m always looking for new ways to improve and enrich my work. Loved this slideshare by Nick Kellet:
What do you think?
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!
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…
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.
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.
And there you have it a new list with only unique values, which you can sort and change as you wish!
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…
Hope this helps!