Posts

Structured references behave differently from other references when copied. Let’s look at some examples. This is true even if I copy and paste outside the table. Excel adds the Table name to the formula, but the column name remains the same. If you better lock a column reference in a table, how do you do it? One trick is to use the multiple-column syntax with a single column. The simplest way to do this is to select two columns when creating theformula,then edit as needed....

To constrain the chart to the cell grid as you drag, hold down the Alt key. You’ll then see the chart snap to the gridlines as you go. you’ve got the option to add the Alt key to constrain movement to the cell grid. When you release the mouse you’ll have a copy of the chart. Notice both of these charts are plotting the same data. The Alt key will still constrain movement to the gridlines, even though you might’t see them....

How can you make one chart look like another? You have several options. One option is to copy formatting, and use Paste Special. First, snag the chart on the left and copy. Excel will apply formatting from the original chart. Let’s take a moment to study to review the formatting applied. First, the font color and size is applied to the entire chart. Colors were also copied. Both charts now use the same monochromatic blue palette....

Let’s take a look. TheLEN functiontakes just one argument: the text you want to count. The same formula will return the number eight for the text “New York.” That’s because LEN also counts space characters. The abbreviation NY returns two, as you’d expect. Be aware that text might contain an extra trailing space which can throw off the count. We’ll look at a way to solve this problem with a function calledTRIMin another video....

Here we have a list of properties. Let’s add our own message at the top of the list that stays visible. The first thing to do is to convert our list into anExcel table. This will make it easier to count the rows in the list. Note that Excel automatically names all tables. We’ll rename this table “Properties” to make the name more meaningful. To count total rows, we can use the functionROWS, and simply input =ROWS(Properties)....

On this first worksheet, this list of colors contains duplicates. We want to get a count of the unique colors. Now, I can easily count these values with theCOUNTA function. Next, I’ll enter the UNIQUE function in D5. Now, to get a unique count, I just need to feed these results to the COUNTA function. And, when I enter the formula, COUNTA returns 5. On the next worksheet, we have time logged by several people for two projects, Alpha and Omega....

In this case, you could use a special trick called a “3D reference”. Here are the test scores we looked at earlier. The Summary sheet is pulling in the results from Week1 through Week5. Suppose we want to average those test scores? 3D referencing works like a cell rangeyou use a colon between the starting and the ending sheets. Once I enter the formula and copy it down, we get exactly the same averages we calculated earlier....

The charting system in Excel is flexible and deep. Let’s take a look. Here we have annual sales figures for a small company. We’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data. First, go for the data you’d like to chart. If your data includes totals, don’t include these in your selection. All Excel charts have several variations....

Here we have annual sales figures for a small company. I’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data. To create a chart, start by selecting the data. Notice that when you select a chart, Excel highlights the cells that contain the source data. You’ll see this highlighting only when the chart is selected. For example, I could limit this chart to display only the last 5 years of data....

Let me show you an example. Here we have a list of names. We want to pull out the first name from the full name. There’s an Excel function calledLEFTthat will extract text from the left, so let’s start there. Rather than explain how LEFT works, let’s just try it out to see what it does. Once I initiate the parentheses, Excel will tell us what we need to provide....

There are many different kinds ofconditional formatting, but the basic steps are the same for all types. Let’s take a look. Here we have a table that shows five test scores for a group of students. Let’s create a conditional formatting rule to highlight all test scores over 90 in green. To create a new conditional formatting rule, first go for the cells you’d like to format. Then, select an option from the Conditional Formatting menu....

Let’s take a look. Let’s look first at the Date code reference table. This table shows the date codes available for custom date formats. Now let’s look at the table we need to format. In the left column, we have a list of dates. The other columns show the custom formats we need to create. Let’s start by copying the dates across the entire table. For column C, the existing format already has all of the components we need....

Let’s take a look. In this case, the currency format is a good start. Now, when we click OK, we see we’re getting close. To actually create our custom format, we need to revisit the Format Cells dialog. This time, we start by selecting Custom. This will immediately create a new custom format, based on currency, that we can further customize. The semicolon separates the format for positive numbers on the left from negative numbers on the right....