Question: I’m using conditional formatting to find duplicate values in a column, and it’s working in that it highlights both values if they’re duplicated. How do I ignore the first instance and highlight only the second?   Answer: By using the COUNTIF function   Process (Excel 2003, 2007 and 2010):   Conditional formatting is a […]

Continue reading

Question: I’ve used a VLOOKUP formula, but it’s not working and is returning an error. How do I fix it?   Answer: By checking the type of error that’s been returned.   Process (Excel 2003, 2007, 2010): The two most common types of errors that will be returned by an Excel formula are #REF and […]

Continue reading

Question: I’ve selected all blank cells in a column, I would now like to enter a formula in all of the cells at the same time. Is this possible?   Answer: Yes, with a  keyboard shortcut.   Process (Excel 2003, 2007, 2010):   In last week’s tip (http://www.alchemex.com/Resources/EOS%20Previous%20Tips%20And%20Tricks/Go%20To%20Blanks.pdf), we looked at selecting all cells in […]

Continue reading

Question: I’m going to plan a holiday, and I’d like to save $10,000 to cover all expenses. How do I figure out how much I need to pay into my savings each month to get to my target?   Answer: The PMT function can help   Process (Excel 2003 and 2007):   When it comes […]

Continue reading

Question: I’m looking at taking out a loan from the bank. I know how much for, and what the interest rate would be, but how do I figure out what the payments would be per month?   Answer: The PMT function can help   Process (Excel 2003 and 2007):   When it comes to finance, […]

Continue reading

Question: I would like to see which cells are blank in a column of data, and I don’t want to resort the the column. Is there a quick way that I could do this?   Answer: Yes, using the ‘Go To Special…’ option   Process (Excel 2003, 2007, 2010):   In this example, we have […]

Continue reading

Question: I have a sheet of sales information and would like to add together all lines for not only a specific product category, but for all sales of that product category in a particular region. How can I do this with one formula? Answer: By using the SUMIFS function   Process (Excel 2007 and 2010): […]

Continue reading

Question: I have a sheet of sales information and would like to add together all lines for a specific sales person. How can I do this?   Answer: By using the SUMIF function   Process (Excel 2003, 2007 and 2010):   The SUMIF function is very useful, allowing you to total a number of lines […]

Continue reading

Question: I am using Excel 2007 for my sales report.  I know I can use the Report Filter in the PivotTable to filter all the sales data. Is there a way that you can just filter a specific Row field as I would like to filter my sales data by Date only? Answer: Yes, using […]

Continue reading