excel Archives - Page 4 of 8

15Feb 2014 by Charlie Nichols No Comments

Creating a Constant in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Occasionally, it's useful to have a constant defined that you can simply refer to instead of having to remember the value. For example, the value of Pi is commonly remembered as 3.14. Or, you may need to have a value that may change but you don't want to update formulas throughout your workbook whenever the value changes. One way to go about this is by creating a constant in Excel. Constants have the properties desired as well as showing up in the autocomplete list that appears when you enter formulas. Steps for Creating a Constant in Excel Creating a constant in Excel is pretty simple. The steps are outlined below. First, go to the Formulas tab in Excel and click Define Name on the Define Name menu in…

14Feb 2014 by Charlie Nichols No Comments

Excel Order of Operations is the Same as Math

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel follows the same order of operations that you learned back in your math classes. If you don't pay attention to these rules when you create your formulas, you may wind up struggling to figure out why your results make no sense. Excel Order of Operations The following ordering dictates in which order your formulas will get evaluated. Although our eyes may scan from left to right, Excel doesn't look at formulas that way. ParenthesesExponentsMultiplication and DivisionAddition and Subtraction Excel Order of Operations Example To illustrate how the order of operations matters, consider the example below. 1+2*4 If you were to try and evaluate this from left to right as it is written, you would first get 1+2=3 Then, you would multiply that 3 times 4, giving you…

17Dec 2013 by Charlie Nichols 1 Comment

Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Now, Add This New Excel 2013 Trio of Functions to Your Version of Excel Excel 2013 introduced a trio of new functions that make it easy to extend your worksheets to use XML web services. The Excel WEBSERVICE function, ENCODEURL function, and FILTERXML function work nicely together to make the task of working with web services straightforward within Excel 2013. This functionality is made available in Excel 2010, Excel 2007, and Excel 2003 in the Excel PowerUps Premium Suite add-in for Excel. This can be downloaded for free. There are many posts and videos posted on YouTube illustrating how you may use the features and seeing how many there are give you a sense for how popular of an addition these functions are to Excel. The sections below…

11Dec 2013 by Charlie Nichols 1 Comment

Excel Formulas Showing Up as Text

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Help! My Worksheet's Excel formulas showing up as text! If you suddenly have Excel formulas showing up as text in your Excel worksheet instead of the results of the formulas, there are a couple of common causes. If you fall into one of these buckets it's a quick fix to get back to normal. Situation 1: You have formula viewing toggled on The easiest thing to try is to toggle the formula view off. You can do this by pressing the CTRL key at the same time as the "`" key. That's the backwards single quote key (in the upper left corner of my keyboard along with the tilde ("~") character. CTRL ` Pressing CTRL+` repeatedly will toggle the formula viewing on/off. Situation 2: You have cells formatted…

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…

23Oct 2013 by Charlie Nichols 2 Comments

Fuzzy VLOOKUP in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] pwrVLOOKUP. Just like VLOOKUP, only Better. You can do a VLOOKUP operation in Excel and get a fuzzy match instead of being limited to the exact match function built into Excel. To do a fuzzy VLOOKUP you need to use the pwrVLOOKUP function that is part of the Excel PowerUps Premium Suite add-in which is available as a free download on this site. Usage of the pwrVLOOKUP function is very similar to the built-in VLOOKUP function. The first 3 parameters are the same (lookup value, lookup range, and column offset). The difference starts in the fourth parameter. In pwrVLOOKUP, the fourth parameter is a strength-of-match value. It is a number between 0 and 100. If you do not provide this value, an exact match (strenth-of-match = 100) is…

30Sep 2013 by Charlie Nichols No Comments

How to Generate Random Demographic Data

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Generate Random Demographic Data Why Generate Random Demographic Data? There are several reasons I can think of that you would want to or need to generate random demographic data. They primarily center on testing scenarios. Due to the nature of the data, it may be unfeasible for you to access real data to test your application or process. You wouldn’t want to risk exposing PII (Personally Identifiable Information) or other sensitive information. Also, some of the data you may need to test would be restricted or protected under the Sarbanes-Oxley Act (SOX) and/or the Health Insurance Portability and Accountability Act (HIPAA). There are complex regulations that you don’t want to run afoul of, but most of all you want to protect your customer’s sensitive data –…

26Aug 2013 by Charlie Nichols No Comments

Date Values in Excel Explained

100 level, Excel

Date values in Excel are numbers In Excel, a date is the number of days since January 1, 1900 starting with January 1, 1900 being “1”. Each date after that, Excel adds one more number to that sequence. So August 26, 2013 is 41512, or 41,512 days since January 1, 1900. The integer part of the number is used for the days. The decimal part of the number is the fractional part of the day -- or the time. So .5 would be 50% of the way thru the day, or 12:00 noon. That makes 41,512.5 to be equivalent to 12:00 noon on August 26, 2013. The date number pattern is a follows. DDDDD.TTTTTT So as you might imagine, adding 3 to the date value of August 30, 2013 results…

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…

13Aug 2013 by Charlie Nichols 1 Comment

How to Open Excel Files in Separate Windows Automatically

400 level, Excel

I was asked how set up Excel so that opening individual Excel files would open in separate Excel Windows. By default, it seems Excel 2007 and 2010 open separate files in the same instance of Excel. In technical terms, Excel uses MDI instead of SDI. I searched and found several posts that basically said "can't be done" or "not supported". I found some others that outlined some Registry tweaks. They were slightly different in their instructions and a little hard to follow. I tried some of the changes and have documented what has worked for me on multiple systems with different versions of Windows as well as Excel. Setting up your system to open Excel files in separate windows If you have Excel 2007 or Excel 2010, you can…