All charts in Excel have a chart area, which encloses all elements of the chart. The easiest way to grab the chart area is to click just below the top edge of the chart. Once the chart area is selected, you’re free to make a variety of formatting changes. To change the line around a chart, use the controls in the Border area. You’ll find controls for color, thickness, and many other line attributes, including rounded corners....
Let’s take a look. Here we have a simple coffee menu. Let’s improve the formatting by applying some custom fonts. To apply a font to one or more cells, first grab the cells you’d like to format. In this case, let’s start by applying a new font to the entire worksheet. The font menu on the ribbon displays each font in its own typeface. To apply a font, just click a font in the menu....
Let’s take a look. Sometimes you better apply formatting to only a few characters or words. There are two ways to apply this kind of formatting. The first way is to select a cell and apply formatting in the formula bar. Start by selecting the text you want to format and go for the format you like. you’ve got the option to also apply bold, italic, and underline styles. After you snag the text in a cell, Excel will faintly display a small menu of formatting options....
Let’s take a look. There are two ways you’ve got the option to format values in a pivot table. The first way is to select cells directly in the pivot table and apply a format manually. Let’s undo this format, and look for a better way to apply number formats. A much better way to set number formatting is to apply it to the field itself. you’re able to do this in three ways....
In this lesson we’ll look at how to do that. Let’s take a look. Here we have a large data table with a header row at the top. Notice that as we scroll down through the data, the headers scroll off the screen. And, if we scroll to the right, the company name is no longer visible. These options are visible on the view tab in the Freeze Panes menu....
In this worksheet, let’s generate 20 random dates between May 1 and May 30, 2020. In cell C6, I’ll enter 20 since we want 20 dates total. In cell E5, I’ll enter the RANDARRAY function. Forcolumns, we want 1. Formin, we want the start date in C4. Formax,we want the end date in C5. When I enter the formula, RANDARRAY generates 20 random dates in May. Remember that these values need to be formatted as dates....
How can we generate random values that aren’t numeric? In this worksheet, I have a list of 4 colors in the range B7:B10. In column E, I’ll enter a formula to create a random list of these colors. To start off, I’ll enter just the INDEX function. Min is 1 and max is 4, since we have four colors in the list. Finally, the integers argument needs to be set to 1 or TRUE, because we want whole numbers....
In this worksheet, let’s generate 20 random times between 7:00 AM and 6:00 PM. To do this, we’ll use theRANDARRAY function. Now, RANDARRAY can generate both integers and decimal values. For Times, we want decimal values, because Excel times are fractional values of one day. In cell C6, I’ll enter 20 since we want a total of 20 times. Now I’ll enter the RANDARRAY function in cell E5. Therows argument corresponds to the number of times we want, so this value comes from cell C6....
The RANDBETWEEN function is a simple function you might use to generate random numbers. For example, I can enter RANDBETWEEN with a bottom value of 1 and a top value of 100. When I press Enter, I get a random value between those two values. To get more natural prices I can modify the formula to subtract 5 cents. I’m using the shortcut Control + Enter here to enter all formulas at once....
This would be, for example, the 1st, 2nd, and 3rd smallest or largest values. In this first sheet, we have a list of students with five test scores. I’ll use theLARGE functionto extract the three best scores for each student. To get top values, use theLARGE function. Then I can copy the formula across and come back and change “k” as needed. When I copy all three formulas down, we get the three best scores for each student....
In this video, I’ll show you how to group data by age range. Here we have a set of data that represents voting results. I’ll go ahead and summarize the data in a pivot table. Or, I can make Vote a column label and then add Gender as a row label. For both fields, the pivot table breaks down the data using the values that appear in each field. But what if you want to group by age?...
Lets take a look. This pivot table shows total sales by Product. Lets add the Date field as a column label, and then group to show sales by year. To group a date field, first select one of the dates. Then right-click and choose Group from the menu. Excel will display a grouping dialog box, with a list of date and time units. In this case, we just need to select years....
Abstract Transcript Pivot tables are very good at grouping dated information. Let’s take a look. Let’s quickly build a pivot table that summarizes new subscriptions by month. For convenience, I’ll first convert the data into a table. This will speed things up later, when we start working with the source data. Now I’ll summarize the table with a pivot table. I’ll also name the worksheets to keep things clear. To start off, let’s add the Email Address field as a value in the pivot table....
