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.

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

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.

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

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

SHEET and SHEETS Functions in Excel 2013

sheets function

I’ve been experimenting with the new SHEET and SHEETS functions in Excel 2013, to see how they could be used.

The SHEETS function counts all the sheets in a reference The SHEET function returns the sheet number for a reference

You can see a demo of these functions in the video at the end of this post.

How Do You Use These Functions?

Have you found any practical uses for these functions? I came up with a few formulas that let you do some troubleshooting.

Continue reading SHEET and SHEETS Functions in Excel 2013

Which Excel Function Are You?

If you’ve been anywhere online in the past couple of years, you’ve probably seen those quizzes, such as Which Star Wars Character Are You? 

Now, it’s time to play a new game – Which Excel Function Are You?

We don’t need an online service to create our quiz – we can build it in Excel.

Pick the Five Functions

Most of these games have 5 or 6 possible outcomes, so I’ll pick 5 Excel functions that have distinctive personalities.

ROUND VLOOKUP MATCH EXACT COUNT

On a worksheet named Admin_Data, I created a table with a list of the

Continue reading Which Excel Function Are You?

Find Last Item in Group With Index Match

How can you use a formula to find the last item in any category, in a sorted list? Someone asked me that question last week, and I used a combination of INDEX and MATCH to find the solution.

To show how I solved it, I’ve set up a small table, with food products, sorted by category and product code. The product codes have two letters, followed by three numbers. The table is named tblProducts.

NOTE: If the product codes aren’t sorted, this solution won’t work.

Step 1: Find the Category Start

Cell B2 contains the category name

Continue reading Find Last Item in Group With Index Match

Using MAX IF With Multiple Criteria

Excel doesn’t have a MAXIF function, but we’re able to create our own version, by combining the MAX and IF functions. But how about a bigger dream? Could we create our own MAXIFS function too, with multiple IFs?

Using MAX and IF

Last week, we used MAX and IF in an array-entered formula, to find the latest date that a product price was changed. Remember to press Ctrl + Shift + Enter after typing this formula, instead of just pressing Enter.

=MAX(IF($A$2:$A$9=A12,$B$2:$B$9))

Note: For more information on array formulas, I recommend Mike Girvin’s book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.

Continue reading Using MAX IF With Multiple Criteria

Get Latest Price for Specific Product

In a previous article, we combined the MAX and IF functions, to find the highest price for a specific product.

Today we’ll use that technique to find the latest date that a product’s price was changed. Then, using the product name and that date, we can find the latest price for the product – even if it isn’t the highest price.

Product Pricing Table

In this example, there is a price list in cells A1:C9. There are three columns: Product, Date and Price.

Find the Latest Date

If we want to find the latest price for Pens,

Continue reading Get Latest Price for Specific Product

Count Items in a Date Range in Excel

If you have a list on your worksheet, and a date in each row, you can get Excel to count how many dates are in a specific date range. For example, how many Pencil shipments went out in September, based on the Ship Date column?

Use COUNTIFS and Cell References

The easiest way to find the number of shipments is to use the COUNTIFS function (Excel 2007 and later) and enter the product name, and the start and end dates on the worksheet.

In this example, there is a drop down list of products in cell A2. The

Continue reading Count Items in a Date Range in Excel