Excel Tips & Tricks

Using keyboard shortcuts can allow you to save a lot of time when working with repetitive tasks. Using keyboard shortcuts in Microsoft Excel helps you increase your efficiency and productivity to use Microsoft Excel like a Power User. In this Excel Tip and Trick we take a look at how we can use Excel Shortcuts [...]

{ 0 comments }

Question:     I have cash flow projections for two projects, how do I select the most viable project between the two? Answer:        By using The Net Present value (NPV), function and selecting the project with the highest  NPV Why:            Calculates the net present value of an investment by using a discount rate and a series [...]

{ 0 comments }

Question:     How do I calculate the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods? Answer:        By using the internal rate of return (IRR) Why:        Returns the internal rate of return for a series of cash flows represented by the [...]

{ 0 comments }

Question:     How do I format cells to show dates as the day of the week? Answer:        By using the Custom number format command Why:            To display dates as the day of the week. (The text function can also be used to show dates as the day of the week)  Applies To: Excel 2003, 2007, [...]

{ 4 comments }

Question:     Can I bypass the Text Import wizard when importing text files into Excel? Answer:        Yes  Why:            To quickly import a correctly parsed text file into excel and bypass the text import wizard Applies To: Excel 2003, 2007, 2010 The following text file will be used for this example. Assuming the name of the [...]

{ 0 comments }

Question:     How do I display the total sales amount by way of a chart?  I don’t want to use the normal chart options given in Microsoft Excel. Is there an alternative to the normal chart options? Answer:        Yes, the REPT function Why:            Repeats text a given number of times. Use REPT to fill a [...]

{ 0 comments }

Question:     We commissioned a research into the buying habits of our clients. How can we find the most frequently ordered quantity of our product? Answer:        By using the Mode function Why:            Returns the most frequently occurring, or repetitive, value in an array or range of data. Syntax MODE(number1,number2,…) Number1, number2, …   are 1 to [...]

{ 0 comments }

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 [...]

{ 0 comments }

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 [...]

{ 0 comments }