Posts

TheDATEDIF functionis a “compatibility” function that comes from Lotus 1-2-3. DATEDIF takes three arguments: start date, end date, and unit. To explain how DATEDIF works, the table below is set up to show all options available for Unit. First, however, let’s calculate both years and months in a different way. This will give you some data to compare with the results of DATEDIF. To calculate years between dates, we can use a function called YEARFRAC....

In this worksheet, we have two columns that contain names. In column D, I’ll add a formula that capitalizes the first name using theUPPER function. The UPPER function takes just one argument: the text you want in upper case. When I add the formula and copy it down, we’ll get all first names in uppercase text. What if you need both first and last names in upper case? This formula can be as flexible as you need....

After you create a chart, you don’t have to worry about updating the chart when values change. Just click Calculation Options and confirm that Automatic is checked. In this chart, the source values are currently in the range C5:C9. If I edit any value in this range, the chart automatically updates. That’s because the reference to the source data is static, and doesn’t automatically expand. The chart will immediately update....

You’ve probably noticed that columns in Excel do not expand to accommodate the data they contain. you might fix this problem by setting the column width. Let’s take a look. All columns in Excel have a set width. To check the current width of a column, just hit the column divider to the right. You’ll see the width displayed in characters. It’s also possible to set the column width numerically....

The default row height in Excel is determined by the font size. As you increase or decrease the font size, Excel will adjust the row height to fit. However, you’ve got the option to still adjust the row height manually. Let’s take a look. All rows in Excel have a set height. To check on the current height of a row, just hit the row divider at the bottom. You’ll see the height displayed in points....

Just grab the chart and click “change chart throw in” on the Design tab of the ribbon. Excel will launch the change chart throw in window, where you might browse and select other chart types. If you Hover over the image with your mouse, you’ll see a larger preview. The chart will immediately update. The All charts tab will show you every chart bang out available. This brings you to the same place....

Abstract Transcript In this lesson we’ll look at how to change the font color. Let’s take a look. Let’s continue with our menu project and add some font colors. Let’s make the headings one color and the menu items another. As always, start by selecting the cells to format. Click once to fire up the menu, then release the mouse button to browse colors. This menu shows all of the available colors in the currently selected color scheme....

Numeric fields are summed, and text fields are counted. However, you’re free to change the math used for numeric fields to several other functions. Let’s take a look. Lets start off by adding Product as a row label. Now lets add Total Sales as a Value, and set the format toAccounting. However, we can easily change from Sum to one of several other math functions. There, you’ll see an item called Summarize Values By....

Lets take a look. These tabs give you a full set of controls to customize your pivot chart. Basic chart options are located on the Design Tab. To use a different kind of chart, nudge the Change Chart pop in button. For example, we can easily switch to a 3D column chart. Or to a pie chart. you’ve got the option to also right-click to access Chart Types. Lets change back to a column chart....

In this worksheet we have a list of six generations. Each generation has a start year and end year, which represent birth years. How can we plot these generations in an easy-to-read bar chart? This seems like a simple problem, but a solution takes a little creative thinking. To help illustrate the challenge, I’ll plot this data in the most obvious way. I want a horizontal bar chart, so I’ll use the icon next to recommended charts....

This is the final chart. This project has a couple of interesting challenges. First let’s look at the available data. you’re able to see we have data for both sunrise and sunset. Both columns contain valid Excel times. The clustered column option isn’t really useful. Stacked columns are closer to what we want. There are several ways to approach this. First, we need a column for total daylight hours. This is pretty simple, I can just subtract sunrise from sunset....

Abstract Transcript One thing you’ll frequently do in Excel is check or debug formulas. Here we have a simple list of names. you’ve got the option to do this quickly by using Go To Special > Formulas. you might see that both Age and Status are calculated values. Now let’s use the F9 key to see what the STATUS formula is doing. In general, it’s easier to debug a formula in the formula bar....

Have you ever had to check to double-check certain lines of text aren’t too long? One approach I like is to use conditional formatting with simple functions. Let’s take a look. Here’s a list of over 300 Latin titles I generated on the Lorem ipsum website. Let’s say we need to check each title to check that it’s not more than 70 characters. To start off, let’s use the Excel function called LEN to calculate the length of each title....