Tag: spreadsheet day

Do you have your party plans finalized? Remember, tomorrow, October 17th, is Spreadsheet Day, in honour of the date that VisiCalc was first shipped.

Last year, the theme was spreadsheets for students, and I posted a student time tracker in which you can plan your projects and track your class and lab hours.

In 2010, I posted my top 5 Excel tips, that I had seen posted on Excel blogs over the previous year. One of those tips was Jon Peltier’s tutorial on making vertical bullet graphs.

Top 5 Excel Tips for 2012

This year, I’m going back to the top 5 theme. Every week, in my Excel News email, I link to interesting articles that I’ve found on other blogs. Some are simple tips, others are more complicated, and some are food for thought.

Below, in no particular order, are my favourites from those articles. And if you’re not on my Excel News mailing list, please add your name, by using the form at the top right.

VBA Conditional Formatting of Charts by Value and Label

Jon Peltier, who creates time-saving Excel chart utilities, shared his technique for building charts with conditional formatting that is based on the values and labels.

In the screen shot below, the Beta bar is dark red, because its value is high, and the Alpha bar is very light blue, because its value is low.

Force Clients to Enable Macros

If you’re creating automated workbooks for other people to use, you might run into problems if those people don’t click the button to enable the macros. That macro warning can be easy to overlook, or it might not even appear, if security level is set to High.

[Bacon Bits blog is no longer online]

Mike Alexander, in his Bacon Bits blog, explains how he solves the problem, by Forcing Your Clients to Enable Macros. Users can’t miss the giant message in his workbooks, and they can’t do any work until they enable macros.

Who Says the Ribbon Is Hard?

Bob Phillips convinces us that it’s easy to make dynamic changes to the Excel Ribbon. In his article, Bob explains how to use Excel VBA to change the Ribbon commands.

He shares his sample code, and you can click the link to download his sample workbook. The link opens as an Excel Web App, so click the File tab, then click Download a Copy, to save a copy on your computer.

Create an Interactive Sales Chart

Chandoo posts hundreds of great Excel tips on his blog, so it was hard to pick just one for this list of favourites. However, I finally selected this interactive sales chart example, because it incorporates several useful techniques.

You can use Chandoo’s example to build your own dashboard with a dazzling interactive chart. Download the sample file, and poke around in the code, to see how it works.

Microsoft Excel Check List Template

On his Clearly and Simply blog, Robert Mundigl has created an Excel template for a structured Checklist. It gives you the option to check and uncheck by double clicking.

That’s a feature that many people like, so you can use download the Microsoft Excel Check List Template and use the same technique in your workbooks.

What Are Your Favourite Excel Tips?

It was tough to pick a top 5 Excel tips, and I’m sure there are many other tips that you found. If you have favourite articles from the past year, please share a link in the comments below.

Happy Spreadsheet Day!

____________

Monday, October 17th, was Spreadsheet Day, and I hope you’ve recovered from all the festivities.

Thanks to everyone who tweeted about Spreadsheet Day, and a special thanks to those who made a Spreadsheet Day post on their blog. In case you missed any of the posts, here’s a list. If I omitted yours, please let me know.

Plan and Track Student Spending

Pivot tables summarize the key information in this student budget workbook, from Bob Ryan, of Simply Learning Excel.

Student Time Tracker

Keep tabs on class times and assignment workload, with my Student Time Tracker. Then, when you become a Microsoft Office consultant, you can use the same technique to track meetings and project work.

Happy Spreadsheet Day

A lesson in the perils of gambling, from Mike Alexander, of Bacon Bits blog. Using a spreadsheet to track your gambling losses doesn’t lessen the pain.

Automating Class Creation

Students attend classes, so Dick Kusleika, from Daily Dose of Excel, shows his technique for automating class creation in Excel VBA. The video demo is silent, so you can play the music of your choice, or add your own voice over.

Experiment With Excel

You don’t need extravagant plans to celebrate Spreadsheet Day. To quote The Science Goddess, “So, my advice for today is simply to go forth and double-click. Open Excel and play around.”

Celebrate Responsibly

Finally, wise words from Excel guru, and party pooper, John Walkenbach – celebrate Spreadsheet Day responsibly.
_______________

Happy Spreadsheet Day! I hope you’re making time to cell-ebrate this special day. October 17th was selected as Spreadsheet Day, because that is the date that VisiCalc was first shipped.

Student Spreadsheets

The theme for this year’s Spreadsheet Day is Student Spreadsheets. If you have uploaded a free, useful template or add-in for students, or posted a spreadsheet tip, please send me the link so that I can share it.

Or, post your links/tips on Twitter, using the hashtag — #spreadsheetday – so we can find them.

Student Time Tracker

My contribution for Spreadsheet Day 2011 is a Student Time Tracker. You can keep track of your lecture hours, and course work hours, to see what the weekly totals are.

To start, you’ll enter the Semester start and end dates, in the blue cells.

Next, enter your courses, and the scheduled lecture and lab hours per week.

Add Your Assignments

As the semester progresses, enter any assignments that you get, and other tasks, like preparing for tests and exams. As you finish your assignments, enter the completed date and actual task time. This will help you improve your time estimating skills.

For large assignments, you can use the Course Work Time Estimator sheet, to enter all the steps, and the time each step should take. Then, add a buffer percentage, to include extra time in the estimate. This will cover all those little things that can go wrong along the way.

Check the Weekly Hours

On the Weekly Hours Time Estimator sheet, you can see the total hours for each week in the semester. The Work Hours are calculated by using the SUMIF function to get the hours for each week.

At the top of the sheet, enter your target hours for each week – the maximum number of hours that you want to spend on classes and assignments.

In the screen shot below, the target is 25 hours (that’s pretty low!), and there is conditional formatting to highlight weeks that exceed that target.
If you see a heavy week coming up, you might be able to complete some assignments early, to ease the workload.

Download the Student Time Tracker

To see how the time tracker works, you can download the Student Time Tracker template. Go to my Sample Files page, and in the Functions section, look for FN0037 – Student Time Tracker

The file is in Excel 2007/2010 format, and zipped. There are no macros in the file.
___________________

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 inspiration for the Spreadsheet Day challenge, wander over to the Science Goddess’ blog, and see if there are any template suggestions.

Spreadsheets Everywhere

Even on my vacation, I saw spreadsheet everywhere, like this table in the hotel. Maybe it’s just me, but those tiles look like the cells in a spreadsheet.

I’m sure that the Margaritas didn’t affect my interpretation of the table pattern. 😉

Please Contribute

Please take a few minutes on Monday to contribute to the Spreadsheet Day cell-ebrations.

• Post a spreadsheet tip on Twitter, with the #spreadsheetday hashtag.
• Write an article with a spreadsheet tip for students
• Create a student-themed spreadsheet template or add-in and post it on the internet

Remember to let me know about your post, so I can link to it on the Spreadsheet Day blog

I’m looking forward to seeing your contributions!
_______________

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 – we ventured into the dark and dusty dungeons below the Science building, where we submitted punch cards, to run a program. Good times!

The Spreadsheet Day Challenge

Even now, with fancy gadgets and Google searches, it’s tough to manage things as a student. By mid-October, the new school year enthusiasm has worn off, and brutal reality has set in.

Students are running out of money, are tired of eating macaroni and cheese, and can’t find any clean socks. A spreadsheet can’t solve all their problems, but might help them keep organized, and stay on a budget.

Many students have Microsoft Excel, or Google Documents, or another spreadsheet, so let’s help them make good use of those tools.

To celebrate Spreadsheet Day 2011, could you create a free template or add-in, to help a student? What spreadsheet tools could a struggling student use?

• Monthly student budget tracker
• Course assignment checklist
• Mark needed to pass this course calculator
• Low cost meal planner
• ???

If you don’t have time to make a template, you can drop by this blog next Monday, and leave a spreadsheet tip in the comments.

• Share one of your favourite formulas
• Post a time-saving shortcut
• ???

Post Your Contributions

Next Monday, October 17th, post your Spreadsheet Day contribution on your blog, or Facebook, or Twitter (use hashtag #spreadsheetday), or create a public Google spreadsheet.

If you send me a link to your free and useful Spreadsheet Day tool, I’ll post it on the Spreadsheet Day Blog, to help students find your work.
Thanks! Looking forward to seeing your contributions. Will you join in?
_____________

Remember, Sunday October 17th is Spreadsheet Day, so you’d better start planning your celebrations. You could start the day with a big bowl of Chex cereal — each bite looks like a little spreadsheet. For dessert at the end of the day, have some pie, or bars, while you dream about charts.

Continue reading “Spreadsheet Day 2010 — Top 5 Excel Tips”