Microsoft 365 Excel Intermediate (2023): Working With Data More Efficiently

Name: Microsoft 365 Excel Intermediate (2023): Working With Data More Efficiently
Author: Bob Akbar

Learn to properly navigate data in Excel 365

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Ackbar, and in these lessons, we'll discuss working with data more efficiently. In this lesson, I will show you how to use proper references. Let's get started here with our table, and let's say that we want to figure out the commission. Now, the commission is going to be at its own table, and we'll convert that into a percentage. Now, you will notice we can put equals the sales times the commission. This works very well until we decide we're going to use autofill, and all of a sudden, nobody's getting a commission any longer. So how does this happen? We use the right reference, K2. We just wanted that formula repeated, but let's take a look at what Excel does. Excel went to K3 when we went down to the third, and there's nothing in K3. So how do we solve this problem? So what we can do is we can say that we want it to stay on K2, and we can fix this by putting the dollar sign. Now, the first one locks it into K, the second one into two. Since we're dragging down, we can't do $K $2. That is being overly cautious, but it's a very valid way of doing it. And now, if we drag this down, everybody has their commissions. Another way that we could do this is we only want to lock in the second row. K is not going to change as we're going down as it would if we're going across, but we're simply going down. So what we can do is we can simply just put the dollar two, and then we can drag this down for the commissions. And there it is. Everybody has their commissions. So let's give you another example here. What if we were going to do the employees? And you notice how I put the equal sign and simply dragged down my full selection and it copied them all over. So now, if I want to copy their sales, so let's get a total for their sales. So we'll do employee here, sales, and commission. And for Jay, it is this sale plus this sale. For John, it is this sale plus this sale. So let's take a second here to remember what we discussed earlier. We want their sale, which we do not want to lock down from a dynamic to a static. A static holds it in place. So we want to be able to drag this down. And as we do that, we want it to go to N3, N4, N5. So we may want to lock down the N part, but we do want the two to change to a three, four, five, and so forth. And then we're going to multiply that by the commission. And we do want to lock that in. We do not want that to change. Could also lock in the K, and there we are. And now we could use the autofill to pull this down and we get the total commissions per person. Now, let me give you another example. What if the values were running this way? Okay, now we have our people here and for their sales, we're simply going to copy it from our other table that we have over here. Now for their commission, we're going to take their total and multiply it by the commission. However, this time we're going across, not just down. So this time we want to lock in the K part. Again, we can do both. However, if we're simply going across, we're going to recross the letters of the columns. So the number for the row is not going to change. And if you notice, if we use our autofill now, going sideways, we get the same commissions as we did here, but we only lock down the column part, not the row part, because the row part wasn't going to change. In contrast here, we locked them both down to be on the safe side, or we could simply lock down the row part because that's the part that was changing as we were dragging down our autofill, and that's all we needed to lock it. So in this one, we just locked in the rows. In this one, we just locked in the columns. Stay tuned for the next lesson, where we talk about range definitions, data filtering, and manipulation. Thanks for watching.

Intermediate Provider : Bob Akbar 3 Lessons · 24m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

Intermediate Excel users can avoid broken formulas and references by defining proper references. Understanding when static versus dynamic is recommended, the user can avoid repeated corrections and instead focus on moving forward with design, input, implementation, and summaries of the data analyzed.

These steps include knowing when data should be placed in separate worksheets. The process extends to knowing when to select some or all portions of a data table when looking to analyze the results. These results may be in the form of charts, summary tables, or a combination of both. By having the proper foundation set from the beginning, the user will save time, produce better results, and have less troubleshooting at the end of a successful project.

In this course on Working With Data More Efficiently, we’ll cover fundamental tables, worksheets, reference, and cell formatting. Not all data is a general type. By being able to define proper ranges and thinking about our next steps, we can set up our foundation in a manner that assists us to be more error free and efficient.

What You'll Learn

Connect and reference data across separate Excel worksheets
Define proper static versus dynamic references to avoid broken formulas
Distinguish foundational tables from result tables
Define data ranges and apply data filtering and manipulation
Recognize Excel field options and proper data table structure
Select full or partial data tables for analysis into charts and summary tables

Key Takeaways

Defining proper references lets intermediate Excel users avoid broken formulas and repeated corrections.
Knowing when to use static versus dynamic references helps users focus on design, input, implementation, and data summaries.
Placing data in separate worksheets and selecting the right portions of a data table supports cleaner analysis.
Analysis results can take the form of charts, summary tables, or a combination of both.
Setting a proper foundation from the beginning saves time, produces better results, and reduces troubleshooting at the end of a project.

Frequently Asked Questions

Who is this course for?

It is for intermediate Microsoft 365 Excel users who want to work with data more efficiently and avoid broken formulas and references.

What does this course cover?

It covers fundamental tables, worksheets, references, and cell formatting, including proper references, range definition with data filtering and manipulation, and proper data table structure.

What skills will I gain?

Skills include Microsoft Excel, Spreadsheets, Common Intermediate Format, Data Driven Instruction, Data Manipulation, and Microsoft Office Specialist (MOS) - Excel.

What are the learning objectives?

By the end you will be able to connect separate Excel worksheets, describe foundational and result tables, and recognize Excel field options.

What lessons are included?

The lessons are Proper References; Range Definition and Data Filtering and Manipulation; and Using Proper Data Table Structure.