Abstract Transcript What’s a formula? In Excel, a formula is simply an expression that begins with an equal sign. A formula can be as simple as 1 + 1 or 2 + 2. However, what gives Excel so much power is that a formula can reference cells. Let’s take a look. All formulas in Excel must begin with an equal sign. The equal sign tells Excel that this is a formula that needs to be evaluated....
Abstract Transcript What’s a function? A function is a special jot down of formula. Excel has hundreds of functions. To introduce the idea of a function, we’ll look at theSUM function. The SUM function is one of the most useful and widely used functions in Excel. Its purpose is to add things up. Let’s take a look. Here we have a worksheet with several labeled cell references. We have the individual cells B7 and D6, and the ranges F7:F9 and H8:H11....
Abstract Transcript What is a number format? Number formats are used to control the display of cell values that contain numeric data. Let’s take a look. Here you’ll find formats for currency, numbers, date, time, and more. To apply a number format, just select one or more cells and choose a format. Number formats only affect numbers; they have no effect on text. Let’s format the rest of the values in our table....
Abstract Transcript In this video, we’ll introduce Pivot Charts. Pivot charts let you rapidly analyze large amounts of unsummarized data in different ways. Unlike normal charts, Pivot charts can be used to plot data with hundreds or thousands of rows. To introduce the idea of a pivot chart, I’ll first create a normal chart. Let’s say I want to summarize sales by state. you’re free to see the formula sums sales, using values from column B as criteria....
Abstract Transcript What is a pivot table? A pivot table is a special Excel tool that allows you to summarize and explore data interactively. In its current form, this data is hard to understand, because there’s too much detail. Before we look at the pivot table, let’s quickly check the total of all sales. Notice the total matches the number we just checked manually. Building a pivot table is the process of answering questions you have about the data....
So, what is an array formula? An array formula is a formula that works with an array of values, rather than a single value. What distinguishes an array formula is some kind of array operation that touches or manipulates the values. Let’s look at an example. Here we have some historical stock price data for Microsoft. If we give the Close prices to theMAX function, we get the highest close price....
What does it really mean? An array is a structure or container that holds a collection of items. The reason arrays come up so often in Excel formulas is that arrays can be mapped directly toranges. Arrays can be vertical, horizontal, or two-dimensional. Vertical arrays have items separated with semicolons. Horizontal arrays use commas. Two-dimensional arrays use both commas and semicolons. If I change a value manually, the corresponding cell updates....
Abstract Transcript In this video, we’ll introduce the idea of an Excel Table. So, what is an Excel Table? An Excel table is a rectangular range of data that has been defined and named in a particular way. To illustrate, here I have two rectangular ranges of data. Both ranges contain exactly the same data but neither one has been defined as a table. Next, I’ll convert the range on the right to a proper Table....
Abstract Transcript What is conditional formatting? you might think of conditional formatting as automatic formatting that is triggered by conditions that you define. Conditional formatting is a great way to visually highlight important information in a worksheet. Let’s take a look. A common use case of conditional formatting is to highlight values in a set of data. Here we have a table that shows monthly sales figures for a group of sales people....
Abstract Transcript What is Excel What is Excel? Excel is a spreadsheet program with a full spectrum of capabilities. Let’s take a look. Every Excel workbook is a collection of worksheets. All the work you do in Excel will be done in a worksheet. When you save a workbook, all the worksheets it contains are saved to a single file. The primary file jot down of Excel is the workbook....
To illustrate how Excel classifies data, I’ll enter some data in column B. As long as you haven’t overridden Excel’s default alignment, you’ll see numbers are right-aligned automatically. Text will be left-aligned by default. Dates and times are right-aligned like numbers. In fact, Excel handles dates and times as numbers. We’ll look at this in detail in another video. So, 2 + 2 returns 4, which is a number. In fact, if we concatenate any number with another number, we’ll get text....
In this video we’ll look how to avoid and handle that situation. The first thing to check is your lookup table. double-check the table is properly defined and the lookup value is in the left-most column. That’s because there is no employee with id E999. Sometimes, though, the problem is more subtle. For example, the code E785 is valid, and VLOOKUP correctly retrieves the information for Harold. So, take care that your data and lookup values are clean and don’t contain extra characters....
Abstract Transcript What’s a relative reference? A relative cell reference points to an address that is relative to the location of the reference. When a formula is copied to a new location, relative references are automatically updated. Relative cell references are standard in Excel. Let’s take a look. In this worksheet we have two highlighted cell references. Let’s create a formula that adds them together. We can enter =B9 + D6...
