200 level Archives - Page 2 of 3

23Mar 2014 by Charlie Nichols No Comments

Add Excel 2013 Functions to Excel 2010

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Get the Latest Excel Functions Working in Older Versions of Excel Do you have a number of Excel 2010, Excel 2007, or Excel 2003 licenses that you want or need to get another year or more of use from? Do you need to hold off on upgrading all of your licenses to Excel 2013, at least for now? But are you in need of some of the new functions that were added in Excel 2013? Perhaps you have a worksheet that was created in Excel 2013 that makes use of one or more of the new Excel functions and you need to be able to open and work with the same workbook on PCs with Excel 2010, 2007, or 2003. You can. Excel Forward Compatibility Adapter You can…

09Nov 2013 by Charlie Nichols No Comments

Fuzzy Search in Excel with the Fuzzy Find and Replace Tool

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Fuzzy Search and Replace in Excel You can find similar entries from a list or table in Excel by doing a fuzzy search in Excel. This gives you a way to consider the following to effectively be the same. John Smith Jonathan Smith Smith, John Smith, Jonathan John Q. Smith John Smyth J. Smith Johnny Smith … etc. So if you just want to look for “John Smith” and simply find those entries that are pretty close to that. You want to do a fuzzy text search (not just a wildcard search at the beginning or end of a string). This post describes how to use the Fuzzy Find and Replace feature of the Excel PowerUps add-in for Excel to find those approximate matches. Fuzzy text search in…

21Aug 2013 by Charlie Nichols No Comments

Percentage Change Between Two Numbers

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] I was asked what the Excel function was for finding the percentage of change between two numbers in Excel. It turns out there is no dedicated function for that. That's OK. The formula for calculating the change between two numbers in Excel is really simple (and there is one less function in Excel that you'd need to learn). The formula for % change between two numbers The simple formula is shown below. =(A2/A1)-1 In this example, cell A2 contains the new number and cell A1 contains the original number. The formula will return a value that represents the percentage change between the two numbers. Negative changes will show up as negative numbers. If you want to see the value as a percentage, just click the percent sign button "%" in…

07Aug 2013 by Charlie Nichols No Comments

Calculator function equivalents in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel calculator functions I took a look at a basic scientific calculator I had in a drawer and I thought it might be interesting to show how each of the buttons on the calculator would work in Excel. So below is my simple inventory of the scientific calculator functions and their equivalent functions in Excel. In the examples below, just assume that the value of “x” is in cell A1. Calculator function list Inverse function $latex x^{-1}$ Excel formula(s) Each of the formulas below achieves the same thing in Excel. =1/A1 =A1^-1 Trig functions $latex sin(x)$ $latex cos(x)$ $latex tan(x)$ $latex sin^{-1}(x)$ $latex cos^{-1}(x)$ $latex tan^{-1}(x)$ Excel formula(s) Each of the formulas below achieves the same thing in Excel as the corresponding calculator button above. =SIN(A1) …

30Jul 2013 by Charlie Nichols No Comments

How to convert text to number values in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] In Excel, if you have text that represents a number that is not recognized as a number value some Excel formulas may not work correctly. In order to convert a text number into a number value you have a few options. Convert text to number First, you can just multiply the value by 1. This will force the conversion of the text into a numerical value and return the equivalent numerical value. =A1*1 Next, you can use the VALUE formula. You wind up with the same result. =VALUE(A1) Convert Roman Numeral text to number If your text is a Roman Numeral you can convert that to an equivalent numerical value (in decimal). Excel 2013 introduces a function called ARABIC that will convert roman numerals into decimal value equivalents.…

25Jul 2013 by Charlie Nichols No Comments

How to get the formula of a cell in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to get the formula in a cell To get the formula in a cell, the FORMULATEXT function was introduced in Excel 2013. It's as simple as the following. Just reference the cell with the formula you're interested in. =FORMULATEXT(A1) Need to do the same, but in older versions of Excel? If you need to get the formula in a cell in Excel 2003, 2007 or 2010 you can use the pwrFORMULATEXT function. pwrFORUMLATEXT is available in an Excel addin you can download from the link below. You use it the same way as the new function in 2013. =pwrFORMULATEXT(A1) Get the free addin Overall, there are about 50 new worksheet functions in Excel 2013. The addin linked below adds 46 of the 50 functions so your legacy version…

14Apr 2013 by Charlie Nichols 2 Comments

How to use Excel to test a list of URLs or links to see if any are broken

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Link checking in Excel Out of the box, Excel doesn't test links to see if they're broken. It does have a HYPERLINK function to make some text you've formatted as a URL to be clickable. But you still need to click the link, which is a pain with a long list. But with this cool add-in for Excel you can test your entire list using one of its Web Assistant functions called pwrISBROKENURL. It does just what you think it might -- it returns a TRUE value is the URL is broken. Using this function for checking links can be a huge time saver. You can use it as simply as in this example. =pwrISBROKENURL("https://officepowerups.com/brokenurl") You can use this in a more complex formula and use more complex…

10Apr 2013 by Charlie Nichols No Comments

How you can add the 50 New Functions in Excel 2013 to your old copy of Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel add-in to power up older versions of Excel Excel 2013 has been released and it looks like there are about 50 new worksheet functions added. Now you can get a matching function for each of the new Excel 2013 functions that has the same name and same parameters. [table id=3 /] Excel 2013 new functions available for your old version of Excel The listing below shows the Excel 2013 new function that is available for Excel 2010, 2007 and 2003. The matching Excel PowerUps function is in parentheses after the name and can be used within Excel 2013 as well. You'll notice that each of the PowerUp functions has a prefix of "pwr". This is done to avoid name collisions with other functions you may have. All…

04Apr 2013 by Charlie Nichols No Comments

Simple explanation of the Excel LOOKUP function

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel has a number of functions that will look up a value based on finding a corresponding value in a range, row, or column. Meet LOOKUP The Excel LOOKUP function matches a value you're looking for in a row or column and then returns the value from another matching row or column. I tried to create a visual to help. Part 1 in the image is LOOKUP finding the value you're looking for (represented by the "?"). Part 2 is LOOKUP finding the corresponding value in the column or row you specify. So the value returned (shown in red above) is the value you were looking for based on the initial value you were matching. Note that in the picture above, both looking up by row and by…

22Mar 2013 by Charlie Nichols No Comments

Are you tired of looking at Excel formula errors?

200 level, Excel

Now that you're getting into using formulas in Excel you've found they can be quite useful. And by now you've found that sometimes they return error values. Sometimes, these can detect errors you need to fix. Other times, they just indicate that you don't yet have all the data populated in your worksheet. You wind up with a cell showing values such as #VALUE!, #DIV/0!, #N/A, etc. It just looks messed up and as if the whole sheet might be suspect. You can add another function to your cell that will "wrap" your existing function with a small layer of error protection. Meet IFERROR The IFERROR function lets you simply show one value if your function runs normally, and alternate value (that you define) if your function returns one of…