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

Excel Tools for Complex Workbooks

free navigator add-in

This week, I got an email from someone who sets up complex Excel workbooks, and has a few challenges:

Some files have so many sheets that it’s tough to find the sheet you need. Files have links to other files, and sometimes the links don’t refresh correctly It’s difficult to track formulas, and see how things are connected.

Are there any tools could help with these challenges? I suggested a few things, some free tools, and some paid tools. If you have other suggestions, please add them in the comments.

Continue reading Excel Tools for Complex Workbooks

Hide an Excel Worksheet Automatically

hide worksheet with vba

When you build a workbook for other people to use, there might be worksheets that can stay hidden some of the time. In this example, the workbook is used to create and print orders.

If the shipment is going to Canada, an Export Form needs to be printed too. For shipments to the USA, the form isn’t needed.

Continue reading Hide an Excel Worksheet Automatically

Excel Roundup 20140616

excel maze

If you still have a Commodore 64 in your basement, you could run a one-line maze generator, in Basic.

If your mom threw out that C64, years ago, you can do something similar in Microsoft Excel. On Google+, Graeme McRae posted a VBA function that will create a maze, using a random string of / and \ characters.

Here’s one that I created in Excel 2013, with a gold background, and bold font. That should keep your kids (or you) busy for a while!

Continue reading Excel Roundup 20140616

Excel Smart Art Family Tree

excel family tree

How far back can you trace your family tree? Prof. Lee Townsend, from University of Hartford, has found an interesting new way to draw her family tree -- in Excel, using Smart Art and some VBA.

The details are below, and I’m also happy to announce the winners in the Peltier Tech Chart Utility Giveaway.

Continue reading Excel Smart Art Family Tree