Author: Debra Dalgleish

Quickly Save Excel Files from Outlook

This might be more of an Outlook tip, but I get a lot of Excel attachments in my Outlook email messages. Most of those files have to be saved, and it seems to take forever for the Save As window to open. Okay, my stopwatch says it’s about 9 seconds – but it seems like forever!

When I’m working, I usually have Windows Explorer open, so I can open and copy the files from there.

I finally discovered that I can drag an attachment directly from an Outlook email into a folder in Windows Explorer.

Drag and Drop to Save Time

For example, here’s a very important Fall TV schedule file that my daughter sent to me.

  • In Outlook, I point to the attachment’s file name,
  • Drag the file onto the Window Explorer window
  • Drop it into the folder where I want to save it.
drag and drop Excel file
drag and drop Excel file

If there are multiple attachments, right-click on one, and click Select All. Then drag all the files to Windows Explorer.

Now, instead of 9 seconds, saving an Excel attachment takes 2 seconds. Not much, perhaps, but it adds up, over the course of a week.

And I’m sure this tip works for other types of files too, but who cares about those? 😉
___________

Excel Drop Down Lists for Country and City

In Excel, you can use data validation to create drop down lists on a worksheet. Usually, a simple list is all that’s needed, to make it easier for people to enter the correct data. This example is a bit more complicated, and has 2 drop down lists, one for country and one for city.

Continue reading “Excel Drop Down Lists for Country and City”

Excel Custom Views Tricks

iconcustomviews We took a look at Excel Custom Views last week, and used them to filter data, and hide or show columns. The Custom Views make it easier to print weekly reports, with different layouts for each version – all in a single file, with no macros.

Here are a few Custom Views tricks that you can use. But remember, Custom Views don’t work if there are named Excel Tables in the workbook

Continue reading “Excel Custom Views Tricks”

Quick Reports With Excel Custom Views

In an Excel file, you might need to change the layout, before you print a report. For example,

  • in a customer report, the pricing columns are hidden.
  • for a supplier report, you filter for a specific product, and hide some columns.
  • for your internal reports, all the columns and rows are visible.

Continue reading “Quick Reports With Excel Custom Views”

Excel Fortune Cookies: Friday Fun

image Have you done this? After lunch in a Chinese restaurant, everyone grabs a fortune cookie, and reads their fortune aloud.

And, at the end of the printed fortune, you add, “in bed.” Maybe it’s just the groups that I hang out with, but we find this hilarious.

Fortune Cooke Game

This week, when typing, I added “in Excel” at the end of the line. That reminded me of the fortune cookie game, and it seemed like a great new version for the office crowd.

So, the next time you’re reading those fortune cookies, end with “in Excel” instead of “in bed”. Your co-workers will think that you’re a comedic genius (probably)!

Sample Fortunes

Here are a few sample fortune cookie fortunes, to get you started – in Excel.

  • A pleasant surprise is waiting for you.
  • Accept something that you cannot change, and you will feel better.
  • Practice makes perfect.
  • Stop searching forever, happiness is just next to you.
  • The smart thing to do is to begin trusting your intuitions.
  • You are admired by everyone for your talent and ability.
  • Your infinite capacity for patience will be rewarded sooner or later.

And if you have any other Excel enhanced fortunes, please add them in the comments.
___________

Change Excel Formula Results With CheckBox

You spent hours creating an impressive table of loan payment calculations. Different loan amounts are across the top of the table, and a variety of terms and interest rates are at the left side.

At a glance, you can see the monthly payment for any combination of variables. Sweet!

loancheckbox01

Then, your boss breaks your magical spell of awesomeness, by asking you to include the total payments for each combination.

Sure, you could copy that sheet, and tweak the formulas, or add more columns, but then the workbook is

  • double the size, and
  • twice the maintenance.

Use a CheckBox

Thanks to Dave Peterson, there’s a new tutorial and sample file on the Contextures website – Excel Formula CheckBox. Instead of duplicating your work, and creating multiple sheets, you can solve the problem with a simple checkbox.

A checkbox at the top of the worksheet is linked to cell C1. If the box is checked, C1 is TRUE, and if it’s not checked, C1 is FALSE.

The loan payment formulas are modified, to include a reference to cell C1. The the box is checked, the monthly payment is multiplied by the total number of payments. The loan payment table shows the total amount to be repaid, instead of the monthly payment.

loancheckbox02

Other Uses for CheckBox Formulas

Of course, this technique isn’t limited to loan payment tables. You can use a checkbox selector in other workbooks too — for example, let users specify if tax should be included, or check the box if they want to see prices converted to US dollars.

Do you have any other ideas for changing the formula results with a checkbox?

Download the Sample File

For the detailed instructions, please visit the Contextures website – Excel Formula CheckBox. You can download the sample file there too.
___________

Table of Contents for Long Excel Sheet

In the comments for my post on creating a table of contents in Excel, Eden asked: “Can I create a content page within one worksheet? I have one worksheet and it is very long.” Good idea! A short table of contents at the top of a worksheet would make it easy to find specific sections on a long worksheet. Here are the steps for setting that up.

Continue reading “Table of Contents for Long Excel Sheet”