Compare Excel Worksheet Values and Formulas

Compare Cell Values and Formulas in Excel http://blog.contextures.com/

If Excel sheets are set up identically, you can create 3-D formulas, to sum a specific range, in a group of sheets. (You can use other functions in a 3-D formula too, such as Average, Count, Min or Max.)

For example, this workbook has sheets for the East, Central and West regions, and all three sheets are set up the same.

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

Continue reading Compare Excel Worksheet Values and Formulas

Counting in Excel

7 Ways to Count in Excel http://www.contextures.com/xlFunctions04.html

The most popular function in Excel is SUM, based on my experience. That's probably the first function you learned how to use, and in the old days, it was the only function that you could insert with the appropriately named AutoSum button.

Sometimes we overlook how much counting we have to do every day too. Here are a few scenarios that you might encounter at work (or should I say "en-count-er"?):

Sales: number of orders for a specific product, from a list in a worksheet Human Resources: count of employees with 10+ years employment Shipping: number of shipments pending,

Continue reading Counting in Excel

How to Count in Excel

Learn 7 ways to count in Excel, with function tips and tricks http://blog.contextures.com/archives/2015/03/26/how-to-count-in-excel/

There are lots of different ways to count things in Excel – maybe you need to count the numbers in a column, or all the data, or just the blank cells. Fortunately, there is a function for each of those:

COUNT COUNTA COUNTBLANK

For example, to count the blank cells in the range A1:A5, use the following formula in cell A7:

=COUNTBLANK(A1:A5)

More Complicated Counting

If you have more complicated things that you need to count, there are other functions to do the job:

COUNTIF COUNTIFS SUBTOTAL AGGREGATE

For example, to count only the visible numbers, after filtering and/or

Continue reading How to Count in Excel

Ignore Outliers with Excel TRIMMEAN

Excel TRIMMEAN function http://www.contextures.com/excelaveragefunctions.html

From what I've seen in workbooks over the years, SUM is the most frequently used Excel function, and AVERAGE is the runner-up. Would you agree, or do you see other functions used more often than those two?

Last week, a client asked about excluding some of the highest and lowest numbers from a data set, to give a better average. It looked like some of those outliers had been incorrectly entered (an extra zero, or a mistyped numbers), and they were throwing off the results.

So, we did some tests with the TRIMMEAN function, to compare the results with a

Continue reading Ignore Outliers with Excel TRIMMEAN

Football Fun in Excel

This Sunday is Super Bowl XLIX – the last one that will start with "XL".  Next year, it will be Super Bowl L – that doesn't exactly roll off the tongue!

In 2011, I showed how you can use the ROMAN function in Excel, to change a number into a Roman numeral. That year, they were playing for the 45th time, which was XLV.

=ROMAN(A2)

Other Football Functions

There are other Excel functions that sound like they could be used in a football game. Here are a few, and I'm sure you can think of others:

CONVERT RECEIVE YIELD

Continue reading Football Fun in Excel

Count Activities in a Date Range

guests date range http://blog.contextures.com/

Today's challenge is to count how many guests stayed at a hotel, in a specific date range, based on the guest arrival and departure dates.

Continue reading Count Activities in a Date Range

Create Random Text in Excel

create random text in Excel http://blog.contextures.com

Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.

I needed 2 types of data:

Numbers: sample test scores in one column Text: random Region names and Gender in other columns.

Continue reading Create Random Text in Excel

Set Row Height With REPT Function

set minimum row height

Thanks to AlexJ for suggesting a great use for the REPT function – setting a minimum row height. He uses this technique to add a bit of spacing in his tables, so they’re easier to read.

You can watch the steps in this video (or watch it on YouTube), and the step-by-step instructions are below the video.

Add Space in an Excel List

For example, here is my To Do list, with a few items to work on, around the house. Most of the Task Descriptions are short, and fit in a single line.

Continue reading Set Row Height With REPT Function

Auditing Excel Formulas Quick Tips

closed door brock's monument

For the past couple of weeks I’ve had lots of trouble with this blog. It’s been up and down, and I’m sorry if you’ve come to visit, and found the doors closed! The sturdy-looking door shown below is at Brock’s Monument, in Niagara-on-the-Lake, which I visited earlier this month.

So, I’ve turned off a few things on the blog, like subscribing to comments, to see if that helps. Maybe Dick Kusleika, at Daily Dose of Excel, has some tips for me, if he’s sorted out his blog problems.

Continue reading Auditing Excel Formulas Quick Tips

Create IFRAME Code in Excel

iframe code in Excel

If you want to embed a YouTube video on your website, YouTube will create some IFRAME code for you. If you want to embed content from somewhere else, you might have to create your own IFRAME code, or find a free generator somewhere in the interwebs.

But now you won’t have to wander the Internet hallways, looking for a solution, dodging scam artists at every intersection. Instead, you can download my sample file, and create your code in the safety of your own Excel file.

Continue reading Create IFRAME Code in Excel