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

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

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 and value formats Applies To MS Excel 2003, […]

Continue reading

Question:  How can I create my own calculated fields to include in the pivotTable?  I intend to have a mark-up of 25% on the sales figure and then calculate the profit. Answer:  By using the PivotTable formulae option Why:  To create calculated fields  Applies To MS Excel 2003, 2007, 2010  1.         This example is based on […]

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 useful to summarize, […]

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 appear grayed […]

Continue reading