Question:     How do I return the rank of a number in a list of numbers relative to other values in a list? Answer:        By using the RANK function in Excel. The function returns the rank of a number in a list of numbers. The rank of a number is its size relative to other […]

Continue reading

Excel File Formats  Question: I am using Excel 2010, and some people in the organization are using Excel 2007 and Excel 2003. Some of the Excel reports they send have very different formats to the ones I know in Excel 2010. What is the difference between the Excel file formats? Excel File formats Explained   […]

Continue reading

Question:   I have a list of all the company’s employees in an Excel report and would like to assign the specific department they work in.  Is there a way to do this without having to type the department next to each employee’s name? Answer: Yes, by using Data Validation in Excel Why:  You use data […]

Continue reading

Question:  In Excel 2010, is there a way to automatically highlight upcoming and past due dates? For example, I have dates that stock will expire in a spreadsheet. I would like Excel to highlight the ones that have expired and those that are 30 days from expiration in green. Is that possible? Answer:  Yes, you […]

Continue reading

Using Value Field Settings in Pivot Tables Question: I would like to have two fields for analyzing sales in the Pivot table, one in a percentage format and the other in a value format. How can I accomplish this? Answer: By using the Value Field Settings Why:    To display the Product sales field in percentage […]

Continue reading

Question: How do I create an interactive report that can quickly be used to summarize large amounts of data? Answer: By using a Pivot Table Why:  A PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) is […]

Continue reading

Question: I have a long data list of products & selling prices stored in an Excel sheet. Is it possible to have a breakdown of subtotals for each product category?  Answer: Yes; by using the subtotal option in Excel Why:  To automatically insert subtotals and totals for the selected cells Important: The Subtotal command will […]

Continue reading

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  With reference to the data given below staff codes, P1, P2 and P7 have duplicate values     […]

Continue reading