Learn how to create Excel dashboards.

Categories

30 Excel Functions in 30 Days

 

Archives

Click to Move Excel List Items Up or Down

Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.

Here’s how the tool works:

Click a button to select a group of cells that you want to work with, like this list of months. Then, click the up and down arrows, to scroll the list through the selected cells. When you’re done, click the Exit button, to “release” the scroll area.

In this example, the scrolling list is in cells D8:E19 – months and numbers. The original list had January at the top, and February

Continue reading Click to Move Excel List Items Up or Down

Create a Running Total in an Excel Column

If you’re using a pivot table, there are built in features that lets you show a running total, or a percent running total.

If you’re using worksheet formulas, instead of a pivot table, there’s nothing built in that will automatically create a running total for you. Fortunately, with a simple SUM formula, you can calculate the running total in each row, to see how your bank account is doing.

Use the SUM Function

In this example, there are withdrawal and deposit amounts in cells C2:C6. We could use the SUM function to total the amounts in column C, and

Continue reading Create a Running Total in an Excel Column

Highlight Duplicate Records in an Excel List

With Excel's conditional formatting, you can highlight cells based on specific rules. There are some built-in rules available, and you can use formulas to create your own formatting rules.

Highlight Duplicates

In this example, we want highlight the duplicate rows in a table. There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.

So, we’ll create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.

Concatenate the Data

In the sample data, there are two identical rows,

Continue reading Highlight Duplicate Records in an Excel List

Create an Excel Table from a List

When you create a list in Excel, do you automatically convert that list to a formatted table?

If not, you’re missing out on one of Excel’s best features.

Formatted tables are easy to set up, and make it easy to sort, filter, format, and expand your list. You can refer to the table’s fields in your formulas, and the reference will adjust automatically, if you add or remove data.

Set Up Your Data

There are a few things to check, before you turn your data list into a table. For example:

Add a heading in each data

Continue reading Create an Excel Table from a List

Create a Dynamic Hyperlink

Last week, I heard from Kevin Lehrbass, who runs the My Spreadsheet Lab website. Kevin has posted an Excel video on YouTube, that shows how you can make a dynamic hyperlink, using array formulas.

Select a country from a drop down list, and click on the hyperlink to go to the selected country’s largest city. Well, you’ll go to that location in the table – you won’t actually end up in the city!

Select a Country

At the top of the worksheet, Kevin created a drop down list of countries, by using data validation. You’ll start by selecting a city

Continue reading Create a Dynamic Hyperlink

Create a Drop Down List With Symbols

To make data entry easier, you can create a drop down list in an Excel cell, using data validation.

This works well with a list of words, but the list doesn't show symbols if you create the list in a font such as Webdings. In the example below, I entered 5, 0, and 6 in cells B2:B4 and formatted the cells with Webdings.

The drop down list shows the number values, instead of the formatted symbols.

Use Built-In Symbols

You can’t create a drop down list of symbols based on formatted cells, but you can use some well-hidden

Continue reading Create a Drop Down List With Symbols

Show RSS Feeds on Excel Worksheet

As you’ve heard, Google Reader will be disappearing in a few months <sigh>, and we’ll have to find other ways to follow our favourite blogs. I’m looking for a replacement, but haven’t found anything perfect yet. How about you?

In the meantime, I can at least list a few RSS feeds in Excel, by using some built-in tools. In the video below, you can see how I created a list of articles from this blog, using an XML map.

Create the RSS Feed List

It’s easy to create an RSS feed list on a worksheet, as long as you

Continue reading Show RSS Feeds on Excel Worksheet

Go Back to Previous Locations in Excel

In Excel, you can create named ranges, and go to those ranges by selecting a name from the Name Box.

In addition to those permanent names, you can also store up to 4 temporary locations, and to back to those locations quickly. This is handy if you’re making updates to a big workbook, and want to switch between a couple of sheets quickly, without lots of scrolling.

Temporarily Store a Location

Here are the steps for storing a location:

Select a cell on the worksheet Click in the Name Box, which is to the left of the Formula Bar

Continue reading Go Back to Previous Locations in Excel

Limit Date Range with Excel Data Validation

Yesterday, one of my clients emailed to let me know that she was having trouble entering January dates in a file that I had created. My first guess was that there was an issue with the regional settings, because her company uses the dd/mm/yyyy format.

But when I tried entering a January date, with my mm/dd/yyyy settings, I got an “Invalid date” message too.

Limit Dates with Data Validation

The date that I had entered – 1/3/13 – was a valid date and in a valid format, so I checked the data validation settings. And that’s where I found

Continue reading Limit Date Range with Excel Data Validation

Excel Average Based on Multiple Criteria

In Excel, you can use the SUMIF and COUNTIF functions, to sum and count values, based on criteria. Did you know that you can also calculate an average, based on criteria?

Average with One Criterion

If you only have one criterion, you can use the AVERAGEIF function. In the screen shot below, the average quantity is calculated for any orders where Pens were sold.

=AVERAGEIF(A1:A10,"Pen",B1:B10)

Average for Multiple Criteria

If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function.

In the next screen shot, the AVERAGEIFS

Continue reading Excel Average Based on Multiple Criteria

Related Posts Plugin for WordPress, Blogger...