2013 - Page 3 of 6 - Office PowerUps

30May 2013 by Charlie Nichols 14 Comments

How to create a resource utilization chart in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] "Resource utilization charts" The name I use to refer to these charts is “resource utilization chart”. I use this name because that’s what I’ve come to know them as within Microsoft Project. They may have another, better suited name. If so feel free to note that in the comments below. Edit: Resource utilization histogram Going for a PMP certification? You'll need A Guide to the Project Management Body of Knowledge: PMBOK(R) Guide. Strategy for creating the resource utilization chart The general strategy here is to use a stacked column chart, and define a pair of calculated fields from the data that will actually be charted. I explain that more below, and use a scenario that may be similar to one of yours to help make it clear. So,…

28May 2013 by Charlie Nichols No Comments

Seamlessly install and deploy your Excel XLL add-in

Development, Excel, Utilities

[wp_ad_camp_1] [wp_ad_camp_4] Automatically install Excel Add-In Once you've created your Excel add-in you'll want to get it installed on your customer's instances of Excel, right? Excel add-in deployment can easily be automated using the Excel Add In Automatic Installer. As the name suggests, you can automatically install Excel add in XLLs and XLAs with this setup extension. Excel add-in deployment There are many web pages that give an overview of the steps that would need to get automated for an Excel add-in deployment - so it's doable. I spent a bunch of time culling through the advice that worked, and the advice that didn't work so well for my own Excel add-in deployment tool. I've repackaged that work into a utility that can help make your add-in get installed seamlessly. Save…

24May 2013 by Charlie Nichols 1 Comment

Fuzzy search in Excel to find similar text values in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Sometimes you have a need to compare text strings that don't exactly match. You might need to match "(425)555-1212" to "4255551212" for example. Or perhaps you'd like to match "12345 Main st" to "12345 main street". The Excel PowerUps Premium Suite add-in (available as a free trial download) includes a function that helps you do just that by enabling a fuzzy search in Excel. Fuzzy Search and Replace If you want to do a fuzzy search and replace you can use the Fuzzy Find and Replace tool. You can see an example of it's use here. Fuzzy Search in Excel with a New Function for your Formulas The function is called pwrSIMILARITY. It simply compares the two text strings and returns a percentage value that represents how similar the…

24May 2013 by Charlie Nichols No Comments

How to generate a random date between two dates

300 level, Excel

How do you generate a random date between two dates? I was asked how to generate a set of random dates between two dates. At first glance, it didn't look like Excel had a function that would easily allow that. However, it turns out that using the RANDBETWEEN function is perfectly suited for this. Sample formula You will use the DATE function to specify your start and end dates for the range. Let's say you want to generate a date between January 1, 2013 and December 31, 2013. The function would look like the following. =RANDBETWEEN(DATE(2013,1,1),DATE(2013,12,31)) How does this work? This works because the DATE function returns a number that represents the date value in Excel. Then RANDBETWEEN generates a random number between those two date values (or numbers). The…

24Apr 2013 by Charlie Nichols No Comments

How to use Excel’s conditional formatting feature to detect potential typos

100 level, Excel

Do you need to quickly identify values that are unique from a list within Excel? One reason for uniqueness of a value may be it's misspelled or there has been an entry or typographic error on the data. Excel makes it really easy with its conditional formatting feature. It takes just a few clicks. First, just highlight the values that you want to detect unique values, possibly misspellings or typos in. Next, click Conditional Formatting up in the ribbon. Then click Highlight Cells Rules, then click Duplicate Values. Change the value "Duplicate" to "Unique" in the dialog box that pops up next. Then click OK. Voila -- the unique values in your list are highlighted for you. There you go.

22Apr 2013 by Charlie Nichols No Comments

How to quickly and easily detect duplicate values in an Excel list

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Detect Duplicates in Excel Column Do you need to quickly detect duplicates in Excel columns? Excel makes it really easy with its conditional formatting feature. It takes just a few clicks using built-in capabilities of Excel. Here is a quick overview to see first hand how to do it. First, just highlight the values that you want to detect duplicates in. You will need to be sure to highlight the range because otherwise you'll be trying to detect duplicates within a single cell which will not work. Also note that the matching will be done for the entire cell rather than a subset or substring within the cell. Next, click Conditional Formatting up in the ribbon. Then click Highlight Cells Rules, then click Duplicate Values. You can just…

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…

12Apr 2013 by Charlie Nichols No Comments

How to locate the last cell of your worksheet

100 level, Excel

You might find that you’re worksheet prints a bunch of extra pages for no reason, or you wind up with an Out of Memory error message, or you may have an abnormally large file or other weirdness. This might just be because there is some information or formatting in a distant cell that you no longer need or was accidentally set. To find that last cell to take a look press the following keys together. CTRL SHIFT END If you find unneeded info or formatting in that cell, or you wind up way beyond that end of the data you’re using you can clear out the excess rows or columns to make Excel consider your worksheet smaller. To find out how, see how to clear unwanted rows or columns. There…

12Apr 2013 by Charlie Nichols 1 Comment

How to reset or clear the last cell in Excel to make your worksheet smaller

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] If you find yourself needing to clear out a bunch of unwanted rows and/or columns in Excel you can do that pretty quickly with the following 2-phase approach. Phase 1, clear unwanted columns Highlight the first of the columns that you want to clear out. This will be the first one to the right of the edge of the data you care about. Highlight the column by clicking on the column header (the letter name of the column). Press the following keys together. This will highlight the entire remaining right side of your worksheet. CTRL SHIFT RIGHT-ARROW Now click Clear All. You'll find this option on the Home tab on the ribbon, in the Editing group, in the Clear item drop down list. Phase 2, clear unwanted rows…

12Apr 2013 by Charlie Nichols No Comments

How to get more out of Excel’s Auto Fill feature

100 level, Excel

Quick basics Excel has a feature called Auto Fill that lets you start a series and then Excel can automatically fill in the rest of the cells by either repeating the series, or incrementing or decrementing some value. If you haven’t used it before, it can save a bunch of time with your data entry. To use the feature just highlight a bunch of cells, grab the fill handle, and drag your selection to more cells. The fill handle is that heavy dot that shows up in the lower right-hand corner of your selection. Getting more Excel has a number of Auto Fill options available, depending on the type of data you have in your selection. The options are listed below. Copy CellsFill SeriesFill Formatting OnlyFill Without FormattingFill DaysFill WeekdaysFill…