Question:   I have a list of product sales with different transaction dates. How do I calculate the total sales for transactions between 2009/03/20 and 2009/03/30 by using one formula?   Answer:  By using the DSUM function (Adds the numbers in a field (column) of records in a list or database that match conditions that you specify) […]

Continue reading

Question:   I acquired a loan of $20,000. The repayment period is 3 years at 10% per annum.  Is there a way to work out the current repayment? I can afford to pay $1000 per month and would like to know the new repayment period too.  Answer:  By first using the Payment function, and then using […]

Continue reading

Question: Is there an automated way of deleting duplicate data rows from a worksheet?   Answer: Yes, by using remove duplicates data tool Why:  To remove duplicate values from a worksheet  Applies To:  MS Excel 2007 & 2010   1.        With reference to the data given below staff codes, P1, P2 and P7 have duplicate values […]

Continue reading

Question: How do I make a specific cell mandatory to fill in, in an Excel workbook?  We have a form that employees fill in but require that important information like employee names to be mandatory.  Can this be done in Excel? Answer: Yes, by using Visual Basic For Applications (VBA) Why:  To make a specific cell mandatory […]

Continue reading

Question: We have just upgraded from MS Excel 2007 to MS Excel 2010.  When applying conditional formatting in MS Excel 2007 we were unable to display negative data bars.  Can this be done in MS Excel 2010?  Answer: Yes, by using conditional formatting Why:  To create data bars for negative & positive values  Applies To […]

Continue reading

Question:   How can one merge MS Excel with MS word given that we have a list of clients stored in MS Excel? Are we able to automate the sending of statements to clients? Answer:   Yes, by merging MS Excel with MS word Why:   To automate the sending of documents by merging MS Excel with MS […]

Continue reading

Question: Given that I have a column of dates in values in my report, how can one only show the name of the months and can this be done in Microsoft Excel? Answer: Yes, by using the text function in Excel Why:  The Text function coverts a value to text in a specific number format […]

Continue reading

Question: Can you help me to apply custom format for employee numbers to all excel workbooks on my computer?  I would like the employee numbers to have preceding zeros such as 001,002,003 etc. Answer: Yes, with the Personal Macro workbook option. Why:  For the custom format to apply to all workbooks on the computer. Applies […]

Continue reading

Question:  I find it tiresome switching between ribbons in Excel 2010 when I am creating reports. Is there a way of customizing a ribbon so that I can group all the frequently used functions together? Answer: Yes, by creating a custom ribbon in Excel 2010. In fact, to make your reporting easier, Alchemex has created […]

Continue reading

Question: Excel is known for its excellent formulae & function capabilities. As such I extensively use Excel to calculate the variance between the target sales amount and the actual sales amount for our salesagents. However, sometimes the formulae returns errors/error messages such as #DIV/0! Is there a way of trapping error messages, so that a […]

Continue reading