Join Dennis Taylor for an in-depth discussion in this video Working with array formulas more easily, part of Excel Tips Weekly.
- [Narrator] If you work with Excel array formulas you've gotten used to the idea of completing the formula or the function by pressing control, shift, enter. But Excel is changing the way these kinds of formulas work. Some of you maybe have not ever used the formulas at all, so in this case it will just be something easier that you might not have been alerted to. In this particular work sheet here we've got totals here in column d based on the information to the left, and a grand total at the bottom. In some scenarios let's say we don't need these interim totals at all we just want the grand total.
What if we could somehow take all of these times all of these and add them up. If we were writing a formula we might try the following: sum, let's add up all of these times all of these. Now in older versions of Excel if you press enter right now you'll get the value pop up error message. Looking like what we're seeing here down in row 22 just for reference. But if you have the latest Office 365 upgrade working with Excel, simply pressing enter does give you a correct answer, it's the same number we see right here, which is the total of all those.
If you have the older versions of Excel and not the most recent updates if you press enter here you will get that value error, but if you press control, shift, enter you will get an appropriate answer. And look in the formula bar at the top of the screen we see this embedded until we start to edit. We see that embedded in braces. That's no longer necessary. Once again if we're writing this formula with the latest upgrades we can type it in like this, enter and we have a correct answer. As we click on this we don't see those braces up there in the formula either.
And another simple little example over here. Here too we've got an average of those differences between order and shipping. Let's just cut to the chase, let's say again, the scenario is such we don't need these interim totals here why not simply average. And the way these calculations were done was simply the shipping date minus the order date. Let's take all the shipping dates, minus the order date. And once again, like in the previous example, we can now with the latest upgrades simply press enter and we will have a correct answer.
That's the same answer we're getting down here, which is the average of those differences. And that's all we're doing here. And when it pertains to functions as well, you might be familiar with frequency function? Here too, this is, well it's simply an array formula, with an array function equal frequency. We want to refer to all the data in column b from here down to the bottom. I'll simply click here and I do know that it goes down to row 621, so simply highlight this and complete the text. Although, I could use a column reference here.
It's better to put in the actual address so we get no extraneous results. Comma, if you're familiar with the frequency function you've set up a bins array, doesn't have to be adjacent to it, but often it would be. Doesn't have to be equally spaced, but often it would be. This is the various ranges of salaries and the chart to the right, which for the moment looks empty, is key to this. Here too, in older versions of Excel you would now press control shift enter, and you would've needed to highlight all those cells furthermore from E2 down to E11. Here just one we'll press enter and we've got answers everywhere.
The newer array form is don't need the braces. You don't need to press control shift enter. And here's one more. Two different functions. The trend function will create a straight line here. It's analogous to what we might do by right clicking adding a trend line. That puts in a linear trend line. If you want actual numbers associated with that point we can and sell c2 simply type equal trend. Left praenthesis, highlight these cells here down to the bottom and simply press enter.
And we've got a list there. And the chart picks up up and projects that for us. And the same thing with growth which we'll do almost the same thing but with an exponential line. Equal growth. So we'll simply refer to these cells over in column b again and enter. And we've got that entry as well too. So if you're new to array formulas it's going to be a lot easier than you would have imagined and if you have been working with them I think you'll recognize, this is a better way to work with these kinds of formulas and functions.
Author
Updated
2/23/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 24m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Working with array formulas more easily