Excel Beginners

Intro To Formulas: Step 3 To Mastering Spreadsheets

A Final Note & Activity

A Final Note

In this guide we have covered the main components of formulas, giving a list of operators and reference styles which will help you to understand how Excel translates the requests we give it into a set of instructions it understands. We also covered some basic functions that have everyday uses and applications, and explored examples of how to apply them.

It is important to understand that just because this guide showed one way to use a function, that does not mean it is the only application it can have. Creativity can lead to solutions which are not only sound in their design, but which run more efficiently than their longer and more complex counterparts.

A recommendation I have for users who are new to Excel, or who are just starting to explore formulas, is to continue to practice what you have learned whenever possible. It is understandable that you may not have the time to create mock spreadsheets with information to practice on, and that cannot be asked of everyone. What can be done, however, is that whenever an opportunity arises you take it to better your skills. This could include when you are working in an existing spreadsheet, updating values or writing small formulas to summarise information. The only other key factor is that if the files you are working on contain important data, do not practice manipulating information with formulas in the original file. Make yourself a copy of the file and work in there until you know the modifications you have made are working as intended. This will save a lot of trouble and headaches in the future if you begin to build this best practice now.

With this guide is included a document that contains examples and situations which span the various topics covered here. Follow along with the practice examples, and make your own modifications to them and see how it goes! Especially working with formulas there is one tool which will help improve any users understanding of how a formula is calculated, and that is the ‘Evaluate Formula’ tool under the ‘Formulas’ menu. Don’t forget that you always have access to additional resources when working in Excel and that using them only implies you are looking to grow and learn.

On that note, you have graduated from our beginner’s level guides! Congratulations. The learning doesn’t stop though. Keep learning and exploring, and have fun doing so.

An Activity

Let’s put your knowledge to the test! Below is a new set of data which we will be using for these exercises. We will set up a few questions for you using keywords and clues to help identify which functions and operators you will need to use. We will be working towards finding answers that can be copied down each row of the data, to give personalized information for the row! You will need to flex your knowledge of fixed and variable references for this one.

Activity Dataset:

Questions:

• Activity Question 1

So that we can better identify ways to increase sales, we want you to find out if the subtotal value of a transaction is above or below the median transaction value. If the subtotal is below the median, display the transaction ID for that row.

Keywords: Subtotal, Median, If, Transaction ID

• Activity Question 2

Location 101 uses the quantity of goods as an employee metric. They want to know how far above or below the average each transaction is. To get that percentage we need to divide the transaction value by the average of all transactions between both locations. For any rows that belong to location 102, display FALSE instead.

Keywords: Location, Quantity, Average, Percentage, False

• Activity Question 3

Management likes the calculations for location 101, but location 102 uses the price per unit as their metric. They want to display the proper percentage for each store, using their own metrics. So for location 101 we need to average quantity of goods, and for location 102 we need to average price per unit.

Keywords: Location, Price, Percentage, Average, Quantity

• Activity Question 4

Looking back over the information, it was noticed that the Transaction dates show November 2019, when it should be from February of that year. The days are correct, but we need a way to change the dates to reflect this.

Keywords: Transaction Dates, Date, Year, Day

• Activity Question 5

Management wants to create new product ID numbers for each location, they want the codes to still start with a capital ‘P’, followed by the store number (only the three digits to the right), and end with the rest of the original product ID number.

Keywords: Product ID, Left, Right

• BONUS QUESTION!

The transaction ID numbers used in the example were created using only the aspects of a formula which were covered in this guide. Can you find the formula needed to recreate the ID numbers?

And that’s it for this one! Compare your final outputs for final activity with me by downloading the results PDF file HERE, or the results Excel file HERE.