Collect and interpret data with Microsoft Forms and Excel

Microsoft Forms and Excel Online

Microsoft Forms and Excel

Collect and graph data online

Microsoft Forms and Excel

The lessons in this issue cover the collection of data using Microsoft Forms. The data collected from this form is used to create a Pie Chart in Excel. The chart is used by students to answer questions about the project. It is also used to interpret the results of the collected data.

Microsoft Forms

Microsoft Forms is an application for creating polls or surveys. The form is created and shared online. You don’t need to purchase Microsoft Office 365 to use the Forms application. Microsoft offers basic services for free with their Outlook email account. The services are all Cloud-based with support for apps on mobile devices. The services include the three major applications. For the purposes of these lessons, I will be using a free account. I want you to be able to follow along even if you don’t have a Microsoft Office 365 purchase.

I recommend using the Microsoft Edge browser on Windows devices. Use the Chrome browser on Mac computers. Apps are available for mobile devices but I will not be using them in these lessons.

Microsoft Forms collects all the responses and creates a variety of graphics. These graphics help us visualize the data. We will briefly look at the responses. We will create our own charts and tables. The form collects and graphs the responses for us. This gives students an idea of what their graphs will look like.

Responses and Excel

Once we collect the responses they appear on the response page. They are organized for us with graphs that represent the answers for each question. As nice as this information is we are going to generate our own graphs.

LEFT and SEARCH functions

The online version of Excel has a limited set of tools. This poses a challenge. We need to separate the different activities that are grouped together. This opens opportunities to learn about some functions. We will learn to use the LEFT function to collect the first item chosen by students in the form. We will combine the SEARCH function with the LEFT function to create code that automates the collection of the first selection.

COUNTIF function

To count the number of separate first choices we will learn to use the COUNTIF function. This function searches for the occurrence of a word or phrase. It counts the number of times the word or phrase is found in a selected Range.

Tables and Graphs

Once we have the items separated and counted we will use tables and graphs to organize our information. We will create a basic Pie Chart. This is all we can do with the online version of Excel. We will take some other data from our form and create tables. In the process, we will learn how to use the COUNTIFS function. This function is similar to the COUNTIF function. The COUNTIFS function allows us to count multiple items in multiple Ranges. One of our tables requires an extension of the skills we have learned so far. We will learn the difference between Relative Cell references and Absolute Cell References.

Formatting Tables

The tools to create graphs in the online version of Excel are limited. This is not so for the table tools. We will learn how to format our tables. We will color cell backgrounds and format table borders. You will learn how to merge cells so the table title fits across the columns.


These are the standards covered in the lessons.

Common Core State Standards for Mathematical Practice and College and Career Readiness Anchor Standards

Math Practice 2 (MP.2): Reason abstractly and quantitatively. Math Practice 4 (MP.4) Model with mathematics.

Math Practice 5 (MP.5): Use appropriate tools strategically. Math Practice 6 (MP.6): Attend to precision.

Reading 7 (CCRA.R.7): Integrate and evaluate content presented in diverse formats and media, including visually and quantitatively, as we all in words.

Writing 9 (CCRA.W.9): Draw evidence from literary or informational texts to support analysis, reflection, and research.

ISTE Standards for Students

3. Research and Information Fluency

Students apply digital tools to gather, evaluate, and use information

b. Locate, organize, analyze, evaluate, synthesize, and ethically use information from a variety of sources and media

d. Process data and report results

4. Critical Thinking, Problem Solving, and Decision Making

Students use critical thinking skills to plan and conduct research, manage projects, solve problems, and make informed decisions using appropriate digital tools and resources.

a. Identify and define authentic problems and significant questions for investigation

c. Collect and analyze data to identify solutions and/or make informed decisions

6. Technology Operations and Concepts

Students demonstrate a sound understanding of technology concepts, systems, and operations.

a. Understand and use technology systems

b. Select and use applications effectively and productively

d. Transfer current knowledge to learning of new technologies