Excel Timetable for Your Holiday Dinner

Happy Thanksgiving, if you’re celebrating today! Our Canadian Thanksgiving was last month, so I don’t have to worry about organizing a big dinner today.
If you’re responsible for getting the family meal on the table today, you can use my Excel Holiday Dinner Planner.
dinnerplanner2013_01
Yes, it takes a few minutes to set up, by entering all the food items, and the preparation steps, but it will be worth the effort! You probably don’t vary the holiday menu too much, so you can reuse the worksheet, year after year.

Calculate the Start Time

After you enter all the dinner items, go to the top of the worksheet, and select the time that you want to serve dinner. Automatically, the Excel dinner planner calculates the preparation start time for each item.
dinnerplanner2013_02

Follow the List

With the planner, you’ll have a complete list of dinner items, with preparation start and end times. Follow the list, and you won’t be likely to forget those dinner rolls in the oven, or leave the cranberry sauce in the fridge.
You can print a list, or a Gantt chart version, to show the preparation schedule.
dinnerplanner2013_03
You can find more instructions, and download links, on the Excel Holiday Dinner Planner page on the Contextures website.
Enjoy your dinner!
image
_________________

Excel Gantt Chart Template Giveaway

Last week, we had a giveaway for Excel Dashboards and Reports, by Mike Alexander and John Walkenbach, and here are the winners:

  • Jake, with comment 33
  • Jason M, with comment 13

Congratulations, and Katie Mohr, from Wiley, will email you later today, to arrange sending your copy of the file.

Excel Gantt Chart Template

This week, Jon Wittwer, from Vertex42, has donated 4 copies his Gantt Chart Template Pro, for project tracking. The file is available in xls and xlsx versions, and uses formulas and conditional formatting to create the charts.

To use the template:

  • Enter your task information, and view the results by day, week or month.
  • Show weekends, or hide them
  • Scroll through the project date range, to focus on a specific time period
  • In the xlsx version you can even change the bar colours, by typing a code in a worksheet cell

ganttchart01

It impressive what can be done in Excel, without any programming! You’ll see some useful techniques in this file that you can apply in your other workbooks too.

Sample Projects

The Gantt Chart Template package includes 7 bonus files, with sample projects. These will give you inspiration for setting up your own project, instead of starting from scratch. Here is a screen shot from one of the samples – Custom Software Schedule.

ganttchart02

Gantt Chart Instructions

The Gantt chart template is easy to use, and there is a Help sheet in the workbook, to show you how to get started. That sheet has:

  • a link to the video tutorials, where you can see all the steps for setting up your file. I watched the videos, and they clearly explain what to do.
  • written instructions for entering your task information, linking the tasks, and entering different types of input.
  • answers to frequently asked questions about the template

Gantt Chart Licence

The Gantt Chart Template Pro has a generous licence, that lets you privately share your edited template within your company or organization. So, instead of software that requires a separate licence for each user, you and your co-workers can collaborate on the same template. There is a link to the licence in the workbook, where you can read the details.

Enter the Giveaway

Jon Wittwer, from Vertex42, has donated 4 copies of the Gantt chart file for this giveaway. If you’d like a chance to win a copy, please read the rules, and then make a comment below.

  • In your comment, tell us
    • why you would like to track your projects in Excel, instead of a different program
    • and/or what type of projects you would track with this Gantt chart template
  • Include your email address, so I can contact you if you win. Your contact information won’t be publicly visible, and it won’t be used for any other mailings.
  • The deadline is Wednesday, September 4th, 2013, at 12 noon Eastern Daylight Time.
  • One entry per person.
  • The 2 winners will be announced on Thursday, September 5th, 2013.
  • Each winner will have 24 hours to claim the prize, and if not claimed, another name will be selected.

____________________

Track Weight and Nutrients With Excel

Happy New Year! I took a couple of weeks off, and I hope that you had some time to relax too. Now, it’s a new year, and time to get back to work.
It’s tough work staring at your computer all day, so maybe you’ve decided to set a couple of health goals for 2013. I’m getting out for a walk every morning, which is a great way to start the day. It’s good exercise, and all that fresh air helps my brain get going too!
If you’re looking for help with keeping track of calories, or weight loss, you can download a couple of free files from my Contextures website, to make things easier.

Calorie Counting

For calorie counting, try my Excel Recipe Nutrients Calculator.  It’s on the Excel Sample Files page, in the User Forms section: UF0016 – Excel Calorie Counter With Recipe Calculator.
For instruction on how to use the calculator, see this blog post: Excel Recipe Nutrients Calculator
caloriecounter01

Weight Tracking

For weight tracking, try my Weight Loss Tracker. You can choose either pounds or kilos as the weight measurement, and enter your targets and daily data. There is also a version for Stone and pounds, if you prefer that system.
weightlosstracker08
You can see the instructions, and download the sample file here: Excel Weight Loss Tracker
Good luck, and try to get away from your computer a few times a day!
_________________

Excel Budget Template

Are you still working on your budget for next year? I’ve just updated my budget template, and added a page on my website, to describe how it works.

The old version was created in 2002, so it was definitely time for an update! This is a simple budget layout, but might give you some ideas for working on your own workbook.

The Menu Sheet

There is a menu sheet at the front of the workbook, with two named cells – Location and Start Date. The information that you enter in those cells is used on the other sheets in the workbook.

There are four navigation buttons, to take you to the data entry sheets and the report sheets. And those sheets have a Menu button, to bring you back to the menu sheet.

budgetvariance01a 

Data Entry Sheets

When you’re in the planning phase, you can enter your budget categories and forecasts on the Forecast sheet.

budgetvariance02a

Later, when you have Actual numbers, you can enter those on the Actual worksheet.

budgetvariance03a

Year To Date Reports

To see how things are going, you can check the Year to Date report, which shows the Actual amounts, up to the current month, and Forecast amounts for the remaining months.

Conditional formatting colours the columns with Actual data, to it’s easy to see where it’s been entered.

budgetvariance04a

Variance Report

The final report shows the variance between the forecast amounts and the actual amounts. Again, conditional formatting colours the columns with Actual data.

budgetvariance05a 

Download the Sample File

To see the details, and to download the sample file, please visit the Forecast vs Actual  – Variance page on my Contextures website. The file is in Excel 2007 / 2010 format, and contains macros.

__________________

Use Excel to Plan Christmas

Mock if you will, but every year I use my Excel Christmas planner to keep track of things over the holiday season.

holidayplanner02 

Where Are the Gifts?

I make a few tweaks to the planner every year, and in this year’s version there is a column to note where you hid a present, after you’ve brought it home. That should prevent those Christmas Eve panics, while you try to find everything. Not that I’ve ever gone through that – I’m only thinking of you! 😉

holidayplanner04 

Black Friday Planning

This year, the stores are even advertising Black Friday and Cyber Monday sales in Canada, so I’ll be able to use the Black Friday planning sheet, to find a few bargains.

Based on the prices that you enter, the worksheet calculates which store has the best price for each item, and which store has the most deals. Note: If prices are the same at multiple stores, the first store will be shown in the "Best Price" column.

 

Download the Excel Christmas Planner

To download the file, you can visit the Excel Christmas Planner page on my Contextures website. Happy Thanksgiving, and good luck finding those awesome sales tomorrow and Monday!

____________________

Do You Excel in the Kitchen?

These days, I don’t have to cook too often, but company is coming tonight. So, I dug out a slow cooker recipe, and got ready to brown the meat.

Of course, I didn’t want to end up with oil spattered all over my work clothes (shorts and a t-shirt), so I grabbed the apron that my daughter made for me. As you can see in the picture below, this is proof that I’m an EXCEL-lent cook, but not very good at ironing.

IMG_2547

Excel Meal Planner

If you want to Excel in the kitchen, but don’t have a fancy apron like this one, you can use my Excel meal planner. It includes a recipe selector tool, thanks to Jimmy Peña, so you can search the web for recipes. Then, pick your meals for the week, and print the shopping list for those meals.

excelmealplanner01 

For special occasions, like Canada Day, or July 4th, you can use the Excel holiday dinner planner to organize a meal for a large group, or a fancy dinner for friends. How long does it take to barbeque a package of hot dogs, and defrost an apple pie?

And in case you were wondering, here is the dinner that I was browning, when I had to grab that apron. It smells great, as it simmers in the slow cooker, so I hope the company enjoys it.

IMG_2545

___________________

Excel Recipe Nutrients Calculator

A couple of years ago, I posted an Excel Calorie Counter workbook, which has been very popular. In one of the comments, Autumn suggested that I add a recipe calculator to the file.

“I’m also looking to add a sheet within the workbook, that would work similarly to the Daily Entry sheet, where I can input a recipe to calculate the nutritional info for 1 serving of said recipe.”

And in the latest version, that Recipe Calculator has been added. Thanks Autumn, for the suggestion and the reminder. 😉
The new version also has protein, carbohydrates, fat and fibre data. If you download the workbook, please let me know what you think of the changes.

Enter the Recipe Ingredients

On the new RecipeCalc sheet, you can enter a Recipe Name and category, and the number of servings. Then, enter the ingredients and their categories, along with the required amounts.
ExcelCalorieCountRecipe01

Calculate the Amounts

Also enter the amount shown in the Food List measurement, and the worksheet calculates a multiplier. This feature uses the Excel CONVERT function.
For example:

  • In the first row shown below, the recipe calls for 1.25 pounds of chicken.
  • The Food List has the nutrient information for 100 grams of chicken.

ExcelCalorieCountRecipe04

Add to the Food List

After you calculate the recipe’s nutrients per serving, you can copy the total amounts, and paste as values in the FoodList sheet.
I used the category _MY RECIPES, but you could use one of the standard categories, if you prefer.
ExcelCalorieCountRecipe03

Use Your Recipes in the Daily Data Entry

Once your recipes are added to the list, you can select them in the FoodEntry worksheet, where you enter your daily food choices.
ExcelCalorieCountRecipe02

Download the Excel Calorie Counter with Recipe Calculator

You can download a zipped copy of the Excel Calorie Counter with Recipe Calculator on the Contextures website. The file is in Excel 2007 format, and contains a macro. In the download section, look for “Excel Calorie Counter With Recipe Calculator”
As I mentioned when posting the original Calorie Counter, I’m not a medical professional, so use this workbook for entertainment purposes only. The calorie counts are from Health Canada, so the ingredients and calorie counts might be different where you live. Check the product labels for accurate numbers.
If you’re looking for more nutrient information, to add items to the food list, you can try these websites:

Your Feedback is Welcome

This is a beta version of the Recipe Calculator, and I have a few ideas for improving it, and I’d appreciate your thoughts too. If you download the file, and test it, please let me know if you have any suggestions for making it easier to use. You can add a comment below, or send me a quick email –
ddalgleish @ contextures.com.
__________________

Updated Excel Weight Loss Tracker

You know how tough it can be to maintain two versions of the same file. It creates twice as much work for you, and no extra rewards!
So, instead of having two versions of the Excel weight loss tracker – pounds and kilograms – I’ve rolled them into one workbook. For now, there is a separate file for the stone measurements, because it has a different layout on the data entry sheet. If possible, I’ll roll that version in later.

Select a Measurement System

When you open the Excel weight loss tracker, go to the WeightGoals sheet and select a measurement system – pounds or kilos – from the data validation drop down.
weightlosstracker01
That changes the labels and calculations in the worksheet. Since we’ve selected Pounds in this example, the labels say “Lbs to Lose” and “Height (Inches)”. Those would show “Kgs to Lose” and “Height (cms”) if we had selected Kilos.
weightlosstracker02

Measurement System Lookup Table

The workbook has a measurement system lookup table, which uses INDEX and MATCH formulas to find the correct information for the measurement that you selected. Even the chart title changes, based on your selection.
weightlosslookup

Weight Loss Tracker Improvements

In this version of the weight loss tracker, there is a pivot table to summarize the data (why didn’t I think of that before!). After you enter data, just click Refresh All, and the pivot table and dashboard chart are updated. You can add as many rows of data as you need, without struggling to change the data range.
The chart has a target line too, so you can see how close you’re getting to your goal.
weightlosstracker08

Download the Excel Weight Loss Tracker

You can download a zipped copy of the Excel weight loss tracker, and try it for yourself. The sheets are protected, with the data entry cells unlocked, and there’s no password on the worksheets.
There are sample files for Excel 2007/Excel 2010 (updated version), and Excel 2003 (old version of the file) on the Contextures website: Excel Weight Loss Tracker
If you can think of any enhancements for the next version, please let me know.

______________

Spreadsheet Day 2011 Review

SpreadsheetDay82 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.
_______________