Question:            Is there an alternative to the minimum function when finding the smallest value in a given data range? Answer:               Yes, the Small function Why:                Returns the k-th smallest value in a data set. Use this function to return values with a particular relative standing in a data set Syntax SMALL(array,k) Array     is an […]

Continue reading

Question:     I have just imported data into MS Excel. How do I remove leading or trailing spaces from the data?  I also would like to limit the amount of space between words to one. Answer:        By using the TRIM function Why:            Removes all spaces from text except for single spaces between words. Use TRIM […]

Continue reading

Question:        You know how to find the largest value from a given data range by using the maximum function.  But how can one find the second largest value from a data range? Answer:           By using the LARGE function Why:              Returns the k-th largest value in […]

Continue reading

Question:     How do I create a reference (name) that refers to the same cell or range on multiple sheets? Answer:         By creating a 3D reference name  Why:             A  3D reference is a useful and convenient way to reference several worksheets that follow the same pattern and contain the […]

Continue reading

Question:     Can you connect a slicer to more than 1 PivotTable? Answer:        Yes, by using the Slicers connection functionality. If you have created 2 PivotTables and you have created a slicer off the PivoTable1, you can connect the same slicer to use Filter on the PivotTable2. Applies:        Excel 2010 Method: Select a cell in […]

Continue reading

Question: Can I expand the usefulness of other functions that perform logical tests?  Answer: Yes, with the AND function  Why:  One can test many different conditions by nesting the And function with the IF statement. Process (Excel 2003, 2007 and 2010): Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or […]

Continue reading

Question:     How can I search for a value in the top row of the table and then return a value in the same column from a specified row? Answer:        By using Hlookup Why:            To perform a horizontal lookup on a data list Applies To:  Microsoft Excel 2003, 2007, 2010  Refer to the data given […]

Continue reading

Question:  I send out a weekly stock report to the stock controller to update with the new stock items that come into the warehouse. In this Excel report the cell that contains a product code name always needs to begin with a standard prefix of ID- and must be at least 10 characters long. How […]

Continue reading

Question:  Is there a way to quickly extract certain data from a PivotTable in Microsoft Excel? Answer: Yes, but using the GETPIVOTDATA function Description:  Returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report Applies To MS […]

Continue reading

Question:     I would like to ensure that the end date is greater than the start date?  Can this be done in MS Excel? Answer:        Using Data Validation Why:          When entering project tasks the end date has to be greater than the start date Applies To: Excel 2003, 2007, 2010  1. Refer to the data […]

Continue reading