Oct
04
2012

# Days between two dates

This is something that is needed so rarely, so I was surprised there was a trivial way to do this. All I needed to do to find the # days between two dates, in a 360 day year in excel was accomplished by this short formula, which did the trick nicely. =DAYS360(D2,B2)

0
Aug
13
2012

Dedupe case sensitive values

While writing a robots.txt file where I had been given incorrect directory names (casing was incorrect), I used this formula the other day to duplicate values after I created an addition list in all lowercase. Borrowed from teachexcel.com, but this will be one I add to my arsenal for future use cases. =IF(A2=””,””,IF(SUMPRODUCT(–ISNUMBER(FIND(A2,A$2:A2)))>1,””,”x”))

0
Aug
10
2012

Refresh all pivot tables

I didn’t realize until today, this hotkey refreshes all pivot tables in addition to normal data in a spreadsheet. Was useful for splitting up several files. CTRL+Alt+F5

0
Jul
19
2012

Week Start Date/Week End Date in excel

A lot of the time I’ll export data from a Salesforce report, web report, or maybe even a spreadsheet a coworker has given me.  As Excel doesn’t have a GREAT way to group all data by month if there is a timestamp as part of a date, Many times I’ll just add a column for [...]

0
May
11
2012

MS Excel Slicers

Math has always been a mortal enemy to many of us. Many of us can remember hurriedly penned pages and pages of calculation and formulae. Just a single error, one slight digit misplaced, and all the whole project could go down the drain. That’s why, when I was finally introduced to Excel, I hailed it [...]

0
Apr
06
2012

Selecting Many Columns

If you’re like me, you want to get as much work done with as little effort expended as possible. When working in excel, I’m sure you’ve found instances where you need to select a lot of columns or rows at once. Most of the time we need to do that because we simply want to [...]

0
Mar
12
2012

Named Cells

Once of the most useful features of excel is it’s ability to assign names to cells. This is, in my opinion, one of excel’s most underused features. It’s got a bit of a learning curve though. Using the column naming standard from A to Z is OK, but it can cause confusion when creating large [...]

0
Feb
06
2012

Autocorrect in Excel

AutoCorrect is a tool that when you type it will correct the most common mistakes that people usually make. For example, if you type “cta” the AutoCorrect function will automatically convert it to “cat”. Another benefit about it is that when you type, you can just write a few letters and it will automatically put [...]

0
Jan
02
2012

Manipulating Text in Excel

Having important data on Microsoft excel doesn’t mean only numbers and formulas. It also contains text. There are a lot of ways that you can give a spreadsheet a better layout and perceived value. If you want to master your text entry skills in Microsoft Excel then you should really learn these tricks: One way [...]

0
Nov
25
2011

Datedif – Excel’s secret function

In the post about Clippy, I mentioned that there are a few features in excel that are undocumented. Here’s one that I find pretty useful. I’m not sure of the history of it, but I know that when I was looking for a built in function to do something a few years ago, I came [...]

1