Excel Archives - Page 4 of 9

24May 2014 by Charlie Nichols No Comments

Work Beyond Excel’s 32k Per-Cell Character Limit

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] The Character Limit in Excel can be Limiting - Don't let it be There is a character limit in Excel. Excel has a character limit (per cell) of 32,767 characters. And in most cases this is ample space for your needs. Sometimes You Need to Work with XML Blocks Larger Than 32k. You may have a need that resembles one of the following scenarios. You need to consume a web service that returns more than the 32k limit. You need to open a local XML file, and that file may also be larger than the 32k character limit. Meet New and Improved WEBSERVICE and FILTERXML Functions Each of the functions, including their “power” equivalent functions of pwrWEBSERVICE and pwrFILTERXML, has been enhanced in the latest version of the Excel…

28Apr 2014 by Charlie Nichols 8 Comments

How to Bulk Import to SharePoint from Excel with a Choice Field

400 level, Excel, SharePoint, SharePoint Designer, Workflow

[wp_ad_camp_1] [wp_ad_camp_4] Scenario: Import to SharePoint from Excel Do you have a large SharePoint list with dozens, hundreds, or even thousands of items? And, are some of the columns in your list choice fields based on lookup values from other SharePoint lists? If you have data from an Excel file that you need to import to SharePoint from Excel, you’ll quickly find that a straightforward copy and paste operation won’t cut it. However, with a bit of prep work you can absolutely do a bulk import of your data without having to write code, or buy a special add-in. Setting up for the Import You will need to do a bit of prep work before you can do the actual import. But this setup work is trivial compared to trying…

24Mar 2014 by Charlie Nichols No Comments

Excel add-in update: version 1.6.3 of Excel PowerUps is Available – Get the update today

100 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. New Functions SKEW.P SKEW.P was added to match the Excel 2013 function of the same name. SHEET SHEET was added to match the Excel 2013 function of the same name. SHEETS SHEETS was added to match the Excel 2013 function of the same name. pwrISREGEXMATCH pwrISREGEXMATCH will identify a regular expression match within a string. pwrREGEXREPLACE pwrREGEXREPLACE will replace text identified by a regular expression with text provided in the function call. Enhanced Functions pwrISBROKENURL pwrISBROKENURL added identification of bad URLs by Title or URL pattern. Now you can…

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…

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…