1. Articles
  2. How to use forms in Excel … plus something better

Decorative cover image with text: Sheetcast forms tutorial

Excel forms have improved my life. Now, I know there’s something even better (stick around to the end of this tutorial). But, for now, let’s start with the basics.

If you’re like me, you’ve spent chunks of time manually entering data into spreadsheets. This activity is never fun, but it can at times be excruciating. It’s easy to get lost in a spreadsheet, especially a wide one, as you endlessly hit “Tab” or slide the horizontal scrollbar trying to navigate to the correct cell. You can get to the end of a long row, only to realize you’ve skipped one of your data points. Then, you retrace your steps to uncover your mistake and shift the remaining data one cell to the right.

It turns out Excel has had a solution for years, buried deep within its customization options. In a few easy steps you can create a clear, simple form, one that allows you to enter your data into clearly labelled fields. Once you reach the end of the form, you hit “Return” to transfer that data into the next open row on your spreadsheet—and clear the form for the next data set.

Here’s a step-by-step guide into unlocking the magic of Excel forms.

1. Add the “Form” icon to your Quick Access Toolbar

At the upper left corner, you’ll see a small group of icons for a few common tasks—the defaults include Autosave, Save, Undo, and Redo. You can customize this set of icons to include any tasks you wish.

First, click on the “File” menu, and look for the “Options” button at the bottom of the left sidebar.

excel screenshot, options

Click “Options” and then find the button for “Quick Access Toolbar.” -pic2

excel screenshot, Quick Access Toolbar

Under the “Choose commands from:” header, select “All Commands” from the drop-down menu. Then, scroll down the alphabetical list to select “Form…” Then, click the “Add” button to include it in the list of active operations on the right. Then, click “OK” to close the Excel Options window. Presto! You now have a “Form” icon in your Quick Access Toolbar.

 excel screenshot, customize quick access toolbar

2. Format your data as a table

In order for forms to work, Excel (for some reason) requires that your data be formatted as a table.

excel screenshot, format data as a table

Start with an open spreadsheet, with headers at the top of each column. Hit Ctrl-T to reformat it as a table. A dotted line will appear around your existing data and a “Create Table” dialogue will open. Confirm that there’s a checkmark next to “My table has headers,” and hit “OK.” The dialogue window will close, and your data will now appear in table form.

3. Create and open your form

This step could hardly be easier. Click on the Form icon in your Quick Access Toolbar, and your form appears.

excel screenshot, create and open form

4. Enter your first new record

Click the button marked “New,” and type your next record into the fields on the form. As with most forms, you can advance to the next field by hitting the Tab key.

excel screenshot, enter new record

Once you have filled out the final field, hit “Enter” to add that record to your table and to clear the form. You are now ready to enter your next record … and so on … and so on …

You’ve now learned the basics of Excel forms. We haven’t covered some of the more intricate capabilities they offer (you can find more in-depth tutorials elsewhere on the web), but if you’re an average office worker with a stack of data to enter, we have already made your life easier.

How to use Forms in Excel, plus something better

5. Take Excel to the next level, with Sheetcast

Although online mini-tutorials like this one can help you get more out of Excel, that’s nothing compared to the software developers at Pleasant Solutions. With Sheetcast, they have created an Excel add-in that can transform an ordinary spreadsheet into a web app in minutes.

Provided you are reasonably comfortable working in Excel (as most office workers already are), you can easily learn to become a web application developer—with the help of Sheetcast.

conceptual photograph of excel sheet reports with many hands and devices overlapping

As we’ve demonstrated above, a simple form can streamline the task of manually entering data. Imagine instead an online form accessible by thousands of simultaneous users—some on laptops, others on desktops, smartphones or tablets—each entering their own data directly. An app that continually syncs and updates thousands or millions of data records. One that can notify you when changes are made, deliver a beautifully formatted PDF report via email at the end of each day—or perform countless other valuable tasks.

With Sheetcast, every piece of newly entered data is integrated with a single click. Your spreadsheet’s formulas are automatically honored in any new data submitted via the web app. When you download your sheet, all the formulas are applied and the data is fully updated, including relative references. You can easily set up different levels of access for specific users, automatic notifications when certain data exceeds a specific value, and countless other customizations.

In short, if you have an existing workflow—or other task involving web-based data collection and processing—Sheetcast is an ideal solution. Anyone who knows and uses Excel can create an app in minutes, one that integrates directly with the processing of your data—not just one time, but indefinitely.

photograph of business profressionals in a meeting, giving thumbs up

Online data collection is only one possibility among thousands with Sheetcast. Create a timesheet app to track your employees’ hours, or an inventory management system to help you stay on top of your supply chain. Build an elegant online portal for your clients and customers, or a document management system to organize your company intranet.

The moment you have your Sheetcast apps up and running, they begin saving you time and money. Your workflows immediately become faster, more reliable, and more secure.

For details on Sheetcast, including a video demo, visit the website.

 

More Articles:

Advanced Data Visualization Techniques in Web Apps From Excel

If you’ve ever viewed a report or infographic powered by Excel-generated graphics, you recognize that the time-tested spreadsheet application has some powerful tricks up its sleeve in terms of data visualization. Bar charts, pie charts, scatter plots, pivot tables, and more

Tips to Prevent Data Entry Errors in Excel

With Excel, your formulas, charts, and reports are only as good as the data you enter. So, if you’re responsible for entering a vast data set, it’s important to minimize the risk for error.

Co-authoring in Excel: Tips and Pitfalls

For years, Excel users have been able to collaborate on shared workbooks. By making it possible for multiple users to work together on a single file, Microsoft unlocked countless ways to boost efficiency and productivity.

Do You Use Any of These Five Excel Features? If So, Try Sheetcast

Maybe you’re a bit of an Excel guru. Your workmates frequently marvel at your ability to get the most out of the app, employing functionality they never even knew existed.

Excel Heroes: Chandeep Chhabra

Meet Chandeep Chhabra! When the Mumbai investment bank where he worked suddenly couldn’t make payroll anymore, Chandeep decided to turn his “Excel expert” side hustle into a full-time career. Despite having little experience and zero Excel credentials, he cold-called his way into a job interview at a major financial services company—and the rest is history.

Excel Heroes: Ken Puls

Follow the captivating journey of Ken Puls—from a novice to a renowned expert! Discover how this Excel hero transformed from an accounting supervisor to a global Excel trainer, sharing his passion for automation and unlocking Excel's full potential.

Excel Heroes: Alan Murray

Embark on the remarkable odyssey of Alan Murray, in his journey from computer mentor to MVP. Starting with teaching computer basics, Alan's evolution into an Office 365 specialist led to him focusing on Excel and Power BI. Alan discovered his passion for Excel and is now leading the popular London Meetup, fostering a global community of Excel enthusiasts.

Excel Heroes: Gašper Kamenšek

Discover Gašper Kamenšek's journey from college student to Excel MVP. His passion for Excel ignited during Microsoft Office classes, leading to a career shift and a thriving community involvement. Explore his experiences, insights, and adventures, from overcoming toxic work environments to becoming a sought-after speaker. Dive into his love affair with Excel and the ever-evolving landscape of data analysis.

Bulgaria Excel Days 2024

Sheetcast proudly announces their lead sponsorship for Bulgaria Excel Days 2024, demonstrating their dedication to the Excel Community. Join experts Ken Puls, Gašper Kamenšek, Alan Murray, and others for three days of Excel insights, presentations, and masterclasses. Don't miss Alex Martin of Sheetcast, premiering advanced new functions and workflows. Bulgaria Excel Days 2024 runs April 23-25 in Sofia, Bulgaria.

Excel Heroes: Crispo Mwangi

As the only Excel MVP in East, Central, and Southern Africa, Crispo Mwangi stands out as an Excel Influencer, business owner, and host of an MS Excel Forum, making his skills highly sought after. Despite his many commitments, this Kenyan luminary published his latest book, "Excel with PowerQuery and ChatGPT.

Excel plus Sheetcast as Your Ultimate Spreadsheet.com Alternative

Amid Spreadsheet.com's closure, Sheetcast provides a reliable solution, an Excel add-in. Created by a company of 15+ years, Sheetcast offers more than a quick fix. It pioneers innovative data management and application development, providing a secure path for those impacted by Spreadsheet.com's shutdown.

Customers Scramble for Alternatives as Spreadsheet.com Shuts Down

With Spreadsheet.com closing its doors on May 31, users are actively seeking alternatives. Sheetcast, an Excel add-in, stands out as the top solution, providing advanced capabilities and a seamless transition to reconstruct workflows into customized web apps. Sheetcast provides a smooth solution to Spreadsheet.com's shutdown

Excel Heroes: MrExcel Turns 25

Exactly 25 years ago, in 1998, Jelen published his first in a series of weekly Excel help articles at his brand-new website MrExcel.com (hardcore fans can still read that debut article thanks to the Wayback Machine). Soon afterward, he launched the MrExcel Message Board, an online community for Excel enthusiasts like himself.

The Evolution of Efficiency from Excel to Web App

For decades, Microsoft Excel has been a foundational component in the way global businesses collect, analyze, track, and report data. As powerful and flexible as Excel is on its own, however, it can now also serve as a stepping-off point to something even more dynamic and efficient.

Excel Heroes: N-nyiimock Bitanyanmi

N-nyiimock Bitanyanmi is a welcome and familiar face in the online Excel community, where he often goes by the name Justice. Behind his enthusiasm, knowledge, and eagerness to learn, however, lies a surprising fact: until he was almost an adult, he had rarely laid hands on a computer.

Ten Memorable Excel Disasters

Pretty much anyone who has ever used Excel has a horror story or two to share—a misplaced decimal, a missing minus sign, a botched cut-and-paste, or some other minor blunder with potentially major consequences.

Seven Essential Ways to Protect Your Spreadsheet

Too many of us are cautious in our daily lives, but cavalier with the safety and security of our spreadsheets.

Why Do People Still Use Spreadsheets?

Microsoft Excel debuted on the Macintosh in 1985 and came to Windows in 1987. Three and a half decades later, its name remains virtually synonymous with the very concept of “spreadsheets.”

Time for Excel to Evolve: From Spreadsheets To Web Apps

The Necessary Evolution from Excel Spreadsheets to Web Apps. Like many of the greatest technologies of our time, spreadsheets can be our salvation or our downfall – and with great power comes great responsibility.

Want to Convert Excel Spreadsheets to Web Apps?

Sheetcast enables you to convert Excel spreadsheet to web apps directly in Microsoft Office, a tool you already use every day. Sheetcast is affordable, easy to learn, and has hundreds of potential uses (limited only by your imagination).