Excel Archives - Page 3 of 9

07May 2015 by Charlie Nichols No Comments

How to calculate compound interest in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Compound Interest Compound interest is interest added to the principal and interest earned in previous periods. This addition of interest to the principal is called compounding. Just below you can see how to calculate compound interest in Excel. The formula for calculating compound interest is =PV*(1+R)^N How to calculate compound interest in Excel The formula above is entered in Excel as =PV*(1+R)^N In the formula, PV is the present value. R is the interest rate. And N is the number of periods (such as annually) that you want to compound. Example Suppose you have $1000 to start with. Your present value (PV) would be 1000. If you expect to earn 7% annual interest, your rate (R) would be .07. To see how much you would have after 10…

03Feb 2015 by Charlie Nichols No Comments

How to compare two strings in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Compare Two Strings in Excel for Similarity Excel comes loaded with several useful text or string comparison functions. Out of the box, Excel provides a function named EXACT that will return whether two strings are exact matches. That's great, unless you actually need to find strings that are really close or very similar. The good news is when you want to compare two strings in Excel you aren't limited to exact matches only. Using a function such as pwrSIMILARITY you can compare two strings and get a rating back (a percentage) that indicates the degree of similarity between the two strings. You actually have a great deal more flexibility with pwrSIMILARITY. For example, the EXACT function is case sensitive so comparing "Abc" and "abc" would return…

22Jan 2015 by Charlie Nichols No Comments

How to Generate Random Text in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] A Little About Lorem Ipsum "Lorem Ipsum" text is commonly used in the printing or design industry as dummy text used as a placeholder for "real" text. The Lorem Ipsum text is originally from Latin literature that is over 2,000 years old. It is not Latin-looking gibberish as often believed. Generate Random Text in Excel You can use the pwrRANDOMTEXT function in Excel to generate random text. The random text generated will be based on the Lorem Ipsum text commonly used as filler. This function is available as part of the Random Data Assistant contained in the Excel PowerUps Premium Suite add-in for Excel. In order to use this function you simply use it in your worksheet as you would any other function. Examples If you want to…

08Dec 2014 by Charlie Nichols No Comments

How to Hide Gridlines in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] I've got this question several times. How do you hide the gridlines in Excel? It's a really quick task as you can see below. For each of Excel 2013, Excel 2010, and Excel 2007 you can click the View tab in the Ribbon. In the Show group, uncheck the Gridlines checkbox. Now you know how to hide gridlines in Excel. To show gridlines in Excel, just check the Gridlines box. There you go.

21Oct 2014 by Charlie Nichols No Comments

Return Random Value from List in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Two Functions to Make Returning Random Data Easy Using the pwrRANDFROMSET and pwrRANDFROMRANGE functions it's easy to get a random value back to your formula. Random Values from a Set The pwrRANDFROMSET function returns a value from a set defined within the function while the pwrRANDFROMRANGE function returns a value from a defined range in Excel. If you wanted to return a random value from a set of colors you can use the equation below as an example. =pwrRANDFROMSET("red", "pink", "green", "blue", "black", "orange", "yellow") This will return a random value from the set of colors in the formula. Random Values from a Range The pwrRANDFROMRANGE function returns a value from a range of cells. This range can be multiple columns and rows as long as they are…

24Sep 2014 by Charlie Nichols No Comments

How to Collect Excel User Input and Return to Formulas

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Collecting Excel User Input Using the Excel PowerUps Premium Suite you can easily collect basic user input and return that input to your formula. You can use the function called pwrINPUTBOX to prompt the user to provide some information. Since this is a function made available to you in Excel, you don't need to write any code. Example Here's an example. Imagine you need to have the user provide some information if a value is beyond a given threshold. Let's say if a key performance metric falls below a specific level you want to capture a brief note regarding why from the user. So let's say we want to capture an explanation if the KPI falls below 85%, and that the KPI is in cell A1. The formula…

24Sep 2014 by Charlie Nichols 2 Comments

How to Show an Excel Message Box or Alert without Code

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Show an Excel Message Box Occasionally, you need to alert the user of a specific condition or value in your Excel workbook. For example, if a value gets higher or lower than a desired level you may wish to make that more apparent to the user. You can use the Conditional Formatting features in Excel to, say, make the field bright red. However, you would be relying on the user to view that tab or potentially scroll to that part of the worksheet to actually see the value in the first place. Alternatively, you can pop up a message box to alert the user of the condition or value. One method is to use the embedded scripting system within Excel and write some custom code to pop up…

17Sep 2014 by Charlie Nichols No Comments

Latest Excel PowerUps Add-In Update: Version 1.7.1 Adds User Input

Excel

A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new functions. Go to the download page. New Functions pwrCONTAINS pwrCONTAINS has been added to make it very easy to check whether a one string is contained within another string. pwrINPUTBOX pwrINPUTBOX provides a way to gather user input from within a formula. You can use the text the user provides in the formula or simply display. pwrMESSAGEBOX pwrMESSAGEBOX provides a way to alert on the conditions you define in your formulas. If a value in another tab or off-screen reaches a trigger level, for example, you can use pwrMESSAGEBOX to alert the user of the condition rather than waiting for the user to scroll or tab over and see the value…

16Aug 2014 by Charlie Nichols 1 Comment

Excel Burndown Chart Template

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] A basic Excel burndown chart template is available. Usage of the template can be found in the How to Create a Burn Down Chart in Excel article. Download File Download the Excel burndown chart template here. There you go.

26May 2014 by Charlie Nichols No Comments

Excel Add-in Update: Version 1.6.4 Adds New Capability

300 level, Excel

A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new and enhanced functions along with some bug fixes and tool additions. Go to the download page. Enhanced Functions pwrFILTERXML pwrFILTERXML has been extended to directly open XML files or call XML web services. This allows pwrFILTERXML to work without having to first call the WEBSERVICE function in another cell. This also means you will not be limited by Excel's 32k per cell character limit. This is key for utilizing web services that return more than 32k of text. pwrWEBSERVICE pwrWEBSERVICE has also been extended to directly open XML files. This allows you to reference local XML files. You will still be limited by Excel's built-in 32k limit per…