|
|
By Debra Dalgleish, on April 18th, 2013 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
By Debra Dalgleish, on April 16th, 2013 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
By Debra Dalgleish, on April 11th, 2013 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
By Debra Dalgleish, on April 9th, 2013 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
By Debra Dalgleish, on April 4th, 2013 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
By Debra Dalgleish, on April 2nd, 2013 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
By Debra Dalgleish, on March 28th, 2013 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
By Debra Dalgleish, on March 26th, 2013 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
By Debra Dalgleish, on March 21st, 2013 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
By Debra Dalgleish, on March 19th, 2013 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
|
|
Recent Comments