Tag Archives: Freebies

How to Create Fill-in Forms using Excel

On a recent shopping trip to purchase a wedding present, I came to a standstill in the kitchen appliance aisle. I was stunned by the number of “one trick ponies” beckoning for my attention – waffle makers, Mickey Mouse shaped waffle makers, rice cookers, bread makers, silver dollar pancake makers, panini makers, sandwich grills. I wondered if it wouldn’t be more space and cost efficient to just use a good set of pots and pans.

So it is with software designed to do a singular task. I am sometimes infatuated by the latest marketing claims that promise to make quick order of a particular function. With a little more thought, however, I am usually swayed back to my tried and true tools. One example is making fill-in forms, several software packages may be purchased for this task. However, creating fill-in forms in Excel takes just a few steps.

There are two settings that must be coordinated, both found in the Format dropdown in the Cells group of the Ribbon. The default settings for the two settings are set as Protect Sheet set to OFF and Lock Cells set to ON. We’ll double check the Lock Cells as a first step.

1. Press CTRL-A to select the whole worksheet. Click on the drop-down arrow on Format in the Cells group. Check that the Lock Cells is ON, indicated by the shaded square around the lock icon. If in doubt, click the lock and the shaded background will toggle on and off.

Forms_Excel_1

2. Create the form with appropriate labels and open cells in which the users will input their information. For example, Cell A2 below shows the label “Name.” Cells B2 and C2 are blank and Merged as one cell for entry of the user’s name.

Forms_Excel_3

3. Select each of the input cells, in the above case, B2, C2, E2, and B3, C3, D3, E3. Cells which are non-contiguous may be selected by holding down the CTRL key while clicking on each cell.

4. Click on the drop-down arrow on Format in the Cells group. In the Protection section, click on Lock Cell. This will toggle OFF the lock for only the selected cells.

Forms_Excel_5

5. Click Protect Sheet right above the Lock Cell option. The dialog box provides several options to enable a customized user experience. Protecting the spreadsheet with a password will prevent the user from making any changes to the form without entering the password. Checking the box “Select unlocked cells” and unchecking “Select locked cells” will allow the user to conveniently tab through all of the input cells and skip over the label cells. Forms_Excel_6

6. Finally, save the file as an Excel Template so that each time the file is opened, a new copy is opened, leaving the original intact for future uses.

To see this form in action, download it here: Fill-in Form in Excel

How to Create a Perpetual Calendar in Excel

Perpetual_Calendar_1

Over the course of any workday, I might access up to 5 electronic devices that all synchronize my calendar. Yet if I need to look for a date later in the year, say June 15, I instinctively look at the paper 2013 calendar taped to the wall above my desk. I’m not quite sure what draws me to look at the paper, maybe it’s the alignment of the 7 columns of the days of the week or maybe it’s the perspective of seeing the whole year at a glance.

Nevertheless, it is a very ingrained habit and each year I tape a new calendar on the wall, usually a freebie that I saw online or one printed by a local vendor and given to us with their first shipment of the new year. This year, I decided to make my own in Excel and researched various methods for calculating the start day for each month. The cool thing about creating the months via formulas is that it becomes a perpetual calendar. Next year, all that is needed is changing Cell H7 to 2014 and voila, it recalculates all dates to the correct positions. It is sized to view nicely within Excel or saved to PDF. To print all on one page, use the “Fit all rows on One Page” option to fit the whole calendar on one piece of paper. Enjoy!

Download Perpetual Calendar in Excel

Best Tip for Simplifying Holiday Decorating

Several years ago, my husband and I approached the Christmas season with our social, professional and volunteer commitments overflowing. The activity in my professional life always peaks in December and that particular year my husband was about to ascend to the presidency of the community organization he serves. The weekend before Thanksgiving, he experienced a back injury which immobilized him for several weeks. To say we were overwhelmed was an understatement. All traditions we typically enjoyed like cookie baking, family dinners, church musicals, and gift shopping were abandoned left and right.

Although full-scale decorating was out of the question, I did want to put up a few decorations. Maybe a wreath on the front door, a nativity set, a candle or two. How much time could that possibly take, right? I grabbed a thirty minute window on a Saturday morning and headed up to the attic to grab those few decorations.,  I soon descended in total frustration, having opened tub after tub looking for “this and that” to my accomplish my goal. The cramped crawl space, poor lighting, and packed tubs were insurmountable obstacles in my tight timeframe.

Simplifying_Decorating_1
Photo: Marcelo Terraza

Two nights later, I set up our artificial tree in the living room. That ended up being our only decoration for the whole season, no ornaments, no lights, no garland, no icicles, just a stark green artificial tree. So, so sad!

The next year, our lives were at a very different place and we reinstated most of our holiday traditions. When I took those decorations out of the attic and started to plan my decorating strategy, I became a bit overwhelmed again with everything spread out all over my garage. Then I had an Aha! moment. The challenge the year before and the challenge I faced right then was knowing what decorations were in what tubs. Excel to the rescue! A very simple spreadsheet would give me an inventory of what I had and where it was stored

Here is my very simple spreadsheet that is my best tip ever in simplifying this important part of holiday preparations. Because the list of items is relatively short, grouping them by tub works fine. A quick visual scan locates the item I am seeking.

Decorations_Excel_1

 

If trimming the tree was first, then I would grab tub 7 for ornaments and tub 4 for garland and head to the living room, ready to do some business!  Snowmen next – no problem, tub 9. Stockings – tub 8, yipee! Even packing everything at the end of the season was easy when I knew what would fit in each tub.

An alternative would be to put each item on its own row and then an alphabetical sort of items might be helpful. I setup Sheet 2 for this method. Again, simple, simple, simple, but it has made holiday decorating and the cleanup so much more enjoyable.

Decorations_Excel_3

Download Decorations Spreadsheet