Open Microsoft Excel. Double-click the Excel program icon, which resembles a white 'X' on a green folder. Excel will open to its home page. If you already have an Excel spreadsheet with data input, instead double-click the spreadsheet and skip the next two steps. Excel formulas can be used for basic number crunching, such as addition or subtraction, as well as more complex calculations, such as finding a student's average on test results and calculating mortgage payments. Formulas in Excel always begin with an equal sign ( =). The equal sign is always typed into the cell where you want the answer to appear. Microsoft Excel is available as part of Microsoft Office 365 subscriptions. Office 365 Personal: $6.99/month or $69.99/year Office 365 Home: $9.99/month or $99.99/year Office Home & Student 2019: $149.99 one-time Office 365 Business: $8.25/user/month (annual commitment required) Office 365 Business Premium: $12.50/user/month (annual commitment required) Office 365 Business Essentials: $5/month.

Simple If Examples | And/Or Criteria | Nested If | More about If

Ms Excel Is Based On

The IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples.

Simple If Examples

The IF function checks whether a condition is met, and returns one value if true and another value if false.

1a. For example, take a look at the IF function in cell B2 below.

Explanation: if the price is greater than 500, the IF function returns High, else it returns Low.

1b. The following IF function produces the exact same result.

Note: you can use the following comparison operators: = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) and <> (not equal to).

2. Always enclose text in double quotation marks.

3a. The formula below calculates the progress between two points in time.

3b. You can use the IF function to display an empty string if the end value hasn't been entered yet (see row 5).

Explanation: if the end value is not empty (<> means not equal to), the IF function calculates the progress between the start and end value, else it displays an empty string (two double quotes with nothing in between).

And/Or Criteria

Use the IF function in combination with the AND function and the OR function and become an Excel expert.

1. For example, take a look at the IF function in cell D2 below.

Explanation: the AND function returns TRUE if the first score is greater than or equal to 60 and the second score is greater than or equal to 90, else it returns FALSE. If TRUE, the IF function returns Pass, if FALSE, the IF function returns Fail.

2. For example, take a look at the IF function in cell D2 below.

Explanation: the OR function returns TRUE if at least one score is greater than or equal to 60, else it returns FALSE. If TRUE, the IF function returns Pass, if FALSE, the IF function returns Fail.

3. For example, take a look at the IF function in cell D2 below.

Explanation: the AND function above has two arguments separated by a comma (Table, Green or Blue). The AND function returns TRUE if Product equals 'Table' and Color equals 'Green' or 'Blue'. If TRUE, the IF function reduces the price by 50%, if FALSE, the IF function reduces the price by 10%.

Nested If

The IF function in Excel can be nested, when you have multiple conditions to meet. The FALSE value is being replaced by another IF function to make a further test.

1. For example, take a look at the nested IF formula in cell C2 below.

Explanation: if the score equals 1, the nested IF formula returns Bad, if the score equals 2, the nested IF formula returns Good, if the score equals 3, the nested IF formula returns Excellent, else it returns Not Valid. If you have Excel 2016 or later, simply use the IFS function.

2. For example, take a look at the nested IF formula in cell C2 below.

Explanation: if the score is less than 60, the nested IF formula returns F, if the score is greater than or equal to 60 and less than 70, the formula returns D, if the score is greater than or equal to 70 and less than 80, the formula returns C, if the score is greater than or equal to 80 and less than 90, the formula returns B, else it returns A.

More about If

The IF function is a great function. Let's take a look at a few more cool examples.

1. For example, use IF and AND to test if a value is between two numbers.

Explanation: the AND function returns TRUE if the person is older than 12 and younger than 20, else it returns FALSE. If TRUE, the IF function returns Yes, if FALSE, the IF function returns No.

2. You can combine IF with AVERAGE, SUM and other Excel functions. The sky is the limit!

Explanation: the AND function returns TRUE if the input value is greater than 100 and the average of the values in the named range Data1 is greater than 100, else it returns FALSE. If TRUE, the IF function returns the sum of Data2, if FALSE, the IF function returns 0.

Confused? You can always use the Evaluate Formula tool to step through your IF formula. This Excel tool helps you understand a formula.

3. For example, select cell G3 above.

4. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

5. Click Evaluate multiple times.

Note: try it yourself. Download the Excel file and choose one of the IF formulas explained on this page. You can use this tool to step through all kinds of formulas.

Have you tried Microsoft Forms yet?

It’s a great new tool from Microsoft that allows you to quickly and easily create surveys, quizzes and polls.

There is a wide variety of potential uses for these. You could use them to get customer feedback, collect reviews and testimonials or even use them as data entry forms.

I’m using these using these forms in my courses to get student feedback and reviews so I can improve my teaching. I also suggested them to a friend who was looking for a simple data entry solution.

Let’s take a look!

Sign Up For Microsoft Forms

If you’re signed up with Office 365, then you already have Microsoft Forms and it can either be accessed from OneDrive, SharePoint, Excel Online or the Forms website.

If you don’t have an Office account, then you can still sign up to use forms for free here https://forms.office.com/ by creating a Microsoft account.

Creating a New Form or Quiz

There are a couple different ways to create a form or quiz with Microsoft Forms.

Creating a Form in OneDrive

You can create forms inside OneDrive personal or business. Navigate to the folder where you want to store your form results ➜ click on New ➜ select Forms for Excel.

You will then be asked to name the workbook associated with your form. This workbook will be saved in your chosen folder and will be where all the form submissions will be saved.

Creating a Form in SharePoint

The same thing can be done to create a form if you have an Office 365 business account with SharePoint online. Navigate to the folder where you want to store your form results ➜ click on New ➜ select Forms for Excel.

This also prompts you for a new workbook name where your form submissions will be saved.

Creating a Form in Excel Online

If you’re working with Excel Online, you can also create forms. Go to the Insert tab ➜ click on the Forms button ➜ select New Form from the menu.

This will create a form that’s linked to the current workbook.

Creating a Form from the Website

After you sign into https://forms.office.com/ you should be taken to the home page where you can create new forms and quizzes. If you don’t land on the home page, you can always get there from any screen using the button in the top left corner of the screen that’s labelled Forms.

From the home screen, click on either New Form or New Quiz.

The Different Types of Questions

Microsoft Forms currently has two types of forms. There are Forms and Quizzes. They both allow you to create the same type of questions. The only difference between them is you can assign point values and correct answers to quiz questions in order to calculate a quiz score.

All the questions can be accessed by clicking on the Add new button. This will show the list of available questions choices, but note that some are hidden in a menu accessible by clicking on the Ellipses.

Ms Excel Is Based On The First

Types of Questions

There are 7 types of questions available. Each has different options.

• The Choice option allows you to define a list of possible answers for the user to select one or more answers from.
• The Text option allows you to create long or short answer text questions.
• The Rating option allows you to create questions with a star or number rating between 2 and 10.
• The Date option allows the user to select a date from a calendar to answer the question.
• The Ranking questions allows a user to drag and drop items to answer questions like order of preference.
• The Likert option allows you to create “agree/disagree” scale type questions.
• The Net Promoter Score option allows you to create questions like “How likely are you to recommend [brand X] to a friend or colleague?” that utilize a net promoter style grading.

Tip: Some question types like the Choice and Ranking options allow you to copy and paste from a range in Excel or a line separated text file. This is handy if you have a long list of choices to add.

Each type of question has a different menu. For example, the above picture shows the available options for the Choice style questions.

1. You can copy, delete or move the question from the menu in the top right of the question.
2. You can add the actual question along with a subtitle (the subtitle option is found in the Ellipses menu).
3. Forms has some built in AI capability to suggest answers for some types of questions. You can select individual items from its suggestions or add them all.
4. For the multiple choices you can add or delete choices. You can mark the correct answer (for quizzes) and add comments to the choices.
5. You can add more choice options.
6. For quizzes, you can assign a point value for the purpose of calculating a quiz score.
7. You can allow multiple answers and set the question to require an answer in order to submit the form.
8. Further options are available in the Ellipses menu.

Form Sections

Sections in forms or quizzes allow you to break up the form into parts.

If you have a lot of questions in your form and don’t use sections, then the user would see all the questions on one page. Adding sections means you can break this up into multiple pages and the user will only see the next section of questions after completing the current section.

This can help with form submission rates, as seeing long lists of questions can discourage a user from answering all the questions and submitting the form.

You can add sections by clicking on Add new ➜ Ellipses menu ➜ Section.

Previewing a Form

When you’ve done creating your form, you can easily preview it and see exactly what a user will see.

Click on the Preview button in the top right to view and test the form. Careful though, as submitting the form in preview mode will still add the response to your results and you will have to manually delete the response to remove it from your results.

You’ll be able to preview what the form looks like on both mobile and desktop by using the buttons at the top right while in preview mode.

Form and Quiz Settings

Each form has some important settings that can be found in the Ellipses menu.

1. For quizzes, you can choose to show the results to respondents automatically after submission.
2. Forms and quizzes can either be public or private to an organization. When shared within an organization, you can chose to record the respondent’s name and limit users to one submission.
3. There are options to open or close the form to accepting responses. You can set a start and end date for accepting responses. You can shuffle the order in which questions appear. You can add a custom thank you message that appears after a user submits the form.
4. You can set notification options to send email notifications to each user or to yourself when a new response is received.

Form Branching

The above is an example of a form that uses branching.

Branching is one of the most useful features in Forms, but it’s unfortunately hidden inside an Ellipses menu. This will allow you to have different questions appear next based on how the user has answered a previous question.

To create a conditional form, click on the Ellipses found in the top right ➜ then select Branching.

This example asks the user if they’ve used Microsoft Forms before and gives two options, either Yes or No. If the user selects yes, then they are asked to rate the product out of 5 stars. If the user answers no, then they are asked why not. This way users are not shown questions that are not relevant to them.

Viewing Form Results

At some point, you’re going to want to take a look at the answers that have been submitted by people using your form. This can be done in the Responses tab of any form where you can see a summarized version of the results.

1. You can view the details of each result individually.
2. You can view all the results in the associated Excel file.
3. You can share the results by creating a summary link. Click on the Ellipses ➜ choose Create a summary link.

In fact, I created a summary link to the above example for which can be viewed here.

Form Themes

There’s not much you can do in order to change the look and feel of your forms, but you can change the colour or background image.

Go to the Theme menu in the top right. Here you can select from a couple preset themes or if you click on the plus icon, you can select a custom colour or background image.

Sharing Your Forms

How do are you going to use your new form?

The whole point of creating a form is to collect information from users, so after creating a form you’re going to need to share it with your user audience! This can all be done from the Share menu in the top right.

1. You can choose to make the form available to anyone with the link or only people inside your organization.
2. You can copy this link and send it to anyone you want to complete the form.
3. Sharing the form can be done via link, QR code, embedded HTML form code (see example of embedded form above), or by email.
4. You can share a copy of your form as a template via a link so others can modify it for their own use.
5. You can collaborate on form creation within an organization or with external users with an Office account.
6. You a link to collaborate on editing the form can be copied and shared with anyone you want to give access to edit the form.

Conclusions

If you need to collect information from different users, then Microsoft Forms might be the tool for you.

With forms, you can quickly and easily create questionnaires that you can share both internally or externally from your work.

These forms will automatically collect and store the responses inside an Excel workbook so they can be easily viewed and analyzed later.

It’s another great tool in the Office suite that works well with Excel and is one you’re definitely going to want to explore using.