Let’s take a look. Here we have a table that contains of order data. We could add a filter, and use it to explore the data. But filters can be a little clunky. Let’s take a different approach and add a “search box” above the data. We’ll use conditional formatting to highlight rows that contain text typed in the search box. First, label the search box, and add a fill color....
First, let’s look at the problem we’re trying to solve. Here we have monthly sales data. At the moment, we only have 5 months, but we’ll be adding more data over time. Now, if I insert a column chart, everything works fine. But notice if I add new data, the chart doesn’t change. It still plots the original data. Well, one way to do it is with anExcel Table. I’ll delete the chart and add the table....
Here’s the chocolate sales data we looked at earlier. Notice, I’ve already added a tab for the dashboard with some placeholders. This makes it easier to visualize what we’re trying to build. So, the next step is to create the charts we need. First, I’ll create a new pivot table and chart on a new worksheet. This chart is for sales by city, so I’ll name the worksheet to match....
Here we have a sample set of data that shows t-shirt sales. you’re able to see we have columns for date, item, color, and amount. So let’s break this data down by color. First, I’m going to name the Color and Amount columns in the data. Now, in our summary table, we need a list of unique colors. If you just have a few items in a list, there’s no need to use Remove Duplicates....
A stacked column chart can show part-to-whole comparisons over time, or across categories. The result is a column chart, where each column is built from quarterly sales in each region. In this configuration, the chart is a good visual representation of the data in the table. The breakdown of sales by quarter is harder to interpret. In the West, we can see Q4 is the strongest quarter and Q1 is the weakest....
So, the first step is going to be to calculate the number of characters in the cell. We’ll do that with the Length orLEN function. And then, we need to figure out the number of characters without spaces. To get the number of characters without spaces we need to strip the spaces out. I’ll useSUBSTITUTE, and we’ll point at this text here. We’ll look for spaces and replace that with “nothing....
Like line charts, area charts are a good way to show trends over time. Here we have some US census population data for several states. Let’s plot this data in an area chart. To start off, I’ll just select data for Arizona, and create a new area chart. Now, if I try Recommended Charts, we’ll see an area option that looks good. But notice it’s actually a stacked area chart, not a basic area chart....
In this video, we look at how to use functions like AND and OR inside the IF function. you could download the worksheet I used in the training below. It’s a great way to develop strong formula skillswithouta huge time investment. you’ve got the option to getCore Formula aloneorin a course bundle(save 15%). Need some good reasons to learn formulas?
Abstract Transcript In this video we’ll look at how to calculate an average value. Let’s take a look. In this worksheet we have a list of 16 properties, each with a price and other information. Let’s calculate an average price. First, I’llcreate a named rangefor the prices. This makes the formulas easier to read and copy. Excel has a function calledAVERAGE, but let’s do the calculation manually to start off with....
Second, she wants to see how many days remain before expiration. Also, can she have it before her lunch meeting at noon? First, let’s convert this data to a properExcel Table. Now let’s calculate the expiration dates. This is a good start. We can finish roughing out the solution and come back to fix this later. Now that we have an expiration date, we can calculate “Days left.” The formula we need is simply E5 minus TODAY()....
TheWORKDAY functionreturns a date in the future or past that takes into account weekends and, optionally, holidays. Now, to review, date calculations can be really simple. If I change C5 to “-1” we get December 21st. This works because Excel stores all dates as serial numbers. But what happens when it’s crucial that you skip weekends or holidays? Well, that’s what the WORKDAY function is for. WORKDAY skips the weekend....
Let’s take a look. Let’s calculate the maximum and minimum values in this list. First, I’m going tocreate a named rangefor the prices in the list. You don’t need to do this, but it makes the formulas easier to read and copy. To get a maximum value, use theMAX function. MAX takes one or more arguments, each representing a number or range of numbers. In this case, we just need to supply the named range “prices....
In C5, I’ll add a start date. The result is that we get a list of 14 consecutive dates. Now let’s show the day of the week for each date. There are several ways we can do this. This time, I’ll set the format to only show the “day of week.” In this case, we end up with just text. Because dates are serial numbers in Excel, we can simply subtract the start date from the end date....
