Charlie Nichols, Author at Office PowerUps

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…

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…

10Apr 2013 by Charlie Nichols No Comments

How to determine whether a given year is a leap year

300 level, Excel

Identify a leap year in Excel: We all know what a leap year is. Every 4 years (almost) we get a February 29th. The ‘almost’ is the key thing. There are some conditions (none of which most of us will see in our lifetimes) where a leap year may get skipped. Here’s the algorithm for the determination that I found on Wikipedia. You can also read up on the history and such of the leap year there as well if you like. If the Year is divisible by 400, it IS a leap year.Otherwise if the year is divisible by 100, it’s NOT a leap year.If none of the above, and the year is divisible by 4, it IS a leap year.Otherwise it is just NOT a leap year. How…

09Apr 2013 by Charlie Nichols 3 Comments

How to find the number of months between two dates in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Number of Months Between Two Dates? How many months are between two dates? The answer is “it depends” because your real question might be one of the following: How many months are between two days where the number is expressed as a fractional number?How many months are between two days where we’re only counting whole months?How many months are between two days rounding half months up to the nearest whole month?How many months are between two days where a fractional month counts as a whole month?How many months are between two days where days in any given month count as a whole month?As we’ll see below, the answer to each of the questions is different. And in your case, the specific question you’re really asking may be one…

06Apr 2013 by Charlie Nichols No Comments

An Excel function to calculate your dog’s or cat’s age

400 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Dog years, explained Do you think one person year equals seven dog years. I did, until doing some research. I wanted to add a “fun” conversion factor for a conversion function in an Excel add-in I've created. What I learned is that the seven year equivalent value is a pretty poor approximation of a dog’s equivalent age. Dogs do mature more quickly than people, but when they are young they mature at an even faster rate (relative to people) than they do when they are older. This post describes some capability you only get by installing an add-in for Excel. The add-in used here is fully functional and free during its trial period - so you'd have plenty of time to try this out. The add-in is called PowerUps…