This video provides an introduction to Microsoft Excel, covering its basic definition, history, and fundamental components like cells, rows, and columns. It highlights the significance of spreadsheets in computing history, particularly VisiCalc as a "killer app," and explains how to access and utilize Excel's documentation for self-sufficiency. The video concludes by outlining the participation activity for Week 2, which involves researching and posting about an Excel function.
Here is the transcript broken into three chunks:
Chunk 1:
[Music] In this video, we're going to be giving a very brief introduction to Microsoft Excel. And we're going to be expanding on this brief introduction in the tutorial videos this week. And we're also going to be discussing what we'll be doing in week two of the course. So this week we have a few different learning resources for you that's going to help get you up to speed on Microsoft Excel. The first one is going to be the textbook and knowledge extension 4 is going to have a brief overview of what Microsoft Excel is and what it's used for in business. Then I highly recommend looking at the PDF slides and tutorials. So, this is going to be a long PDF document that goes through the different functions and features of Excel and teaches you all about writing Excel formulas. The last resource and probably one of the most important ones are the Excel video tutorials. This is going to be one long YouTube video that you can follow along with and practice your Excel work. So, the best way of learning this is by actually doing it. So, I highly recommend having Microsoft Excel open, downloading some of these Excel workbooks, and following along with this tutorial. It's the best way to learn is by actually trying out some Excel problems. So, moving on, let's give a quick introduction to what Excel is. Now, once again, we're going to be focusing on the what in this video, whereas the how to use it will be covered in the video tutorial. So you might be asking what is a spreadsheet? Well, we have a few textbooky type definitions here. They're computer program to organize and analyze data in a tabular form. And it presents a grid of cells, each of which can contain text, numerical data, or the results of computation. But let's take a look at what that actually means. Here we have a screenshot of Microsoft Excel 2016. Excel is the most popular and commonly used spreadsheet software. Recall that on that last slide we stated that spreadsheets are tabular and present grids of cells and you can probably already sort of see the tabular form here. This means that we have a table of horizontal columns and vertical rows. When we talk about columns we refer to them by the letter shown on the top here. For example, we might say column A, column B, column C, and so on. When we cast get past column Z, it will add a second letter. So it' be things like column A a column A B column A C and so on all the way up to column XFD where it ends. Rows are referred to by number. So we might have rows like one, row two, row three, row four, and they go all the way up to 1,48,576. That may seem really big, but if for some reason you need more rows or columns or you're starting to hit the limits of Excel, that means you're probably not using Excel for its intended purpose and you should probably consider splitting that into multiple sheets or moving to a database management system like Access, which we will be covering later in the course. Now, the important part is what is contained in these rows and columns, which are cells. Cells are a single piece of data or more properly called datim. Cells are referred to by their cell address. This is a combination of the column letter followed by the row number. For example, the cell we have highlighted right now in the green is called E4. That's because it's in column E and is in row 4. This naming scheme gives each cell its own unique address. That will become important later on when we start referencing cells and formulas. So cells can contain single number piece of text in computer science we usually call that a string or a formula. So formulas are equations that set the value of a cell based on the result of some calculation rather than a hard-coded value we type in. We'll get far more into this in the video tutorial. So don't worry about if you don't quite understand it yet. Cells also have formatting associated with them. This has two parts to it. First is the type of formatting you'd be used to using programs like Microsoft Word. This would be things like setting fonts, font sizes, colors, backgrounds, borders, and so on. This formatting would be purely for appearance reasons, and it doesn't have any impact on the data or calculations. Kind of just makes it more human readable and human usable. The second type of formatting determines how Excel interprets the data in the cells. This is commonly referred to as number formats or number categories. And what this does is based on how you set the number category or number format rather, Excel will interpret the data as text, numbers, currency, dates, percentages, and so on. Next, we should discuss what we call this whole thing. An Excel file is referred to as a workbook or an Excel workbook. Whereas individual sheets or tables of rows and columns is referred to as a worksheet or just a sheet. A workbook can contain multiple sheets up to 255 sheets per workbook. Sheets allow you to better organize your data and calculations and make things even easier to work with rather than just having one massive table of data. So now that we have a little bit of an idea of what spreadsheets are, we'll learn far more in that video tutorial. You might be asking why are they important? Well, the idea of spreadsheets have been around and used in accounting for hundreds of years. It's only since the 70s, however, that they have been electronic. For this, they'd be largely done on paper in ledger books or large handwritten tables. And all of those calculations would be done manually. In fact, that word computer that we're so familiar with originally referred not to electronic machine or device, but it was a job title for people that would be responsible for simply doing manual calculations on large tables of data. Shown here is a picture of a computer room from the 1940s. In particular, this is a computer room for the National Advisory Committee for Aeronautics or NACA. But you might notice something is a little bit missing from this computer room. At least as a computer room we'd understand it today. And that is there's no computers here. At least not as we now understand them. Instead, the computers here were human computers who would manually transcribe raw data from cellulose film and then use slide rules to perform calculations. So it's sort of interesting to think about how long this must have taken and how much manpower it must have taken. Nowadays, this has been replaced by things like Microsoft Excel and databases that can allow someone to do the same calculations quite rapidly. Spreadsheets also played a very important role in computing history. They're one of the first killer apps that made businesses start to invest in computing hardware and software and bring about acceptance of the personal computer both for businesses and home users. They made a reason for the consumer to want to have this I have some optional readings here that you can read if you're more interested in the history of computing, but one great documentary I'd suggest you watch is Triumph of the Nerds. You won't be graded on that, except for this little part that I'm going to show you now. The enthusiast market had its limits. To reach the rest of us, the Apple 2 needed what nerds call a killer application. software that's so useful that people will buy computers just to run it. For the Apple 2, this application was called VisiCalp. It came straight from the blackboards of the Harvard Business School invented by a graduate student Dan Bicklin with his programmer friend Bob Frankston. VisiCalc was the first electronic spreadsheet. A spreadsheet is a tool for financial planning, bringing together for the first time the seduction of money with the power of microcomputing. Dan Brickland's professor at Harvard showed how companies used a grid of numbers on a blackboard to work out profits and expenses. 60 down here and your profit would be this minus this, which gives you 40. And then, well, let's see what's the sales growth. We'll say there's a a 10%. The trick to a spreadsheet is that all the values in the table are related to the others. So changes in one year would ripple through the table affecting prices and profits in subsequent years. Students were asked to calculate how future profits would be affected by various business scenarios. It was called running the numbers and they did it laboriously by hand. Well, let's say your initial costs have 100 fixed cost at the beginning. So now you have a minus 20 is how much you make the first year and then the second year you have 100 but your your variable let's say is is 25. So now you're you're losing what is it? Um see there's a pain in the neck. I wasn't very good at this stuff. 80 what? No no no. Uh we failed. We just lost minus 15. Right. And then eventually you're making money. What year do we make money and you know and how much does the cost of money? That's what running the numbers was because each value was linked to the others. One mistake could mean disaster. It blows all your numbers afterwards because you make all your calculations based on other numbers before them. If I had miscalculated, Dan, who had worked as a programmer, started daydreaming about how he could use a computer to replace the tedious hand calculations. I imagined that there was this magic blackboard that did like word processing does word wrapping. If you make a change to a word, it automatically pulls everything back. Well, why not recalculate the same way? So that if I changed my number, you know, I should have used 10% instead of 12%. Uh I could just put it in and it would recalculate everything, go through it, you know, and that would be this this this idea of electronic spreadsheet. Following a model that's common today, Dan Bricklin designed the program, but got his friend Bob Frankston to write the actual computer code. After months of programming late at night when computer time was cheaper, the Harvard Business School blackboard came to life. Now we've set this up. Okay. Then we type a new value in. Okay. Here I'm going to take that 100 and I'm going to change it. All right. And here it recalculated. Wa. That saved me so much time. People who saw it and went and got it. Been an accountant. Remember showing it to one around here and he started shaking and said that's what I do all week. I could do it in an hour. What I could do you know you know and they would take their credit cards and shove them in your face. I meet these people now. They come up to me and say I got to tell you you know it's you changed my life. You changed my life. You made accounting fun and you have to remember what it was like in those days. We didn't want we did not use the word spreadsheet because nobody knew what a spreadsheet was. I came up with the name visible calculator or visi because he wanted to emphasize that aspect. Vizel hit the market in October 1979 selling for $100. Marv Goldmid sold the first copies from his computer store in Bedford, Massachusetts. After a slow start, Vizel took off. What it did in our society, it gave people who were obsessed with numbers, whether they're in business or at home, how much am I worth today? What's my stock portfolio worth? How am I doing against budget on this project? It gave them the ability to play with scenarios and change it and say, well, what if I do this? So, put people in a sense in control of the thing that lots of people in our society feel is driving them, and that's numbers.
Chunk 2:
The spreadsheet was every businessman's crystal ball. It answered all those what if questions. What if I fire the engineering department? What if I invest $10 million in pantyhose futures? Look, I'll be rich in under a year and have slimmer thighs at the same time. The computer says so. The effect of the spreadsheet was enormous. Armed with an Apple 2 running visit, the 24year-old NBA with two pieces of dubious data could convince his corporate managers to allow him to loop the corporate pension fund and do a leverage. It was the perfect tool for the 80s, the me decade, when money was everything and greed was good. But not all the PC pioneers made great fortunes. Dan Brooklyn decided not to patent his spreadsheet idea. Though more than 100 million spreadsheets have been sold since 1979, Brooklyn and Frankston haven't earned VisiCal royalties in years. You know, looking back at how successful a lot of other people have been. It's kind of sad that we weren't as successful. It would be very nice to be gazillionaire. So, that was a small segment from the Triumph of the Nerds documentary. And the important takeaway here is that Vizyelk, the first spreadsheet software, was that killer app that really got personal computers going. It's what made them sort of useful to the home consumer as well as the business consumer and made them marketable and acceptable by the general public. One last thing I should talk about is how you can find and use the Excel documentation. In the real world, users of Excel don't memorize every function and formula. rather they rely on the documentation provided by Microsoft to look up the functions they need and understand how to use them. Being able to use this documentation correctly is very important skill as it allows you to be self-sufficient and expand your Excel knowledge as you need it. In fact, I would strongly suggest that you don't start learning Excel by trying to memorize functions and formulas. You shouldn't be going through those PDF slides and trying to memorize absolutely every function in there, but rather you should study it through practice and practical examples. That'd be looking up functions in details as you go. This is why we mostly have tutorial to follow this week rather than me just going through the slides and reading the syntax of functions to you cuz I don't think that's a great way to learn it. I think you should learn by doing when it comes to the practical parts of this course. So where can you go to find this documentation and how do you use it? Well, there are three main sources. The documentation built into Excel, the official Office support site, and I have some supplementary resources posted on Owl that'll sort of guide you to some other web- based resources. So what you can see on the screen right now is just a simple Excel workbook. But one thing to note is while we're working in Excel and typing in a formula, let's say we want some numbers here. I'll type 1 2 3 4 and we want to take the sum of those numbers. As we start typing in a formula either into the address bar up here or into a cell directly down here, we can see that it starts typing suggestions for us. So what these suggestions are are a list of functions built into Excel that are available for us to use. So in this case, we just want to take the sum of the numbers. So we could scroll down and see that one of the functions available is sum. And when we click on that, it tells us a short description. Says, adds all the numbers in a range of cells. But as we start typing more, it's going to show us the syntax right here. So, at first, this syntax can be a little confusing. First, it starts with sum, and it says number one, and then there's some numbers in square brackets and some dot dot dot dot dot. So, what these are in here are parameters that this function is going to take. Those are values that you have to provide it. If they're written without any brackets around them, that means it's a mandatory parameter and you have to provide it. So this could be a value that you type in like 55 or it could be a cell reference like B1 or it could be a range like that B1 to B4. And don't worry if you don't understand these ranges and things yet, but we're going to be getting more into those in the tutorial. The important part here is understanding the syntax. So the next thing we can see is that there's number two in square brackets. So what the square brackets mean is that this is an optional parameter. So it means we could just close this function now like so and hit enter and it would give us the total 1 + 2 + 3 + 4 is 10. Or we could start adding more parameters. So in this case we could add another 10. So this would be saying take the sum of this range and the number 10 and then we get 20. The other thing to note is while we're going along you can see that there's this dot dot dot at the end. That means that this list keeps going and going. So we could keep on adding more parameters that we want to add to the sum. So if we want to know more about this function we could click on sum here and hit yes. Yes. And what will come up on the side is a description of the sum function. And sometimes we even get videos, little mini video tutorials telling us how to use it. I'm going to try hitting play here. Use the sum function to add values. You can add individual. And there would be like a little tutorial just on how to use this function. So this built-in documentation is quite powerful. It also gives examples. And if we could scroll down, we get syntax. Again, it's using the same syntax I described to tell you how to use the function. It describes each of the parameters. Says like the first number you want to add would be number one and it says what that can be and it even has best practices. So, this is an extremely great resource. There's another way to access this when you're in Excel and that would be clicking on this help little tab up here. This will bring up the help ribbon. You can click on the help button right here on the help ribbon and that will bring up that little help menu again. And we can use the search bar to search for any function that we want to use. So instead of sum, let's take a look at count. So we type it in as suggestions, but we could just type it in and hit enter. And it'll say using the count function. So that's a quick description, but we can also go down here for more details. So we click on count function. And if you get this warning, just hit yes. And it gives you the description of the count function. It gives you a little video tutorial that you can follow along and the syntax. So in this case it's saying count takes at least one value and but it can take any number of more optional values. We know that because the bracket means it's optional and the dot dot dot at the end means it can take an unlimited number. It tells us the values required the first item the cell reference or the range that you want to count the numbers in. Then I have some remarks examples all kinds of stuff. So this is super powerful when you're going to be working on that assignment.
Chunk 3:
But if we remember there was a few other ways to get into the documentation. Let's take a look. So another way to get to that same documentation and in this case without necessarily having to have Excel installed or well on your phone even would be to use the site supportoff.com. So here I have a browser. I have support.off.com already typed in and I hit enter. It's going to bring up this site. So this is support for all office products. You could hit Excel to get in more Excel specific stuff, but we can just start by typing the function name right into this little search box. So in this case, let's say we want to learn about the function called min. So one of the first results here is going to be min a function. And you know we're on the right track cuz it says it applies to Microsoft Excel. And when we click on that, we're going to get the same sort of documentation. It's going to have a description of the function, returns smallest value in the list of arguments. It has the syntax. It says min a takes a value and then any number of optional values. And it has remarks about what this does, how it works, and even has examples of how to use it. So, it's the same documentation you'd find in Excel, but this allows you to access it on your phone, for example, or if you don't have Excel installed. And now we've come to the participation activity for the week. There's only one main participation activity for week two and this is it. That's why it's worth 150 points. So for this activity, first read the Excel slides and follow along with the Excel tutorial video. Once you've done that, pick an Excel function, then look up documentation for the function either via the Microsoft Office support site or through Excel directly and make a form post in the week 2 Excel functions topic on Owl. This post should contain three things. First, this should contain a description of the function in your own words. Can't just copy and paste this from the documentation. You actually have to write it in your own words. Explain what this function does, what it's useful for. Two, you should have the syntax of the function. This can be copied from the documentation using the same style that they use to explain most functions. And three, you should provide an example as real world as possible of how to use this function. So you can post screenshots or attach axslx Excel workbook file to your form post to make it more clear. If you don't have a way of taking screenshots or you don't know how, even taking a picture of your screen or just trying to draw a table the best you can is completely fine. We just want to demonstrate how this function works for everyone else. The idea here is after everyone posts this, we'll have sort of our own library of Excel functions. So, there is a full list of Excel functions you can access to make this a bit easier. You can use this link here that's in the slides. I also have it down below in the description of this video. And you can pick any function that's not heavily covered already in the slides tutorial videos. This means you can pick anything except what I have listed here. To make this as clear as possible, you can pick any Excel function you want except the ones listed over here. So do not pick anyone over here if you want full points for this participation activity. So for example, sum is listed over here. That means you cannot pick some because we already covered that well in the videos. So look in that list of Excel functions, find one that's not listed here, and then you can write a form post about it. So you can post about variations of these functions. For example, you could use sum a, some if, some ifs. That's all completely fine. But if you do, also explain how they're different than the regular versions. Another example would be things like count a, count if, count ifs. Those are all allowed because they're not listed over here, even though count is. So let's give an example of what this form post would look like. Here I'm using sum, but once again, you can't use sum because it was in that restricted list. So first, we'd have a description in our own words. The sum function takes a list of cell references or ranges and so on. And then we have the syntax of it there at the bottom. So it's showing in the same syntax used in the documentation how you would use this sum function. Then we would include an example. So for here we want to find the total of balances paid and outstanding as well as the overall total of all types of balances. I've included a screenshot here as well as how we use the sum function to solve this example. This example should be your own creation. and it can't be copied from another place. So that's all I have for you in this video. Remember to get started by reading acknowledge extension 4 and then starting in on those Excel slides. After that, watch the Excel video tutorial and then complete that participation activity we just talked about. Thank you for watching and have a great day. [Music]
