|
|
Are you ready for Spreadsheet Day on Monday (October 17th)? I'm just back from a week of vacation, and will be working on my "Help a Student" template this weekend. Yes, I crossed the border this week, and spent time shopping and relaxing in Rochester NY. I toured the home of George Eastman, founder of the Kodak company, and will post some photos next week. He wasn't a spreadsheet guy, but some of his ideas can be applied to spreadsheets. Spreadsheet Day I hope you'll cell-ebrate Spreadsheet Day on Monday, and post a link to your free template, add-in or spreadsheet tip, that will help a student succeed. The Science Goddess, on the Excel for Educators blog, has asked teachers to suggest spreadsheets that would be useful to their students. As she says to her readers, "if you don't talk to your kids about spreadsheets...who will?" If you need
Continue reading Spreadsheet Day 2011 Preparations
It's hard to believe that a year has passed already, and it's only a week until Spreadsheet Day -- Monday, October 17th. Don't panic though, there's still time to organize an office party, and order a spreadsheet cake. If you have thousands of dollars in your celebration budget, you could buy a special bottle of Scotch, that is the "Excel" of the whisky world. That's out of my league though – I'll have a glass of Canadian wine instead. And please keep reading, to see how you can contribute to the celebrations. Before the Spreadsheet Back in the old days, when I went to university, there were no laptops, or spreadsheet programs. Sad, I know. Fortunately, paper had been invented by then, so I was able to take notes, without a rock and chisel. There was even a computer assignment in my Statistics class. No tapping on an iPad though
Continue reading Spreadsheet Day 2011 Challenge
Last week someone sent me an Excel file that was having problems – it wouldn't save properly, and there were a few other strange behaviours. The file had been working well for a few years, but recently started acting up. The file was used in a factory, where the technicians filled in data, and printed the file, a few times each day. To print, they clicked a button on the data entry sheet. A macro printed the data entry sheet, copied the latest data to a storage sheet, and cleared the data entry cells. Danger! When I tried to open the file, Excel 2010 warned me that the file could be dangerous – not a good sign! I ran the file through my virus scanner, and nothing malicious was found, so I opened the file in Excel 2003. No complaints from that version. What's Hiding Under There?
Continue reading Excel Worksheet Buttons Cause Problem
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.
If there are multiple
Continue reading Quickly Save Excel Files from Outlook
Recently, I saw this tweet, from someone stuck on an Excel problem:
An Excel problem I have never solved – easily copying a formula down tens of thousands of rows where the row next to it may be blank.
Yes, it's easy to copy a formula down a column, if the adjacent column is filled. Follow these steps to copy the formula down to the first blank cell in the adjacent column.
Select the cell with the formula Point to the fill handle, at the bottom right of the selected cell When the pointer changes to a black plus sigh, double-click the fill handle
Fill Down with Empty Adjacent Column
However, if the adjacent column is empty, this fill handle trick doesn't work. How can you quickly enter formulas in a column, if you're setting up a workbook, with lots of empty cells?
Here's how I do it –
Continue reading Quickly Copy Excel Formula Down
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.
When you create a Custom View, it stores the current settings for all the sheets in the workbook. You could use that to set up multiple sheets for monthly reports, and store a default Custom View, with all the sheets set with no filters applied, and all rows and columns visible.
Here are a few more tricks for you Custom Views sorcery kit. And remember, Custom Views don't work if there are named Excel Tables in the workbook.
Set the Print Area
When you create a Custom View, you can include the print settings, such as Print Area. In this PrintABCD Custom View,
Continue reading Excel Custom Views Tricks
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.
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!
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
Continue reading Excel Fortune Cookies: Friday Fun
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.
Create the Headings List
The first step is to copy all the headings to the top of the worksheet, where they'll be used for the table of contents.
Insert blank rows at the top of your worksheet, to make room for the table of contents. Click on the first heading, to select it. Press the Ctrl key, and click on each of the remaining headings, to select all of them. Click the Copy command on the Excel Ribbon Right-click in an empty
Continue reading Table of Contents for Long Excel Sheet
How bad is the economy? With FRED, the free Excel add-in from the St. Louis Federal Reserve, you can easily download and and analyze economic data.
Just download and install the FRED Excel Add-in, then select and download data. Then, start your analysis:
choose a data manipulation (i.e., growth rate) specify a date range (i.e., 1960:Q1 to 2010:Q4) aggregate data to lower frequency (i.e., weekly to monthly) search for data, and browse the most popular data series. Getting Started
If you’re not familiar with the FRED website, take a quick look at the FRED Add-in’s user guide, to help get started.
The key to pulling data is to enter a series name, so if you don’t have those memorized, select one of the Popular Data options, to get its Series ID.
Or, use the Data Search button to find the series that you need. For example,
Continue reading Excel Economic Data Add-in: FRED
It's easy to add a line break when you're typing in an Excel worksheet. Just click where you want the line break, and press Alt + Enter.
When you press the Enter key to complete the formula, the line break appears, and Wrap Text is automatically added to the cell. You might have to adjust the column width though, because the text won't flow into the next column.
Add a Line Break in a Formula
It's not quite as easy to add a line break in a formula, but it's possible!
This formula shows text, and the sum of the values in C1:C6
="Total amount is: " & SUM(C1:C6)
To add a line break between the text and the total amount, you can use the Excel CHAR function, with the number 10. Use the & operator to join the line break character to the other text in
Continue reading Add Line Break in Excel Formula
|
|
Recent Comments