Abstract Transcript Each character you see displayed in Excel has a number. Excel has two functions that work with these numbers directly:CODEandCHAR(Character). Let’s look first at theCODE function. The CODE function accepts one argument, which is the text for which you’d like a numeric code. If I use CODE with a capital A (“A”) it returns 65. With a lowercase “a” CODE returns 97. TheCHAR functionis basically the reverse of the CODE function....
Other values are assigned a weighted blend of color. This makes it easy to see general patterns in data, especially with a large data set. Let’s take a look. Here we have a large table that shows average monthly temperatures for Salt Lake City from 1949 through 2012. Let’s use a color scale to help visualize the numbers. There are twelve presets in the Color Scales category of the Conditional Formatting menu....
In addition, it shows operating income at different sales volumes. If we change an assumption, the model recalculates. A better way to expose values in the table is to add them directly to the labels using concatenation. We can do this using the ampersand symbol. First, add an equal sign to turn the label into a formula. Then, wrap the existing text in double quotes. Next, add an ampersand character and point to the cell with the correct value....
Abstract Transcript In this lesson we’ll take a look at the Currency format. The Currency format is designed to display a currency symbol along with numbers. It has options for the currency symbol itself, the number of decimal places, and negative number handling. Let’s take a look. Like Number format, the Currency format provides options for decimal places and handling negative numbers. However, it does not provide an option for a thousandths separatorthis is always enabled for Currency....
Abstract Transcript Data bars are one of the key types of conditional formatting in Excel. They’re great way to show the relative magnitude of values in a set of data. Let’s take a look. Here we have a simple table that tracks the progress of a group of people towards a fundraising goal. Column C shows that each person has a goal of $3,000. And Column D shows the current funds raised per person....
Abstract Transcript In this video, we’ll cover the basics of data labels. Data labels are used to display source data in a chart directly. Generally, the easiest way to show data labels to use the chart elements menu. When you check the box, you’ll see data labels appear in the chart. you could even select a single bar, and show just one data label. In a bar or column chart, data labels will first appear outside the bar end....
Abstract Transcript In this lesson we’ll take a look at the Date format. The Date format is flexible and can display the same date in many different ways. Let’s take a look. Here we have a set of dates in column B of our table. Let’s start off by copying these dates to all columns. Let’s look first at the Short Date and Long Date formats. This means that dates in these formats may look different on different computers....
So, 10 divided by 2 is entered as 10 / 2. Let’s take a look. The first formula doesn’t use any cell references. We just input = 10 / 2, which gives a result of five. We enter = D6 / 7 and get 3. For C9 divided by H11, we enter = C9 / H11 which gives us -2. When using division, you may see a VALUE error if any cells contain text....
Drag and drop is an elegant alternative to using copy and paste. It can save you a few steps, but it requires a little more dexterity. Let’s take a look. Then, just drag the selection to a new location. When you release the mouse, the contents of the cells are moved to the new location. To copy information with drag and drop, the approach is very similar. opt for cells you’d like to copy and hover over the edge of the selection....
Abstract Transcript Let’s look at how to use exponents in Excel. Exponents are entered using the caret symbol which is above the number 6 on the keyboard. For example, the formula for 3 squared is entered as = 3 ^ 2. Let’s take a look. The first formula doesn’t use any cell references. For 3 squared, we input = 3 ^ 2 which gives us 9. Excel doesn’t care about white space in formulas, so feel free to add space for better readability....
These are the options to fill, justify, and distribute content horizontally in cells. Let’s take a look. Let’s look first at the Fill option. Selecting a horizontal alignment of Fill will fill a cell with content, repeating the content as necessary. This is not very useful for ordinary content, but it can be used for other effects. Or, we could use a period instead. Justify only affects text in cells that are wrapped....
What do criteria do? Otherwise, return the original price. What are criteria? Criteria are logical expressions that return TRUE or FALSE, or their numerical equivalents, 1 or 0. In all other cases, criteria should return FALSE or zero. Logical operators Criteria often make use of the logical operators listed in the table below. Logical operators can be combined in various ways, as seen in the examples below. The table below lists the key logical functions....
Abstract Transcript In this lesson we’ll look at the Fraction format. The Fraction format is made to display fractions. It can handle fractions of up to three digits, and comes with several presets for common fraction units. Let’s take a look. Let’s first copy these numbers to the rest of our table. As we can see, Excel converts most of the values to fractions. However, some numbers aren’t converted, while others are just a close match....
