Microsoft Excel Formulas: Master Microsoft Excel 2016 Formulas in 30 days

TABLE OF CONTENT

Another Book from the Author

TABLE OF CONTENT

INTRODUCTION

CHAPTER ONE

Formula and Function Basics

Simple Formulas

Mathematical operators

Understanding cell references

To create a formula:

Modifying values with cell references

To create a formula using the point-and-click method:

To edit a formula:

Practical Exercise

Complex Formulas

The order of operations

Creating complex formulas

To create a complex formula using the order of operations:

Relative and Absolute Cell References

Relative references

To create and copy a formula using relative references:

Absolute references

To create and copy a formula using absolute references:

Using cell references with multiple worksheets

To reference cells across worksheets:

Practical Exercises

Functions

The parts of a function

Working with arguments

Using functions

To use a function:

Working with unfamiliar functions

Understanding nested functions

Other common functions

CHAPTER TWO

Formula Tips

Double-Check Your Formulas

Check the references

Look for mix-ups

Break it up

Ballpark it

Check the arguments

Walk through the order of operations

Switch to formula view

Understanding Number Formats

Why use number formats?

Applying number formats

Using number formats correctly

Percentage formats

Date formats

Other date-formatting options

Number formatting tips

Practice reading formulas

Determine which cells contain formulas

Try to understand what the formula is doing

Think about the order of operations

Interactive: Formula Exercise

Simple Formulas

Complex Formulas

Functions

CHAPTER THREE

Scenarios

Over-Budget Items

What are we trying to find out?

Writing the formula

Percent-Off Sale

What are we trying to figure out?

Step 1: Find the discount

Step 2: Subtract the discount from the original total

Bonus Section

Basketball Team: Player Statistics

What are we trying to figure out?

Writing the formula

Writing the formula

Highest-scoring game for each player

Highest-scoring game

Movie Tickets

Our spreadsheet

Solving this problem

Writing the formulas

Using the If Function

How can we solve this problem quickly?

Prepare the spreadsheet

Writing the function

Using Concatenate to Combine Name

How can we solve this problem quickly?

How it works

Writing the function

Buying a Printer: Cost Comparison

How can we answer this question?

Writing the formulas

CHAPTER FOUR

Create an Invoice

Invoice, Part 1: Free Shipping

How to solve this problem?

Writing the function

Invoice, Part 2: Using VLOOKUP

What are we trying to do with this spreadsheet?

How it works

Writing the function

Adding the product price

Bonus Section

Invoice, Part 3: Fix Broken VLOOKUP

What are we trying to fix?

Update the cell ranges

Using a table

Invoice, Part 4: More Shipping Options

What are we trying to do?

Writing the function

Invoice, Part 5: Data Validation

What are we trying to fix?

Data validation

Data validation in Excel

Naming an Excel range:

To create a data validation drop-down list:

Data validation with Google Sheets

To create a data validation drop-down list

CONCLUSION

ABOUT THE AUTHOR

Check Out My Other Books

Understanding Microsoft Word 2016

INTRODUCTION

Welcome to our Microsoft Excel Formulas tutorial. If you want to learn more about using formulas and functions in Excel, you've purchased the right book. Whether you're new to Excel or are a more advanced user, this tutorial or workbook is designed to show you how to use different formulas and functions to solve real-life problems.

Formulas work almost exactly the same way in most all versions of Microsoft Excel and other spreadsheet programs, so you can work along with this workbook even if you don't have Excel. This workbook is well adapted to other Excel, LibreOffice, OpenOffice and many other spreadsheet programs. However, keep in mind that we'll be using Microsoft Excel 2016 and 2013 throughout this workbook, so you may notice some slight differences if you're using a different program.

This workbook is also suitable and adapted to new Microsoft Excel formula users. This workbook starts with the absolute basics of creating formulas and gradually works up to some more advanced formulas. But before you begin, you'll want to have a basic understanding of how to navigate your Microsoft Excel.

If you already have a basic understanding of how spreadsheets work, you're ready to dive in and get started. All through this book, we will provide written and also very interactive lessons that will explain the basics of formulas and guide you through real-life scenarios where you'll use formulas to solve problems!

Thanks again for downloading this book, I hope you enjoy it!

CHAPTER ONE

Formula and Function Basics

Simple Formulas

Microsoft Excel came with a very excellent feature. One of which is the ability to calculate some numerical information using formulas. Microsoft Excel can add, multiply, divide and subtract just like a calculator. In our first lesson on mastering excel formulas, we will show you how to use cell references to create simple formulas.

Mathematical operators

Microsoft Excel is known to use standard mathematical operators for formulas. These operators are but not limited to a plus sign for addition (+), an asterisk for multiplication (*), a minus sign as subtraction (-), a forward slash for division (/), and a caret (^) for exponents.