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

Excel Roundup 20140811

101 excel formulas

Does Ed Bott include your favorites in his list of 6 Excel power tips?

He included one of my favourites – Paste As Values. However, I usually Paste Values by dragging with the right-mouse button pressed, instead of using a keyboard shortcut. You can see that technique in the very short video below. Do you use the mouse or keyboard for Paste As Values?

Or watch on YouTube: Change Excel Formulas to Values With Mouse

Continue reading Excel Roundup 20140811

Top 5 Items in Advanced Filter List

advanced filter top 5

When you try to use the Top 10 filter, on a list that already has some filters applied, the results probably won’t be what you want. The Top 10 feature ignores the filters on other columns, and just returns values that are in the overall Top 10.

Recently, I showed a workaround for that problem in this blog post: Top Ten Values in Filtered Rows. In that example, I added a new column, and used the SUBTOTAL function to show the value, then filtered that new column. Hidden rows would have a value of zero, thanks to the SUBTOTAL function,

Continue reading Top 5 Items in Advanced Filter List

Excel Roundup 20140728

Power Query for Power BI and Excel

The Office 365 Garage Series has a video on what’s new in the Office Apps, and they demonstrate some cool new features. The Excel App is mentioned at the 7:12 mark, when they look at the new APIs for formatting.

However, at the 3:12 mark, they downplay the old development methods, including VBA and COM, and rave about the new methods -- HTML and JavaScript, that are used for the online versions of Office.

Apparently it’s almost impossible to find people who are still working in the older development platforms. What do you think of the chart they use to

Continue reading Excel Roundup 20140728

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

Excel Roundup 20140714

Excel Tables Complete Guide

Are you using Power Pivot or any of the other Power BI tools in Excel 2013? If you’re not sure where to start, there is a new series on the Power BI blog, that will help you dive into the new BI and visualization features.

The series outline is here, and it will have links to all the articles, as they are added to the blog. The first article is Getting Started with Power Query – Part 1, and it features the 15-minute video shown below. You’ll need Excel 2013, and the article has a link to download the Power

Continue reading Excel Roundup 20140714

Add New Items to Excel Drop Down List

add new item to drop down list

There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.

When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.

Continue reading Add New Items to Excel Drop Down List

Add Number to Multiple Cells in Excel

Add amount to multiple cells

If you’ve already entered a number in a cell, or a group of cells, what’s a quick way to add something to that amount?

For example, I keep track of my To Do list in a workbook, and one of my items is “Daily Admin tasks”. Sometimes, I start the day by answering client emails, posting links to my latest blog post, and doing the accounting for the previous day’s sales.

So, I enter the time spent – 0.75 hours – and move on to the next task.

Continue reading Add Number to Multiple Cells in Excel

Summer Schedule 2014

stop and smell the roses

Happy Summer! I hope you’re enjoying fabulous weather, and you get away from your Excel workbooks occasionally.

The posting schedule will be a bit lighter here at Contextures, over the summer months. That way, you can go on vacation, and not miss anything!

Summer Schedule

Don’t worry though – you’ll still find plenty of Excel information, to keep you up to date. Here is the posting schedule:

Monday – bi-weekly – Contextures Blog – Excel Roundup Tuesday – bi-weekly – Excel newsletter to subscribers Wednesday – bi-weekly – Pivot Table Blog – pivot table article Thursday – bi-weekly – Contextures

Continue reading Summer Schedule 2014

Excel Roundup 20140623

sql server download links

Have you ever tried to find and download a file on the Microsoft website? If so, you might sympathize with Long Zheng, who described the painful process of downloading SQL Server Express.

“The Microsoft SQL Server team has many goals. One of them is to create an industry-leading, high-performance, scalable and resilient database software. The other is to make said-software difficult to download.”

There’s a happy ending though – Scott Hanselman has created a page with download links, so you can find and download the files that you need.

Continue reading Excel Roundup 20140623