Transcript
 Kelly:
Hi, this is Kelly Ford with Microsoft and today I'm going to give you a little bit of information on two aspects of Excel. We're going to talk about getting information on financial items such as stocks and other investments, and then we're going to talk about pivot tables.
When I talk through these items, don't worry about the exact keystrokes or commands. I'll send links to all the places where you can get the step-by-step information. What you should focus on is the concepts of what's possible. One other item. When you do try this, you will have to turn on some connected services in Excel. That is what allows excel to get information online. To do that you are going to press Alt F in Excel and then arrow to options in find general. In general, go to privacy and then turn on the checkbox for online experiences.
Couple other sort of housekeeping details before we get into the content. I may talk about some things that you haven't used much in Excel.
For example, in the portion of my presentation on stocks, I am going to use tables and I know we generally think of Excel as already being a table, but you can also have a portion of your spreadsheet be marked as a table specifically, that allows screen readers to automatically read column headers and when we get into the stocks is what allows some of the data to fill out automatically down a column.
You may also notice that the speech rate for Jaws screen reader that I'm using is a bit fast in recording a presentation, it?s never easy to pick the right speed. Again, I'm sending the written information so and my goal isn't that you hear Jaws say everything, but just that that you get the concepts. 
Last, I do talk a little bit about using ranges to make a selection list. If you haven't done that, don't worry. Again, I'll send you information.
That should be all the housekeeping things, and so now, let's move on to how you can use Excel to get some investment information.
In this part of my presentation, I am going to talk a little bit about how you can use some data type features in Excel. One with a couple other functions if you want to get investment information.
The data type is something that does require an Office 365 subscription and stocks are just one type of information. You can get a lot of this information from other places, but I like Excel for the ease of reading information and the consistency across experiences.
The details of how to use these items are also available online and as a part of the follow up to this presentation I will share links, so I wouldn't focus on the exact keystrokes and such, but rather just some of the concepts. So, in this case I'm gonna to start by typing just a couple of Top Stock ticker symbols.
So, I work in the high-tech sector, so we'll just use some of those. I typed MSFT?
JAWS:
Edit A
Kelly:
Type Amazon?s ticker symbol.
JAWS: Blank A3, Edit
Kelly: 
And apple.
JAWS: Blank A4, edit, edit
Kelly:
We'll do Facebook FB
JAWS:
Blank A5
Kelly:
And Alphabet or Google.
JAWS:
Edit, blank A6
Kelly:
So, in cells A1 through A5, I now
JAWS: Row A5, FB A4, ap A3, Am A2
Kelly:
Have those ticker symbols.
JAWS:
MSFT A1
Kelly:
I'm going to now select those cells with shift and down arrow.
JAWS:
Select tabs A2, select app A3, select FB A4, select Goog A5.
 


Kelly:
For reasons that I'll show you in a moment, I'm first going to make a table with this information, so I'm going to hit control T.
 JAWS:
Create table, create table. Where?s the data for your table? My table has header check box, not checked.
Kelly:
OK, and I'm going to say my table doesn't have headers.
JAWS: 
OK button
Kelly:
OK, and then I'm gonna hit OK.
JAWS:
Select Goog, A6, HSA Investment Options, A1, column 1, A6 Goog, A1, column 1, analysis lens, formatting tab, MSFT, A2
Kelly:
So right now we have
JAWS: Column 1, A1
Kelly:
On table in here
JAWS:
Drop down button
Kelly:
Because I told my excel that I didn't have column headers, it put a column in their header of column and I'm going to change that to name by typing name.
JAWS:
MFST, A2
Kelly:
Now I'm gonna to select my cells again.


JAWS:
Select tabs, A3, select app A4, select FB A5, select Goog A6
Kelly:
In this case I?m now gonna press Alt A for data.
JAWS: 
Upper ribbon, ribbon, ribbon, tabs, tabs expanded, data tab, menu, data sub, menu, easily discover, from text slash CSV, from web button
Kelly:
So, we're going to tab through this in this case.
JAWS:
From sheets button, recent sources button, existing connections button, queries and connections, Refresh All button, queries and connections button, properties button unavailable, edit links button unavailable, submenu, data types menu collapsed.
Kelly:
So, there you can hear Excel says data types.
JAWS: Leaving menus, data types table, recently used, stocks button 
Now it says stocks. I'm gonna press enter.
JAWS:
A2, Microsoft corporation, XNAS, MSFT, A6, alphabet inc, leaving menus, list stocks, volume 26 of twenty, A2, Microsoft corporation, analysis lens, formatting tab, name A1, column header, drop down button, Microsoft corporation, XNAS, MSFT, A2, contains stocks data type, show card control, shift F5.
Kelly:
In this case, those ticker symbols I typed were converted to the names. So, so far not overly exciting.
JAWS:
Amazon.com, inc, XNAS, AMZN, A3 contains apple inc, XNAS, AAPL, 
Kelly:
But because this has now been converted to a data type.
JAWS: 
Name, A1, Microsoft Corporation, XNAS, 26 of 20


Kelly:
I can press. Alt shift.
JAWS:
Microsoft corporation, XNAS, MSFT, A2, contains, show card control shift F5, volume 26 of 28.
Kelly: 
And, I have a list of items I can choose.
JAWS: 
Ticker symbol, shares, ticker symbol
Kelly:
In this case we'll pick ticker symbol.
JAWS:
Microsoft corporation, XNAS, MSFT, column header, no filter, drop-down button
Kelly:
We?ll do it again.
JAWS:
List box, ticker symbol, 25 of 28, shares, price, previous post, price
Kelly:
And we'll say price.
JAWS:
Microsoft corporation, XN, list box, price, 23 of 20, 
Kelly:
Now gonna hit control home.
JAWS:
Microsoft Corp, Name A1, column header, no filter applied, list box, price, 20
Kelly: 
And, 
JAWS:
Name, A1, column, first symbol, B1, column header, no filter applied, drop-down button.
Kelly:
Read across my first row.
JAWS:
Price, C1, column header, no filter applied, drop-down button, 
Kelly:
So, you'll notice my table automatically got the column headers.
JAWS:
Ticker symbol, Name, A1, 
Kelly:
More importantly.
JAWS:
Microsoft corporation, XNAS, MSFT, A2, contains stocks data type, show card control shift F5, ticker symbol, MSFT, has formula B2.
Kelly:
So, there we heard the ticker symbol.
JAWS:
Price, dollar 277.66 at formula C2.
Kelly:
And the price. So, I could add many other columns or I can go back.
JAWS:
Amazon.com, inc, XNAS, AMZN, list, share, outstanding, ticker symbol, AMZN at formula D3, price, dollar three thousand six hundred seventy-five point 74 at formula C3.
Kelly:
And if I want more columns of data?
JAWS:
Ticker symbol, AMZ, name, Amazon.com.
Kelly:
I can press Alt, Shift F10 again.
JAWS:
List box, shares, outstanding, 24 of 28, ticker symbol, volume, volume average.
Kelly:
And pick any one of these columns.
JAWS:
P slash E, open, official name, name, (something) tab, low, industry, exchange.
Kelly:
In this list I can also type the 1st letter of what I'm looking for. I'm going to type a 5.
JAWS:
52, Amazon.com, inc, ticker symbol, AMZN has formula D3, list box 52.
Kelly: Now?
JAWS:
Price, dollar, $3,675.52 week high, $3,685.48 at formula D3
Kelly:
That has been added to the information. So really, it's just a matter of typing some information. Then converting it to data type and then using Alt Shift F-10 to pick the information that you want.
JAWS:
Blank, E3, blank F3.
Kelly:
Now I'm going to go just to another area of my spreadsheet for minute and?
JAWS:
Blank M2, blank M1.
Kelly:
There may be times when Excel doesn't know the data type or you have to do some other work, so let's just type MSFT all by itself once.
JAWS:
Edit, blank M2, MSFT M1.
Kelly:
Now I'm gonna go alt A.
JAWS:
Upper ribbon, ribbon, ribbon, tabs, tab, tab expanded.
Kelly:
The shortcut for the stock state type or to get to that is Alt A, then D, then one.
JAWS:
Leaving menus, data types table, recently used, stocks button, MSFT, M1, data selection pane, MSFT M1.
Kelly:
Now in this case I have provide feedback with sound turned on in Excel and you will notice that we heard a sound and something else came up. So, this is, if Excel doesn't know about the data type or it wants you to make a choice,
JAWS:
Virtual PC
Kelly: you would read down
JAWS:
Picture of Microsoft Corporation
Kelly:
further.
JAWS:
Slower, slower, slow.
Kelly:
So, I'm gonna read down this information.
JAWS:
Microsoft Corporation list box item. Picture of select button, M1.
Kelly:
So here we hear that the cell is M1
JAWS:
Search query.
Kelly: My search query
JAWS:
At MSFT.


Kelly: 
Was MSFT I could actually change that ?cause you heard that was an edit.
***JAWS:
Search, cancel search button, search button, select A match and results, 

Kelly:
And then we heard MSFT and NASDAQ so I can press enter.
JAWS:
Microsoft corporation, XNAS, MSFT, M1, Microsoft corporation, XNAS, MSFT, M1.
Kelly:
Now that has been converted to the stock data type and Microsoft again with focus on this. There's a couple things I can do now. I can hit control shift F5.
JAWS:
Menu, leaving menus, power bi, data type, Microsoft Corporation, link data type.
Kelly:
And tab through some data. This is what the this called a card.
***JAWS:
$277 us dollars and 66 cents, 
Kelly:
And there you hear extract Microsoft Corporation to grid.
JAWS:
Kelly:
So, this is the same data that you can get from Alt Shift F10, but you can tab through it and explore it.
***JAWS:
(missing), price, dollar 277.66, extract price (missing) button.
Kelly:
So, this is all helpful. And now let's look at how you can put a lot of this together in an actual example.
So, I have a spreadsheet here with some investment choices from a health savings account, I happen to have.
JAWS:
Price history, price history, PSC, demo, demo, Microsoft Corporation, HSA, investment options, HSA investment, list box, exchange.
Kelly:
So, what I did is,
JAWS:
A, A1, column header, no filter applied, drop-down button.
Kelly:
The website where I was getting, having to look some of this information up.
JAWS:
Column header, no filter applied.
Kelly:
Kept changing and wasn't overly screen reader friendly so I just grabbed all the ticker symbols from my investment choices.
JAWS:
A, A1, column header, no filter applied, drop-down button, list box changed, 1 of 20. 
Kelly:
And then, I built a table of that information.
***JAWS:
(missing), contains stocks data type, show card control shift F5.
Kelly:
And just like,
JAWS:
List box changed, 1 of 20, price, 71.56 as formula C2.
Kelly:
I showed earlier with the ones I had manually entered. I've done the work already here to build this, and then I,
JAWS:
Change percent, -0.11% at formula D2.


Kelly:
Built my table so now I can read through this and get the information.
JAWS:
Change, -0.08 at formula E2, return YTD, 5.65% at formula E2.
Kelly:
So here I can hear that you know the five-year return on that investment, was the number that we heard.
JAWS:
Return 7.54%
Kelly:
My apologies, that was the return YTD.
***JAWS:
(missing), 7.54%, has formula, expense ratio, 0.16% has formula H2.
Kelly:
The, the other thing is, another column I can add his expense ratio. And I always like to understand that because it might be a great return, but how much are you paying for that? So, I can arrow up and down.
***JAWS:
MFS International, (missing) 0.62% has formula H3.
Kelly:
This column now,
***JAWS:
Natixis Corp (missing), 0.47% has formula H4, BNY Media (missing), 0.73%, (missing), Pimco (missing), 0.75%, list box, change, 1 of 20.
Kelly:
And explore all of this information. 
JAWS:
Name, expense ratio, H1, column header, no filter applied drop-down button. 
Kelly:
I can also sort by typing. In this case, we want to find, I can just type ASD.


***JAWS:
Ribbon, ribbon, ribbon, leaving menu, (missing), 
Leuthold grizzly schwartz fund, 1.60%, name, expense ratio.
Kelly:
To sort most expensive to least expensive.
JAWS:
Leuthold grizzly schwartz fund, 1.60%, has formula H2, 1919 financial services fund, 1.46%, has formula H3.
 Kelly:
So, this is kind of helpful. Again, you could put any stocks or symbols you want here. Last, I've done a couple other things that for me I find helpful in this example.
JAWS:
A, A1, column header, no filter applied drop-down button.
Kelly:
14:02  So, I made a range of this first column. In Excel you can select all the cells you want and press control F3 and then that is what's called a range and you can use that in formulas.
So, what I did is I built a second spreadsheet, because I want to get the price history and such for an investment.
 JAWS:
Demo, demo, Microsoft, price history, price history.
 Kelly:
So, I switched to my price history sheet.
JAWS:
Blank A1, investment B1, column header, no filter applied drop-down button.
Kelly:
In this case, again, I put a ticker symbol in.
JAWS:
Pimco stocks plus small funds, list has drop-down, investment selection use alt plus down arrow to open the investment list and arrow followed by enter select, B2 contains data validation.


Kelly:
But what I actually did, instead of putting the ticker symbol in, I used Excel?s data validation feature. Which is again available on the data ribbon, and I said I want a list and for the source of that list I used the range that I had created. And then I did the same thing where I added the other columns of information I want to get, a detail, so,
JAWS:
Price, dollar 11 point 74 at formula C2, investments, PIMCO stocks plus small funds.
15:19 Kelly:
But now, because this is a range and I've said show that range as a drop-down list, I can hit alt down arrow, 
***JAWS: (do we need all the text while searching the doc?)
PIMCO stocks plus small funds, seven slash, seven slash, 2020, PIMCO stocks small funds, (missing), Columbia balance fund, I2. 
Kelly: 
And pick the stock I'm interested in.
***JAWS:
Wells Fargo, (Kelly scrolling through options), PIMCO stocks short fund, list, PIMCO stocks short fund, list has dropped out.
Kelly:
Again, this same information was available on my first sheet.
JAWS:
Price, dollar 8.95 at formula C2.
Kelly:
But what I've done now is, I'll show you why I want it this way.
JAWS:
Change percent, zero point one one percent, at.
Kelly:
Because on this spreadsheet,
JAWS:
Price, dollar 8 point 9 investment, PIMCO stocks plus short fund. List box changed, 1 of 20.

Kelly:
I'm, actually,
15:59***JAWS:
PIMCO stocks plus short funds, (missing), list box changed, 1 of 20, PIMCO stocks plus short funds, list has dropped out, investments, selection use alt plus down arrow to open the investment, list box changed, 1 of 20, price list (missing) tab.
Kelly:
Going to use another function. That is, the price history function in Excel. Again, that's a function that's available to Office 365 subscribers. But, so what? I've done now,
JAWS:
Start date, A3.
Kelly:
In cell,
JAWS:
Seven slash, seven slash, twenty twenty has formula B3, start date A3.
Kelly:
A3, I've said start date.
JAWS:
Seven slash, seven slash, twenty twenty has formula B3.
Kelly:
And then B3 I put in my date.
JAWS:
Blank, C3, seven slash, seven slash, twenty twenty has formula B3. Seven slash, seven slash, twenty one has formula B4.
Kelly:
And then I made a formula for how long do I want, 
JAWS: 
Equal today left para, right para.
Kelly: 
The information
JAWS:
Seven slash, seven slash, twenty twenty has formula B3. Edit equals B4 slash three hundred sixty-five.
Kelly:
So here for my start date, I've said today's date minus 365.
JAWS:
Seven slash, seven slash, twenty twenty has formula B4.
Kelly:
For my end date, I just use the Today function to say it's going to be today. I could change this.
JAWS:
End date A4, blank A5.
Kelly:
Now I'm going to arrow down a little bit more.
17:15 ***JAWS:
Blank A6, PSTIX has formula A7, contains formula (missing)
Kelly:
So, this is the symbol, where I'm getting the price history and you heard Excel say that it has a formula.
17:24***JAWS:
Edit equals stock history B2, B3, B4, 0,2,0,1 (missing).
Kelly:
Again, there's a lot here, but this is out on the web of how this actually works. But what I'm saying is I want the price history for the ticker symbol that I selected for my start date and end date, which is 365 days. And now I can arrow up and down. 
17:49***JAWS: (is JAWS saying ?values built from? or ?values billed from??)
A, A8, contains values built from A7, seven slash, seven slash, twenty twenty A9, contains values, dollar six point three five, B9 contains values built from A7.
Kelly:
And I haven't set this up so that the dates read as I'm arrowing. I'd like to just move down the list just to get a sense of how prices fluctuated.
JAWS:
Dollar six point three o, Visa, dollar six point three four.
Kelly:
So, I can do that.
***JAWS:
Dollar six point two eight, B12, contains values (missing) from A7.
Kelly:
So, this is just a little bit of information about how you can get some stock and investment information in Excel. Again, we started by entering some ticker symbols. We then optionally converted that to a table just so that some of the automatic filling of information works. We then chose data type from the Excel ribbon and stocks to have that ticker symbols converted to a stock data type. We then used Alt Shift F10 to pick additional columns that we wanted to display, such as the actual ticker symbol, even though we had typed that the price and whatever other information you want.
And then I also took all that information and built it into a more robust spreadsheet where I converted a bunch of ticker symbols to a range as well. Used that as a validation source for a drop down list to look up information and then combine that with the price history function in Excel to get the price history for a stock. 
Hopefully this helps a little bit, and again, we'll follow up with links and such where you can get the exact syntax for all of this.
We're now going to take a look at another handy feature in Excel known as pivot tables. Pivot tables allow you to look at a large amount of data in various ways, so that you can make easy determination about key points. Part of the reason why they're called pivot tables is because it's very easy to change the sort of view that you have on the information. We're going to start with a simple example of some financial transactions similar to what you might download from your bank or credit card company. I've downloaded a few of my older transactions and this has columns in the following order.
 JAWS:
Transaction, date, A1, column header, no filter applied drop-down.
Kelly:
Up so this has date.
JAWS:
Transaction, B1.
Kelly:
Transaction.
JAWS:
Amount, C1.
Kelly:
Amount.
JAWS:
Business, D1.
Kelly:
Business.
JAWS:
Category, E1.
Kelly:
And the category column. Now the category column was one that I added.
JAWS:
Dining, E.
Kelly:
We?ll review a couple of the transactions.
JAWS:
One slash, two slash, twenty sixteen A2. Transaction, debit, B2. Amount, minus twenty-one point four one C2. Business, SQ, star curb jumpers 3D Seattle, D2. Category, dining, E2. 
Kelly:
So, there we can hear that, that was from early 2016 and about $22.00 from a business called Curve Jumper and the SQ I know just comes from the square payment system.
So, I have about 25 transactions here and I want to make a pivot table because I'd like to know sort of how I spent money and things.
So, to add a pivot table, I've already turned this data into a table as I talked about earlier. What I'm going to do now is hit Alt N,
JAWS:
Date, A1, column, upper ribbon, ribbon, ribbon, tabs, tab, 
Kelly:
For insert.
JAWS:
Insert, tables, pivot table split button.
Kelly:
In this case, I'm just going to accept the default pivot table if. I wanted to understand more choices I could hit alt down arrow to expand that split button.
JAWS:
Leaving menus, leaving ribbons, blank, column.
Kelly:
Excel is gonna to prompt me for where the data is. Again, I can just accept the default right here.
JAWS:
Choose where you want, choose whether you want to add.
Kelly:
And where I want the data.
JAWS:
Choose where you want the pivot table to be placed. The worksheet radial button.
Kelly:
I want it on the new worksheet.
JAWS: 
Choose, OK button, sheet one, sheet one, blank A3, pivot example.
Kelly:
So now I'm into the pivot table spreadsheet. This is just like a regular spreadsheet, but if we press F6 a couple of times.
JAWS:
Sheet one tab, pivot table fields, type words to search for.
Kelly:
We get to the pivot fields control. I can search for the fields I want to manipulate, but I prefer to arrow through them.
JAWS:
Date, check box unchecked.
Kelly:
Now when you build a pivot table, you can put information in one of four areas. Those areas are the report filter, to limit kind of what you're viewing or adjust it. The rows, the columns or the values. You do not have to put something in all of those columns.
JAWS:
Transaction checkbox unchecked.
Kelly:
In this case,
JAWS:
Amount checkbox unchecked.
 Kelly:
I?m gonna put the amount in the values field because I want Excel to add that up.
JAWS:
Context menu, add reports filter, add the column labels.
Kelly:
I do that by pressing shift F10 or the computer application key and arrowing down to where I want to place this information.
JAWS:
The values.
Add the values.
Kelly:
In this case, we're going to add it to values.
JAWS:
Leaving menus, amount, checkbox un.
Kelly:
I'm actually gonna edit a second time for reasons that will become obvious when we explore the pivot table.
JAWS: 
Context menu, add thorough labels, add, add the values, leaving menus, amount.
Kelly:
And in this case, the other item that we want is the business name.
JAWS:
Business, checkbox unchecked.
Kelly:
Or just business. I'm going to add this to rows right now.
JAWS:
Context menu, add the reports filter, add thorough labels, leaving menus, business, check box unchecked, business, check box.
Kelly:
I've now built a pivot table. If I want to explore the fields I've added before, I go look at the data I can tab.
JAWS:
More tables, dot, dot, dot, columns, values button.
Kelly:
So there excel tells me,
JAWS:
Rows, business button, value, sum of amount button, sum of amount two button, rows, business button, columns, values button.
Kelly:
What information I have.
I'm now going to press control shift F6 twice. 
JAWS:
Sheet one tab. 
Kelly:
Because the first time I blank on the tab row or the sheet list,
JAWS:
Blank, A2, Row Labels, A3, no filter applied drop-down button.
Kelly:
Now I'm into my pivot table and I can start exploring this. So again, the business name is going to be down the leftmost column.
JAWS:
Amazon A4, column header.
Kelly:
So, my first column is Amazon.
JAWS:
Sample amount minus twenty-two point nine six.
Kelly:
So that indicates for this period of transactions I spent a little over $22.00.
JAWS:
Sample amount 2, minus twenty-two point nine six, C4
Kelly:
Couple things, so I've added that a second time, as I said, I would and we hear the same information. But one of the things you can do in pivot tables is change how you're viewing the information. You can change it in a couple of ways.
JAWS:
Menu, copy.
Kelly:
Again, shift F10 or the application key.
JAWS:
Remove, summarize values by sub menu, summarize values by.
Kelly:
In this case.
JAWS:
Show values as submenu, show values.
Kelly:
We want to change how we're showing the value.
 JAWS:
Leaving menus, menu.
Kelly:
So, I hit right arrow.
JAWS:
No calculation check.
Kelly:
Right now it's set to no calculation.
JAWS:
Percent of grand total.
Kelly:
But to add value to this, what I really wanted to be is percent of grand total.
JAWS:
Leaving menus, 2 point one one percent. 
Kelly:
So, on this sheet, right now the Amazon number represents, as you heard, 2.11% of what I've spent. I can also sort this information because clearly, I would like to probably do that. Again, Shift F10 or your application key.
JAWS:
Menu, copy, format, number, refresh, sort sub menus, sort, remove sum of amount two, sort sub menus, sort, sort smallest to largest.
Kelly:
Because negative numbers in this case is what I'm most interested in, because those are expenses. We?re gonna change this to smallest to largest.
JAWS:
Leaving menus, 21 point nine three percent, C4.
Kelly:
So now we hear, 
JAWS:
Sum of, Best Western, A4, column header. 
Kelly: 
A hotel stay, or stays, I don't know how many yet, was my biggest expenditure in this period and that was 21% of my spending.
JAWS:
Sum of amount, minus 238.3.
Kelly:
Or $238.00.
JAWS:
Sum of, Safeway, fifteen point four one percent, C5.
Kelly:
So here we heard Safeway at 15%.
JAWS:
Fred Meyer, nine point seven five.
Kelly:
Fred Meyer, another store. And we can hit control down arrow to go to the end of this.
JAWS:
Grand total 100.00 percent, C29
Kelly:
So obviously the total percentage is 100 because that's all the numbers, but we want to go left one column.
JAWS:
Sum of amount minus one thousand eighty-seven point o six.
Kelly:
So, this indicates that my total spending for the period of transactions here was 10,000 or $1087.00. But there's an important point, this transaction I know includes some payments.
JAWS:
Payment electronic, 250, B28.
Kelly:
So, if I want to exclude that,
JAWS:
Payment electronic, A twenty.
Kelly:
I can go and say, again, shift F 10 or the application key.
JAWS:
Menu, copy, formats, refresh, sort sub menu, filter sub menu
Kelly:
And I can say filter.
JAWS:
Clear filter from biz, hide selected items.
Kelly:
Hide selected items.
JAWS:
Leaving menus, grand total 828, sum of amount minus 1337 point.
Kelly:
So, the total spending now for the period that these transactions represent was thirteen hundred and thirty-seven dollars.
JAWS:
Blank, A1, blank, row labels, sum of amount, best western, sum of amount two, seventeen point eight three percent, C4.
Kelly:
So, you can see that hotel percentage went up a little bit. And if you don't like these column names, which obviously I don't want it just to say sum of amount 2,
JAWS:
Sum of amount two, C3, column header.
Kelly:
I can just type a new column name.
JAWS:
Edit (typing new column name Percentage), Best Western, 17.
Kelly:
So, I can do this at any time just by downloading my transactions from my bank and build my own pivot tables to review information. I know there's other ways to do this, but this can be very handy. This is just one example. I'll show you another example where you can use pivot tables to explore a large amount of data.
Some of you may be familiar during COVID about the government loan program or called PPE program. And as you probably heard in the news that information you know where the money was spent or who got loans was a made available to download. So, I've downloaded for the state of Wisconsin.
JAWS:
Spending, spending, Wisconsin PPE data, Wisconsin.
Kelly:
A spreadsheet from the government of this. Now this has.
JAWS:
Loan amounts, A1.
Kelly:
I'm in control home to go to the top and I'm going to hit control down arrow.
JAWS:
200 Forty-one thousand nine hundred twenty-three, eighty nine thousand one hundred sixty-five.
 Kelly:
89,000 and a little over rows of data. So, there's no way that I'm ever gonna, you know, study 89,000 rows. But I reviewed the columns that were available and I made a pivot table of the information I want. So, I'm gonna go to that pivot table once.
JAWS:
PPE pivot, PPE, pivot, opening.
Kelly:
So again, I'm gonna press F6 to go to the pivot table field.
JAWS:
PPE pivot tab, pivot table fields, type, loan amount, box checked.
Kelly:
So, this is the list of fields that I could add.
JAWS:
More tables, dot, dot, dot, columns, business type button.
Kelly:
So here we say here that my columns,
JAWS:	
Rows, city clean button.
Kelly:
Here's what's called city clean.
JAWS:
Values, sum of loan amount button, deferred layout update check.
Kelly:
And my values.
JAWS:
Values, sum of loan amount button.
Kelly:
Are the sum of the loan.
JAWS:
Rows, columns, business type button.
Kelly:
And I actually edit columns this time of business type. So, let's see how this plays out. Again, we'll hit control shift F6.
JAWS:
Albany, A11, column header, PPE.
Kelly:
So, what this has done is for the state of Wisconsin, it has listed the cities.
JAWS:
Blank, B11, Albany, Afton, A10, Adell A9, Adams A8, Abrams A7, Abor Vitae A6, blank, blank, C6, two million thirty-seven, corporation, z, list, pivot examples, excel, pivot, Abbottsford, employee stock ownership plan, MS corporation, plan B5. 
Kelly:
So, let's start at the first one.
JAWS:
Abbottsford, A5, column header. 
Kelly:
Abbottsford. Now if we hit right arrow.
JAWS:
Cooperative, blank, B5. 
Kelly:
So, no businesses in Abbottsford were classified as a cooperative.
I don't want to arrow through all the  cells, so I'm gonna use a command in Excel to move to the first cell that has data. In this case, I'm going to hit control right arrow.
JAWS:
Corporation, two million thirty-seven thousand.
Kelly:
So, the city of Abbottsford had businesses that got 2,000,000 in PPE Loans 
JAWS: 
Corporation.
Kelly: 
If I wanna see 
JAWS: 
Abbottsford.
Kelly: 
The total for the state, I can hit control N.
JAWS:
Grand total, grand total, nine billion eight hundred ninety-one million seven hundred
Kelly:
And now I hear that throughout the state of Wisconsin in this program there was $9.8 billion. If I arrow to the left.
JAWS:
Blank, trust, one million 708.
Kelly:
Businesses that were classified as a trust got a,
JAWS: 
Blank, trust, one million seven hundred
Kelly:
1,700,000.
***31:40JAWS:
(missing)

Kelly:
The point isn't that this data would be what you want to look at, but it shows you that you can very easily build powerful views of large amounts of data. The other thing to know is, again, we could sort this.
JAWS:
Blank, A, blank, sum of loan amount, A, row label, cooperative, four, column.
Kelly:
So, we'll go over.
JAWS:
Blank, blank, blank, blank, E3, blank, E2, independent contract, grand total T4, column header. 
Kelly:
To this grand total column.
JAWS:
Abbottsford, five million, menu, col, field list, form, number, refresh, sort sub menus, sort, sort smallest to largest, sort largest to smallest.
Kelly:
And we'll say sort, largest to smallest.
JAWS:
Leaving menus, one billion, trust, one hundred forty, trust, row labels, A4, Milwaukee A5.
Kelly:
So now the top column, or row I apologize is Milwaukee. And we can see the order of cities. And as you can guess,
JAWS:
Madison A6
Kelly:
It's going to go by population, for the most part.
JAWS:
Professional association, self-employed, sub chapter s, trust, grand total, six hundred sixty million. 
Kelly:
So now I've moved over to the total common. I could go down and see throughout.
JAWS:
Milwaukee, one billion two hundred fifty.
Kelly:
Wisconsin, who got money.
JAWS:
Madison, six hundred sixty, Greenbay, three hundred nine.
Kelly: 
I am originally from a town in Wisconsin called Fond du lac.
JAWS:
(Kelly scrolling quickly down the Grand Total list T5-Fond du lac), Fond du lac, ninety-nine million eight hundred. 
Kelly:
So, it was $99 million of loans to people in Fond du lac.
Now I'm really curious if I wanna drill into the details. So, this is great I can build this pivot table and get the high level details. But I can also again press shift F 10 or the application key.
JAWS:
Menu, cop, formats, refresh, sort, remove, summarize, show values as sub menu, show details.
Kelly:
And if I use the show details option, a new spreadsheet will open-up with the details based on the interception of the number I'm looking at. So, all of the businesses in Fond du lac only the in this case.
JAWS:
Sheet two, sheet two, A1, loan amount w seven hundred, analysis lens, format, A1, loan amount.
Kelly:
Now this becomes a new sheet that I can look at.
JAWS:
Analysis lens, format, A1, loan amount, W seven, analysis lens, formatting tab.
Kelly:
We've just scratched the surface on what you can do with pivot tables. Again, the basic process is obtain your data either by downloading it or using some of excels connectivity options. There's a whole host of options for connecting to information.
Then, make sure that data is in a tabular form and for ease of use, also convert it to a table again by selecting all the data, pressing control T and indicating that it has column headers. It makes things a little bit easier, especially if you add more data and things like that. 
Then go to the Insert menu so you want to insert a new a table and a pivot table, so you want it on a new spreadsheet and choose OK.
On that spreadsheet, go ahead and press F6 a couple times until you get to the fields.
And then arrow down and bring up the context menu with either shift F10 or the application key and choose where you want the fields to go. Either report filter, rows, columns or values.
And you can add multiple items to the rows, multiple things to the columns or as we saw in my example, we added the values twice for the same information because we wanted to view it two different ways, both as a raw number and then as a percentage. So, I hope this gives you a little bit of an idea on how to use pivot tables in addition to getting from the first part of the presentation some financial information. I thank you for your time.