Excel Archives - Page 7 of 9

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…

05Apr 2013 by Charlie Nichols 1 Comment

Open xlsx files in Excel 2003

100 level, Excel

Do you still have Excel 2003, but somebody sent you a file from Excel 2007 or later? Need to open xlsx files in Excel 2003? Good news, you're not stuck - you're in luck. You can open xlsx files in Excel 2003 To open xlsx files in Excel 2003, Microsoft has an upgrade package called the Office Compatibility Pack that lets you open files in later versions of the Office apps. That's cool, because it saves you from having to upgrade the copy of Excel you have just to work with the new file formats. One thing you don't get is the new functions that might be used in the newer files. These would be worksheet functions that were added as part of the newer version of Excel. Here's a…

05Apr 2013 by Charlie Nichols No Comments

How to turn on the Developer tab in Excel

100 level, Excel

By now I'm guessing you've read some instructions that have asked you to click the Developer tab in Excel. But, you don't see it right? You need to turn on the Developer tab in Excel. Here are the few simple steps to enable the tab. There are steps for both Excel 2007 and 2010. Microsoft moved the option around a bit between versions. Steps to turn on the developer tab in Excel 2007 Click the Office Start button, then click Excel Options. Click on the Popular section in the left navigation, and then click the checkbox for Show Developer tab in the Ribbon. Click OK. The above will show the developer tab in Excel 2007 for you. Steps to turn on the developer tab in Excel 2010 Click File, then…

05Apr 2013 by Charlie Nichols No Comments

How you can use the Histogram tool in Excel

400 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] First off, what's a histogram? A histogram is just a chart that shows counts of items in discrete buckets. In other words, a bar chart. The histogram tool in Excel lets you define the groupings or buckets. Or, in the terms of the histogram you get to define the Bins. A standard bar chart in Excel will just chart the individual distinct values, which is often not what you need to do. Using the histogram tool you can have Excel automatically count the items in the various Bins you define, and then chart those. Note: You may have also heard of a Pareto chart. That's just a histogram sorted from highest count to lowest. Here's an illustration to highlight the difference Imagine you're a teacher and you need…

04Apr 2013 by Charlie Nichols 1 Comment

How to enable the Analysis ToolPak Add-in in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Lots of cool analysis capabilities are available when you enable the Analysis ToolPak in Excel. Here are the few simple steps to enable the features. Just which set of steps you use will depend on which version of Excel you have. Excel 2007 steps to enable the Analysis ToolPak Click the Office Start button, then click Excel Options. Click on the Add-ins section in the left navigation. Make sure Excel Add-ins is showing in the Manage drop-down box, then click Go. The Add-in list will show up. Click the checkbox next to Analysis ToolPak. Click OK. The above will enable the Analysis TookPak in Excel 2007 for you. Excel 2010 steps to enable the Analysis ToolPak The steps for Excel 2010 are basically the same, but the windows have only a slightly different…

04Apr 2013 by Charlie Nichols No Comments

Simple explanation of the Excel LOOKUP function

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel has a number of functions that will look up a value based on finding a corresponding value in a range, row, or column. Meet LOOKUP The Excel LOOKUP function matches a value you're looking for in a row or column and then returns the value from another matching row or column. I tried to create a visual to help. Part 1 in the image is LOOKUP finding the value you're looking for (represented by the "?"). Part 2 is LOOKUP finding the corresponding value in the column or row you specify. So the value returned (shown in red above) is the value you were looking for based on the initial value you were matching. Note that in the picture above, both looking up by row and by…

04Apr 2013 by Charlie Nichols No Comments

How to find values in one set that are duplicated in another set in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Ok, you have a set of values in a column or a row and you want to find out if any of those values exist in another list (which could be in either a row or a column elsewhere). You can do that in Excel by using the MATCH function to test for a specific value in another range. We’ll look at that below. Worksheet setup First, let’s set up a worksheet example. You can replace with any values you like of course. Here’s a list we want to find duplicates from. Think of this as your ‘Test’ or source list. Red, Pink, Purple, Blue Here’s a list that we want to look into. Think of this as our ‘Master’ list. Red, Orange, Yellow, Green, Blue, Indigo, Violet…

04Apr 2013 by Charlie Nichols No Comments

How to change the case of your text in Excel

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Three functions to change the case of text in Excel You can easily change the case of text in Excel. Unfortunately, Excel doesn't support an in-place way of converting text from one case to another as you can do in Word, Excel's Office sibling. Out of the box, Excel provides a few worksheet functions that give you a basic level of conversion. But since they're functions, you can't change the text in place. You need to put the new value in another cell or use in another formula. Here are three text case conversion functions you can use. UPPER LOWER PROPER UPPER and LOWER do what you'd think. They convert to all upper or lower case text. PROPER will add an initial capital letter at the beginning of…