Excel UserForms for Data Entry

 

30 Excel Functions in 30 Days

 

 

Contextures News

Excel Productivity Tips + More

June 23, 2015

Plan your meals, quick print, refer to tables, and many more tips, in this week's Excel news.

 -  Debra -  ddalgleish@contextures.com

Summer Schedule

Now that it's summer here in Canada, I'll publish the newsletter once every two weeks, so you will get the next issue on July 7th.

Planning Template

Last week, I updated the weekly meal planner template on my website, and you can download it here: Excel weekly meal planner The new version shows the full week across the page, and you can select up to five items for each meal. Then, click a button to run a macro that updates the shopping list, which is a pivot table.

The workbook also has lists that are used for the drop downs in the menu sheet. Other sheets have functions that count what has been selected, and pull the costs of the ingredients.

If you don't need a meal planner, you could use a similar set up for other weekly plans, such as a work schedule, or keeping track of school assignments. Just change the information on the list sheets, and you'll be able to select people, or projects, or anything else you need, in the weekly planner.

Productivity Tip - Printing

If you have several sheets in a workbook, you don't need to print them one at a time. Instead, quickly print them all at once.

Right-click on any sheet tab, and click Select All Sheets. Then, click the File tab on the Excel Ribbon, and click Print.

You'll see a preview of the sheets, and if everything looks okay, click the Print button. If the sheets have page numbers in the headers or footers, the numbers will run sequentially for all the selected sheets -- the numbering won't start at one for each sheet.

WARNING: When you go back to Excel, right-click any sheet tab, and click Ungroup sheets. If you leave the sheets grouped, anything that you type will be entered on all the sheets, and you probably don't want that to happen!

To see a short video with this tip, go to the Excel Printing page on my website.

Excel News

Here are a few more Excel articles I read this week, that you might find interesting and useful.

Quick and Easy

Quick Tips -- To help you save time when adding things up, Marie McCooey shares her quick tips for AutoSum. The tips will save you time, but I never remember to use this shortcut!

Productivity Tips -- For more ways to get things done more quickly in Excel, try these 10 productivity tips from John Micheloudis. My favourite is the Fill Down Formulas -- double-click the fill handle at the bottom right corner of the selected cell, to quickly copy that formula down to the last row of data.

Beyond the Basics

INDEX Function Examples -- John Micheloudis uses the INDEX function with other functions, to get the sum or average for a range of cells. The INDEX function is very powerful, but a bit tricky to learn. If you'd like more examples, go to the INDEX function page on my site.

What is the Need -- Dan Harrison reminds us to validate the real business requirements, before building something in Excel. What someone asks for might not be what they really need! I loved this example, because the same thing happened with one of my customers recently. It's nice to find a quick and easy solution, instead of complicated (and costly) programming.

Advanced and VBA

Table References -- Zack Barresse and Kevin Jones wrote a very comprehensive article on how to use structured referencing with Excel Tables. It covers both worksheet references, and VBA. You might not want to read the whole article, but you can bookmark it, so you can find it quickly, if you have to work with tables.

Excel is the Best -- Rob Collie, from PowerPivot(Pro), explains why Excel, and its Power BI apps, make Excel the world's best data tool. Read the comments too, for a lively discussion. Rob used to work at Microsoft, so he has experience with Power Pivot from both the developer and customer perspectives.

Photo of the Week

The rose bush in our back yard is in full bloom this week, and the bright pink flowers look beautiful. When I need a break from working on the computer, it's nice to go outside for a few minutes, and relax in the back garden. Those roses, and their vibrant colour, give me back my energy!

And Finally -- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser: http://www.contextures.com/newsletter/excelnews2015/20150623ctx.html

_____________

See more recommended Excel products.

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

__

 

Privacy Policy

 

Contextures Inc., Copyright 2017
All rights reserved.