Keeping Track of Garbage in Excel

Keeping Track of Garbage in Excel

I’ve worked with “garbage” data many times in Excel. You know what I mean – data that is so messy it takes you hours (or days) to clean it up. This week though, I did a different type of garbage tracking -- an Excel waste collection schedule.

Maybe I went a little overboard, but you’ve probably done that too, right? Anyway, here’s how I spent way too much time on an Excel project, but had fun doing it. Don’t judge! What weird Excel projects have you done?

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Keeping Track of Garbage in Excel

Excel Lookup With Two Criteria

Excel Lookup With Two Criteria calculations

If you need to get a product price in Excel, you can use VLOOKUP or INDEX/MATCH to get the price from a lookup table, based on a product code. But what if you have two pieces of information, such as a product name, and a size, and you want to find the price based on that information? How can you do an Excel lookup with two criteria?

(adsbygoogle = window.adsbygoogle || []).push({});

Continue reading Excel Lookup With Two Criteria

Enter Complex Excel Formulas Fast

Enter Complex Excel Formulas Fast

On Chandoo's blog, Hui is running a series of articles with Excel Tips. There were some great tips in this week's list, but my favourite one was down in the comments section – how to enter complex Excel formulas fast.

I've been using Excel for a long time, and I've never seen this suggestion before. And it's a real time saver!

Continue reading Enter Complex Excel Formulas Fast

Choose From a List to Change Excel Data

TEXT function select language

Do you need a quick way to show different data in a worksheet? For example, on an order form, you could let people select a region, and automatically include the shipping cost or tax rate for the selected region. I added an English/French selector to a workbook last week, so a formula would work correctly, in either language.

Continue reading Choose From a List to Change Excel Data

Excel Sheet Selector With No Macros

If you're setting up a workbook for other people to use, they'll appreciate it if you make it easy to move around in the file. You can create a table of contents on the first sheet, and that will get them off to a good start. But then what?

Once they're on one of the other sheets, how can they get back to the table of contents, or go directly to a different sheet? We'll add a drop down list of sheets, to make it easy to go to the one that you need.

Continue reading Excel Sheet Selector With No Macros

Excel VLOOKUP Sorting Problem

VLOOKUP Sorting Problem http://blog.contextures.com/

You can use an Excel formula to pull data from a lookup table – for example, enter a product name, and automatically see its price. Be careful though, or things can go horribly wrong, and you'll end up selling things at the wrong price.

In this example, I used the VLOOKUP function to show what can go wrong. The same thing can happen with other functions too, such as an INDEX/MATCH formula. In fact, its more likely to happen there!

Continue reading Excel VLOOKUP Sorting Problem

Customize Weekends With Excel WORKDAY Function

Project End Date with Excel WORKDAY.INTL http://blog.contextures.com/

If you're trying to figure out when a project will be completed, you can use the WORKDAY.INTL function (Excel 2010 or later). It's more flexible than the older WORKDAY function – it doesn't assume that you work Monday to Friday any more!

Continue reading Customize Weekends With Excel WORKDAY Function

Create a Rolling Total in Excel

Create a 12 Month Rolling Total http://blog.contextures.com/

It's easy to create a running total in Excel, and show the accumulated amount in each row. You can use the SUMIF function in a table, or show running totals in a pivot table.

But what if you want to show the total for a specific number of previous months – not all the previous months?

Continue reading Create a Rolling Total in Excel

Hide Formula Results Based on Date

Hide Formula Results Based on Date http://blog.contextures.com/

My challenge this week was to show the running total in a list, but only for the current date and earlier. I wanted rows for future dates to appear empty, but have the formulas in them – ready to go. How would you solve that problem?

Continue reading Hide Formula Results Based on Date

Count Items Based on Another Column

Count Items Based on Another Column http://blog.contextures.com/

How can you count items in one column, based on a criterion in a different column? We've shipped orders to the East region, and want to know how many orders had problems (a problem note is entered in column D). COUNTIF sounds like the right function to use, but it doesn't work for this problem.

Continue reading Count Items Based on Another Column