tips and tricks Archives - Page 3 of 5

11Jun 2013 by Charlie Nichols 21 Comments

Display icons in SharePoint lists

SharePoint

[wp_ad_camp_1] [wp_ad_camp_4] How to display status icons in a SharePoint list I needed to display a KPI indicator icon in one of my SharePoint list columns. To display status icons in SharePoint lists in my case, I needed to show a Red/Yellow/Green status symbol. I couldn’t find a setting in SharePoint that made this easy as I had done in Excel and Project before. But then a colleague showed me a little trick that scratched the itch just fine. The setup You’ll need to have a field that carries a value that the user can set. In my case, I created a field called “Status” and made it a drop-down select list with the values “Red”, “Yellow”, and “Green”. Next, I created a calculated field I called “Status Indicator”. The…

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,…

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…

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…