Skip to content
Felix
  • Topics
    • My List
    • Felix Guide
    • Asset Management
    • Coding and Data Analysis
      • Data Analysis and Visualization
      • Financial Data Tools
      • Python
      • SQL
    • Credit
      • Credit Analysis
      • Restructuring
    • Financial Literacy Essentials
      • Financial Data Tools
      • Financial Math
      • Foundations of Accounting
    • Industry Specific
      • Banks
      • Chemicals
      • Consumer
      • ESG
      • Insurance
      • Oil and Gas
      • Pharmaceuticals
      • Project Finance
      • Real Estate
      • Renewable Energy
      • Technology
      • Telecoms
    • Introductory Courses
    • Investment Banking
      • Accounting
      • Financial Modeling
      • M&A and Divestitures
      • Private Debt
      • Private Equity
      • Valuation
      • Venture Capital
    • Markets
      • Economics
      • Equity Markets and Derivatives
      • Fixed Income and Derivatives
      • Introduction to Markets
      • Options and Structured Products
      • Other Capital Markets
      • Securities Services
    • Microsoft Office
      • Excel
      • PowerPoint
      • Word & Outlook
    • Professional Skills
      • Career Development
      • Expert Interviews
      • Interview Skills
    • Risk Management
    • Transaction Banking
    • Felix Live
  • Pathways
    • Investment Banking
    • Asset Management
    • Equity Research
    • Sales and Trading
    • Commercial Banking
    • Engineering
    • Operations
    • Private Equity
    • Credit Analysis
    • Restructuring
    • Venture Capital
    • CFA Institute
  • Certified Courses
  • Ask An Instructor
  • Support
  • Log in
  • Topics
    • My List
    • Felix Guide
    • Asset Management
    • Coding and Data Analysis
      • Data Analysis and Visualization
      • Financial Data Tools
      • Python
      • SQL
    • Credit
      • Credit Analysis
      • Restructuring
    • Financial Literacy Essentials
      • Financial Data Tools
      • Financial Math
      • Foundations of Accounting
    • Industry Specific
      • Banks
      • Chemicals
      • Consumer
      • ESG
      • Insurance
      • Oil and Gas
      • Pharmaceuticals
      • Project Finance
      • Real Estate
      • Renewable Energy
      • Technology
      • Telecoms
    • Introductory Courses
    • Investment Banking
      • Accounting
      • Financial Modeling
      • M&A and Divestitures
      • Private Debt
      • Private Equity
      • Valuation
      • Venture Capital
    • Markets
      • Economics
      • Equity Markets and Derivatives
      • Fixed Income and Derivatives
      • Introduction to Markets
      • Options and Structured Products
      • Other Capital Markets
      • Securities Services
    • Microsoft Office
      • Excel
      • PowerPoint
      • Word & Outlook
    • Professional Skills
      • Career Development
      • Expert Interviews
      • Interview Skills
    • Risk Management
    • Transaction Banking
    • Felix Live
  • Pathways
    • Investment Banking
    • Asset Management
    • Equity Research
    • Sales and Trading
    • Commercial Banking
    • Engineering
    • Operations
    • Private Equity
    • Credit Analysis
    • Restructuring
    • Venture Capital
    • CFA Institute
  • Certified Courses
Felix
  • Data
    • Company Analytics
    • My Filing Annotations
    • Market & Industry Data
    • United States
    • Relative Valuation
    • Discount Rate
    • Building Forecasts
    • Capital Structure Analysis
    • Europe
    • Relative Valuation
    • Discount Rate
    • Building Forecasts
    • Capital Structure Analysis
  • Models
  • Account
    • Edit my profile
    • My List
    • Restart Homepage Tour
    • Restart Company Analytics Tour
    • Restart Filings Tour
  • Log in
  • Ask An Instructor
    • Email Our Experts
    • Felix User Guide
    • Contact Support

Pivot Tables - Felix Live

Felix Live webinar on Pivot Tables.

Unlock Your Certificate   
 
0% Complete

1 Lesson (32m)

Show lesson playlist
  • 1. Pivot Tables - Felix Live

    32:00

Prev: Scenarios - Felix Live Next: Equity Method Investments and Non-controlling Interests - Felix Live

Pivot Tables - Felix Live

  • Notes
  • Questions
  • Transcript
  • 32:00

A Felix Live webinar on pivot tables.

Downloads

Database Analysis Workout 1 EmptyDatabase Analysis Workout 1 Full

Glossary

d functions Excel IF IFs Pivot Tables
Back to top
Financial Edge Training

© Financial Edge Training 2025

Topics
Introduction to Finance Accounting Financial Modeling Valuation M&A and Divestitures Private Equity
Venture Capital Project Finance Credit Analysis Transaction Banking Restructuring Capital Markets
Asset Management Risk Management Economics Data Science and System
Request New Content
System Account User Guide Privacy Policy Terms & Conditions Log in
Transcript

Welcome to this session.

I'm hoping that we have got, yeah, we've got the screen sharing on.

So I'm hoping we've got a little introduction today to it into this half hour webinar.

And this webinar is all about extracting data from Excel, and also using pivot tables. But pivot tables are just one part of that.

What we're basically gonna do is we're gonna look at some techniques for extracting data from Excel.

Now, just to kind of put this into context, Excel is obviously a an incredibly powerful tool, very flexible tool, and it's often the sort of tool that we use to collate information, to sift and analyze information.

And one of the things that Excel does is often database packages, other packages will export information in Excel format.

So you might, well often find that you're dealing with data that's been dumped into Excel.

So this session really is about how do you sift your way through that data? How do you start making sense of that enormous amount of information? So we're basically gonna look at some Excel spreadsheets today.

And I'm gonna take you through how you would actually use a number of different techniques. These techniques that are sitting on the screen at the moment to extract information from Excel.

And I often think these Excel sessions are a little bit like looking over someone's shoulder.

You know, you always find this in the office.

You'll often find that people are, uh, real sort of skill, really skilled, really experienced that Excel.

And often people look over their shoulder and say, how did you do that? How did you make that happen? What did you use? What function did you use? What technique did you use? What menu item did you use? And that's exactly what this is.

This is basically your opportunity to look over, uh, my shoulder and see how we use these functions, these techniques that are listed on the screen at the moment.

Of course, there will be a recording of this session.

So if I go a little bit faster and you missed something and you wanna recap, then you can come back and look at the recording.

You can now see Enuma has now put the resources on the screen at the moment.

So if you haven't got the resources, if you haven't got the spreadsheets, then click on that link in the chat box, and basically it'll take you right through to these two spreadsheets. As I said, basically two spreadsheets that you need.

One's the empty version and one is the full version.

Okay, without further ado, let's jump, jump, jump, jump in.

I'm just going to turn off my presentation mode and drag across onto the screen.

The first of our spreadsheets.

Oh, my machine is just probably need to make that just a little bit smaller so I can fit it on the screen.

Here we are. There we are.

So, here's our spreadsheet. And without further ado, let's jump into the spreadsheet. So I'm gonna go into the first tab.

You'll see at the bottom of the, this list of tabs, you will basically see that there are a number of tabs and they all have those different techniques for extracting data.

So we're just literally gonna work our way along these tabs.

I use each of these different techniques to pull out some, To pull out some data.

So we start right at the beginning, all of these tabs that they have exactly the same collection of data.

So when we pulled out, or one particular item of data, when we grab a particular answer then hopefully we can use some of the other techniques and we'll come up with the same answer just to kind of verify that we are getting the same answers, the same the techniques are, the different techniques is coming up with the same answer.

So, let's start right at the beginning.

So we're gonna start with basically sorting information.

You can see, if I look on this tab here, that've got, I've got a bundle of entries which have got a client, you know, a client name here.

The date that we bill that client, what we sold them in terms of products, how many of those items we sold, what the total sale amount was.

And also we've got an analysis by region as well.

So at the moment, they're just in a totally random order. They're not even in date order, but they're sort of, you know, in this transaction number.

So in some way, we've accounted for them and, and logged a transaction number.

So, how do we do something with this? Well, the very first thing we can do, if we click into our spreadsheet, click into the middle of our data, we can try and sort that data.

Now, you could do this using the menu item up here where I'm just sort of waving my mouse, uh, where it says sort and filter.

Of course, for most of these items, most of these things you can use keyboard shortcuts. And if you do a lot of this, then it's worth learning the keyboard shortcuts.

I'll tend to use the mouse just so it makes it really obvious where these items are within the menus.

But I'll just sort of show you how you can get into that sort menu, using the keyboard shortcuts while you click alt that gives you all of these letters at the top.

We click H for home.

And then we get the letters onto all of these, all of these options.

And this is the one we want over here, which is S, so I'm gonna click into sort, and then I'm just simply because I actually don't want that.

I want it to be in client's accounts.

So I'm gonna go into sort and filter, and I'm gonna sort A to Z.

Okay? So I do that and without any sort of fuss, it basically rearranges my entire loss of data in client account ascending order.

And you can see therefore that it groups together all of those accounts,1ASH.

So I'm gonna scroll down a little bit and I'm gonna grab this, second one and the second one. And the list is accounts 2BET.

Now if I wanted to, for instance, work out what was the total sales for, to account 2BET, that I can just click on the top item, hold down shift, and gradually work my way down and highlights all of these items all the way down to account 2BET a little bit further.

Let's go a little bit further until we get to the next one.

Keep on going.

It's quite a long way and it's a fairly cumbersome process, but it is feasible to do this off. If I go, keep on going, keep on going, keep on going. And eventually it'll turn into, there we are.

It'll turn into account three.

So if I just highlight everything that says account 2BET, then you can see at the bottom, Excel automatically adds everything up.

And it tells me that the total of all the sales to account 2BET is 28561.

And that's a feasible way of doing that. It's a feasible way of basically grouping the data, sorting the data.

Now, if I wanna be a little bit more specific, I can go into sort and filter.

And what I'm actually gonna do is I'm gonna highlight the entire table.

Because it makes it a little bit easier to navigate, and it helps me with the row headings.

I'm gonna highlight all of the table, all including the headings here, hold down, shift, hold down, control, hold, click bottom, arrow down.

And it highlights the entire table right down to that nearly 2000 lines of information.

I'm gonna go back to the top just so we can see how this works.

Go into sort and filter.

And now I'm gonna be a little bit more sophisticated.

I'm gonna jump into custom sort.

I jump into custom sort, and it now gives me, the opposite. It gives me the headings, and it enables me to be a little bit more sophisticated.

So if, for instance, I wanted to sort by client's accounts, which is what I've already done, but then I want to sort again, within those groupings, I can add a level.

And then I'm going to, I'm gonna click by perhaps, sales.

I'm gonna go into the sales amount sell values, and I'm gonna actually ask it to do largest to smallest.

Click on, okay, and it rearranges everything.

Now, if I go down to my accounts 2BET, which is a little bit further down, then it's still in the first instance sorted by, accounts names.

So it's still all of my account 2BETs here.

But you'll see now that the sales amount is now in reverse order.

It's starting at 286, and it's gradually getting smaller and smaller.

So if I wanted to sort and then work out, which was the biggest sale that we made to this particular account, it's this one right at the top.

And of course, you can basically nest or build up these reasonably sophisticated reasonably sophisticated queries pretty easily.

Uh, and it'll basically keep on sorting as much as you want. If I wanted to say, you know, first of all, do it by client's accounts, then do it by region, then do it by sort of reverse sale order.

I could work out what was the biggest sale to the east region, what was the biggest sale to the Midwest region and, so on.

So that's a good starting point.

It's a little bit clunky, it's a little bit time consuming.

It's perhaps not very sophisticated and elegant, but it works.

It's absolutely feasible to do.

Now let's click into the next one. Filtering.

Again, we're gonna jump into our table.

We just click our table once, make sure our cursor is in the middle of the table.

And now we're gonna go up to the same menu item up here. Again, we could do this with keyboard shortcuts.

We could do Alt Hs.

I'm gonna click into here so you can see the menu, and it basically says, turn on the filter.

So I'm gonna turn on the filter, and initially nothing much seems to have happened, but you'll see that I've got little dropdown menus next to all of these top items.

So what am I gonna do? If I click into one of those, if I basically click into account 2BET, sorry, sorry, client account, you'll basically see that it opens up this little dropdown box and it basically says, here are all of the accounts names, all of the people you have sold to over this period in this table of data.

And they're all currently ticked.

So it hasn't actually filtered, it hasn't actually reduced the numbers.

I'm gonna tick, click the top one, the select all, and it unicks it untick them all.

And then I'm just gonna click on the one I'm interested in that account 2BET click.

Okay. And you'll see that what it's done now is it's only given me account 2BET. There's nothing else. There's nothing else here.

It's basically filtering and just extracting this particular account.

Now, you'll also see that it's changed the, row headings. It's changed these row numbers to blue to indicate that they've been filtered.

But if you look a little bit more carefully, you'll see that the row numbers are sort of unchanged for these items.

So what's happened is it hasn't got rid of everything in between.

It's just hidden them. It's basically just effectively created, a row with nil heights.

They're still there.

So you can see it's just called out 5 and then 14, and then 26. Everything else is still there, but it's just hidden.

You can't see it. Now that sort of begs the question, what if I go down to the bottom of the sales accounts and I say, if I wanted to just find out the total sales to account two BET, if I go to the bottom here and say equals some as I normally would do, and basically highlight that's a entire um, column, what do I get? Well, I don't get that 28561 that I saw before.

I get 238,000, which is the total.

And that's because it's still adding up all of those hidden items, all of those hidden rows.

So I need to be a little bit more sophisticated.

So what I use is I use my alt equals, that old friend, that great keyboard shortcuts.

I hold down alt and I press equals and hit and hit return.

And you can now see that basically what it's done is it's basically given me that 28561.

But if I look at what's in the cell, it isn't some anymore.

It's something called subtotal.

And subtotal is a way of basically pulling out just certain elements with a certain feature.

And subtotal 9 basically says, I'm gonna add up everything that isn't hidden, so it's only pulling out the items that aren't hidden.

And that's why it now comes back with that 28561 as a subtotal.

So really powerful feature filter and subtotal will enable you to be very sophisticated.

Now, if we go to the top, again, we've already d already clicked the client's accounts.

This is super flexible.

So I could click maybe another one, just account 3CIC, and now you'll see that I've basically got these it's interleaving account 2BET, and account 3CIC.

I go back and I can basically turn off account 3CIC click.

Okay, and I'm back to just my account 2BET.

I can also, basically look at sub filter.

So a nested filter.

So if I go into location, I can click on select all, so everything goes off. And then just click on east.

And that's gonna give me just the sales to accounts 2BET in the east region.

And that's gonna give me a smaller number.

Again, my subtotal is working, and that's 8767 There we are. So really powerful, very, very powerful.

What's the next thing we're gonna look at? Next thing we're gonna look at is something called some ifs.

And I'm actually gonna start with something called some if.

So, we'll use a number of these different techniques, but I'm gonna start with something called some if, so I'm gonna click into my first blank cell, which is the equals F, and I'm gonna tar start typing some if equals, some if.

And Excel prompts me. It says, do you want some if or do you want some ifs for the moment? I'm just gonna go with some, if I hit tab and it accepts that, and I am now going to start using this feature.

And what some if does is it basically adds up a bundle of data, but only data that is in line with a particular criteria, only data that complies with one particular criteria.

So what I'm gonna do first is I'm gonna just look at the, the formula.

And the formula basically says some if range criteria, some range.

So same thing I'm gonna do, I'm gonna try and extract all of the sales to to account 2BET.

So the range that I want to look at initially is the accounts just the client's account.

So I want everything in here, but I only want to pull out the stuff that has got account 2BET within this particular column.

So I go to the very top, hold down, shift and control, and it goes all the way to the bottom.

It highlights the entire column, almost 2000 rows of data.

Then I hit comma, and I say, I want that to be equal to accounts 2BET.

Now I'm just gonna type these in and therefore put it in quotes for the moment.

But of course, I could actually put account 2BET in another cell and just point at that other cell.

and that would be my criteria, but I'm just being simplistic about this.

I'm just type typing it in.

And then it says, what about the sum range? What do you want to do when you found accounts 2BET in column C? Well, what I want to do is I basically want to add up, I want to sum everything in the sale amounts.

Now, I could do it in the units amounts, and that would be give me the total number of units that we sold to this clients, but we're just gonna go with the sale amount. So we hopefully recognize that same figure.

Again, I start at the top of column G, hold down, shift and control.

Go right down to the bottom, put a bracket around it hit return.

And I get exactly that same figure as before, the 28561.

Other things other, functions in this same sort of area.

I'm gonna do a count, A count if, so, I'm gonna say equals count if, and this just literally counts the number of items, the number of rows.

So I'm gonna do count if, and then I'm gonna say the range.

And I'm gonna go to the client account again, column C all the way down to the bottom.

And then I'm gonna say, accounts 2BET, close bracket, hit return.

And that basically says, I've got 217 individual lines of sales to account 2BET.

Now, the final thing I'm gonna put on here is something called some ifs.

And you might, if you haven't come across this before you might guess that if some, if looks at one particular criteria, some ifs enable you to focus on or pull up data for more than one criteria, and you can basically build off as many as you want.

So what we are going to do is, the question here says, how many units of money market products has account 2BET 2BET sold? So what we're gonna do is we're gonna start with some ifs, we open brackets, and then it gives us the structure.

It gives us the nature, the syntax of the function.

And it says, what are we? And it's a little bit different now. It's got the sum range at the beginning, and then each of the criteria that follow after that.

So I've got the sum range here.

So what I want to do, what I'm gonna add up is actually the number of units, not the sum not the sale amounts.

So that's my sum range for this particular query.

And then it says, and then what's my criteria range.

My criteria range is the client account again where I'm gonna make it equal to account 2BET.

And then I want to be that, I want that, I just wanna pull up the ones that are equal to account 2BET quotes around it.

And then what about my second criteria range? Well, now what I want is I want the type of product, and I want that to be equal. The question asked me about money market products. So I want to highlight that product range, that product column, and then I want that to be equal to money markets.

And you've gotta be careful with your typing.

Of course, it has to be accurate.

I hit return, and then I get a much a much smaller number.

So this is the number of money market products that we have sold to account 2BET.

And that sum, if it's really very powerful, that sort of nested nature of the that nested nature, of the search criteria.

Okay, next thing we're gonna do is jump into the next column, the next tab, which is pivot data.

And pivots tables are super useful, incredibly powerful, and very, very flexible, but really very easy to use.

So how am I gonna create a pivot table? I'm basically going to click into the middle of my pay off my table of data, and then I'm gonna use Excel's Helping Hand.

I'm gonna use Excel's features.

I'm gonna click into insert, and then I could click onto Pivot table and set it up manually.

But I'm a bit lazy. So what I'm gonna do is I'm gonna click on the recommended pivot tables and basically Excel will give me a pivot table.

Now, I don't mind if it's totally the wrong thing, if it's the wrong structure, I don't really mind because they're super, super easy to change.

So I'm gonna say yes, whichever is the first one it recommends.

I'm gonna gonna click on, okay.

And it opens up a new tab and it says at the moment it's basically ordering my pivot table by the number of units that I've, that, that have been sold.

Now that's not very meaningful.

So you can see over here there's a little pivot table sidebar, which enables me to drive the pivot table.

So at the moment it's basically saying I'm gonna order my pivot table by a unit.

The number of units sold, that's not really very meaningful. So the first thing I'm gonna do is I'm gonna click on that row and I'm gonna drag it back over to the field list.

And then what I'm gonna do is click on the client account, drag it to the rows, and you'll see that it's defaulted to the sum of sale amounts. That's pretty good. That's pretty useful.

Um, so just doing that, dragging the rows over by clients, basically it's now sifted and gone through my data and basically said, I'm gonna look at all of those client accounts, and I'm basically gonna go through and add up the sale column for just those just those, all of those clients.

And you can see this is the reason we can often did it this way.

You can see that that number that we recognize now accounts 2BET. I've got exactly the same 28561 that it's pulled out and said, that's the total of all of the sales to accounts 2BET.

And it gives me the same 239 thousand in total.

Really super useful.

Now let's make it a little bit more sophisticated.

What if I wanted to look to break down this by clients and region? So I've got a location here.

I'm gonna drag the region to columns.

And now what it does is it gives me those regions at the top east, midwest, south, and west.

And then the accounts names down the left hand side, I've still got the totals, so I can still see that total to account 2BET is 28561.

But it's basically made up of these four items, these four items adding that up.

What else can I do? I could say, actually I'd quite like it in a sort of a column format.

So rather than have it as a 2D format, I can basically drag this location and I'm gonna drag it to rows.

So I now split my rows into two sections.

So the first separation, the first sort of subtotal is the account.

So again, we see that account 2BET, it's 28561, and then it drags, and then it opens that up into the regions underneath.

And we can see that number that we've seen before 8767.

That's the sales for east. The same thing we saw before.

If we decided we want it the other way round we just literally drag these items.

So location comes first and then clients accounts afterwards.

And again, what we see is East account 2BET is the same, 8767, all super useful.

Final thing I'm just gonna mention basically if you click on the sum of sum of field settings, then basically you can, it gives you all sorts of, all sorts of all sorts of items.

I could say I, instead of saying some I could do counts.

So how many items do we see? And you can just see now we've got this many rows.

I think it's just interesting. We look right at the bottom.

We've got 1891. That's basically the number of rows that we had.

So it's just telling you how many items in each row.

So super useful, incredibly powerful, very, very flexible.

But couple of things just to mention pivot tables are by their nature one-off items, one-off queries.

You need to be quite careful coming back to these at a later date.

If you've added some data to the end of your table, you gotta be very careful that the pivot table refreshes still points at the entire table.

And generally what I would suggest with a pivot table is kind of do it once, do it as a one-off item, as a one-off analysis, use it, make use of that use it within that set of relatively standing data.

But certainly if you've got some data that updates that's particularly if the data gets bigger or smaller, you need probably to go back and refresh your pivot table.

I would actually, I was being very secure about it, I'd actually redo the pivot table.

Now, two other things that we're just going to do, I'm just gonna mention briefly conditional formatting, conditional formatting, very powerful feature once you've done some analysis to actually kind of put little highlights little telltale colors.

So the first thing I'm gonna do is I'm just gonna go to this table here.

We've got on the conditional formatting tab, again, exactly the same sort of, exactly the same sort of data, but we've pulled out these accounts to be the, these account labels.

And you can see that one, that number again, 28561.

If we wanted to basically show which are the bigger and which are the smaller ones, what we do is we highlight the data that we want to apply it to.

We go up to conditional formatting here, and you can basically see that if I go into color scales, I almost don't need to do anything here.

I just hover over them and I basically see that, see what they, what it will do.

So the color scales, basically.

The first one on the left this is quite a nice, this green one.

Basically it gives me a different shade of green for the biggest and the smallest on this one, you can see that the smallest number is white and the darkest number 28561, which is account 2BET is the darkest green number.

The one alongside, it basically just flips the scale around.

So my 28561, which is the biggest, is now the pale and the darker one is the smallest.

Lots and lots of really easy to use.

Conditional formatting. Have a look at the model answer.

There's about five or six different examples of this.

Final thing we'll do is something called d functions.

I'm gonna jump into d functions, and we've got about three or four minutes left, so we should be able to just dive into this and just show you what these are.

This is Excel basically pretending that it's a database, it's pretending that it's a database.

So I'll just show you one or two of these.

So what we're doing here is we've got this data at the bottom, exactly the same data we had before with the headings.

And what we're gonna do is we're gonna run a database query, and we're going to use the headings.

And these headings need to be, this is the query area.

And these headings basically need to be in exactly the same format as our main database table.

So I'll just show you how you would actually do this.

And, um, if I pop a number up here, I4, I'm gonna say equals D sum.

So what's D sum? D sum, basically D for database and sum for adding up.

So basically it says D sum is going to add up.

And what I need to do is I need to first of all define where my database is, where my database of original data is.

So I need to use my keyboard.

I need to go down and find the entire database, including the row headings.

So I go all the way to, B17, highlight all the headings, hold down, shift, hold down, control, hold, and click down arrow.

And it highlights the entire thing all the way down to the bottom.

And then what I need to do is I need to tell, excuse me, I need to tell it which field I want to pull out.

I want to pull out field, f which is the number of units, because this question is asking me how many units of loan products has accounts one a SH client sold.

So I'm gonna point to the number of units, which is F17.

And then all I need to do is to point at the database query area, which is this little block here, but I need to click keep.

I need to point to first of all the headings, and secondly, the row underneath, which is where the query, criteria are gonna sit.

I hit return and I hit bracket and then hit return.

At the moment, it's basically saying the total of all of the units for all of these items is basically 78707.

So if I simply added up column F, that's what I'd get.

But now what I can do is I can go and create my query in here.

So I go into client's accounts and I say accountS I don't say that.

I say accounts.

If I could type properly 1ASH, and you'll see if you keep an eye on that 78,707, you'll see it's fallen to 8516, basically because it's just now adding up the number of units to just this particular account.

If I then go into product and type loans, then you'll see that it falls again, it goes down to 1985.

So it's really super powerful way of basically pulling data out, pulling data out of a particular database.

I'm just gonna do one other.

There are lots of these D sum features, but I'm gonna say equals D average.

And exactly the same thing will happen.

It'll say, where is my database? So I go down to my database of information, then it says, what field do you want? I'm gonna say exactly the same. I'm gonna say F17.

I'm gonna look at the number of units.

And then where is my database, my query area.

It's exactly the same as I did before hit return.

And it's saying on average, we sold 41 to any particular clients, but what about to clients? And I'm gonna say just equals the line above.

What about to clients accounts? 1ASH.

And you'll see it's now gone up to 43 I could type the south region over here, and you can see it tweaks, it changes just a little bit.

There's lots of these. So D average, D sum, DMax DMin are all really great ways of drilling down into the data.

Now, this is, I think, is really super sophisticated.

You know, this is basically turning excel into a database and being incredibly flexible in terms of the way that, you are able to basically pull data outta that.

Phew. And that's it, as if by magic, it's exactly half an hour.

So I hope that little looking over my shoulder has been useful.

I can see we've got all the attendees still here, so I hope that's been useful.

Not expecting you to remember every detail of what I've just done, but you've looked over my shoulder and you've seen me doing this.

Now, hopefully, you know that this can be done.

Do have a look at both of the blank version of the spreadsheets and the fully populated version of the spreadsheet.

And do have a little go at trying some of these techniques yourself.

Okay, that's me done. Thanks every much very much for your attention.

hopefully, you've got some queries.

Use the link to jot some queries down and give us some feedback on the session.

I hope to see you on another of our webinars.

Okay, thanks. Have a great rest of your Friday and look forward to seeing you soon.

Okay, cheers. Thanks very much. Bye-Bye.

Content Requests and Questions

You are trying to access premium learning content.

Discover our full catalogue and purchase a course Access all courses with our premium plans or log in to your account
Help

You need an account to contact support.

Create a free account or log in to an existing one

Sorry, you don't have access to that yet!

You are trying to access premium learning content.

Discover our full catalogue and purchase a course Access all courses with our premium plans or log in to your account

You have reached the limit of annotations (10) under our premium subscription. Upgrade to unlock unlimited annotations.

Find out more about our premium plan

You are trying to access content that requires a free account. Sign up or login in seconds!

Create a free account or log in to an existing one

You are trying to access content that requires a premium plan.

Find out more about our premium plan or log in to your account

Only US listed companies are available under our Free and Boost plans. Upgrade to Pro to access over 7,000 global companies across the US, UK, Canada, France, Italy, Germany, Hong Kong and more.

Find out more about our premium plan or log in to your account

A pro account is required for the Excel Add In

Find out more about our premium plan

Congratulations on completing

This field is hidden when viewing the form
Name(Required)
This field is hidden when viewing the form
Rate this course out of 5, where 5 is excellent and 1 is terrible.
Were the stated learning objectives met?(Required)
Were the stated prerequisite requirements appropriate and sufficient?(Required)
Were the program materials, including the qualified assessment, relevant and did they contribute to the achievement of the learning objectives?(Required)
Was the time allotted to the learning activity appropriate?(Required)
Are you happy for us to use your feedback and details in future marketing?(Required)

Thank you for already submitting feedback for this course.

CPE

What is CPE?

CPE stands for Continuing Professional Education, by completing learning activities you earn CPE credits to retain your professional credentials. CPE is required for Certified Public Accountants (CPAs). Financial Edge Training is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors.

What are CPE credits?

For self study programs, 1 CPE credit is awarded for every 50 minutes of elearning content, this includes videos, workouts, tryouts, and exams.

CPE Exams

You must complete the CPE exam within 1 year of accessing a related playlist or course to earn CPE credits. To see how long you have left to complete a CPE exam, hover over the locked CPE credits button.

What if I'm not collecting CPE credits?

CPE exams do not count towards your FE certification. You do not need to complete the CPE exam if you are not collecting CPE credits, but you might find it useful for your own revision.


Further Help
  • Felix How to Guide walks you through the key functions and tools of the learning platform.
  • Playlists & Tryouts: Playlists are a collection of videos that teach you a specific skill and are tested with a tryout at the end. A tryout is a quiz that tests your knowledge and understanding of what you have just learned.
  • Exam: If you are collecting CPE points you must pass the relevant CPE exam within 1 year to receive credits.
  • Glossary: A glossary can be found below each video and provides definitions and explanations for terms and concepts. They are organized alphabetically to make it easy for you to find the term you need.
  • Search function: Use the Felix search function on the homepage to find content related to what you want to learn. Find related video content, lessons, and questions people have asked on the topic.
  • Closed Captions & Transcript: Closed captions and transcripts are available on videos. The video transcript can be found next to the closed captions in the video player. The transcript feature allows you to read the transcript of the video and search for key terms within the transcript.
  • Questions: If you have questions about the course content, you will find a section called Ask a Question underneath each video where you can submit questions to our expert instructor team.