What is Scroll Lock? Usually, the arrow keys will move you one cell at a time in whatever direction you wish. However, when Scroll Lock is enabled, the worksheet is “scrolled” instead. The active cell never changes. If you have a Scroll Lock key on your keyboard, just press it to toggle Scroll Lock off. No key for Scroll Lock? Unfortunately, it’s harder to disable Scroll Lock if your keyboard doesn’t have a Scroll Lock key....
Excel has a powerful system for tracking changes so that they can be reversed when needed. It’s called undo and redo. Let’s take a look. If you tap the Undo button again, you’ll step back one more step. Each time you click Redo, Excel brings back another change. Both Undo and Redo have keyboard shortcuts that are worth remembering. Control Z for Undo and Control Y for Redo. Using these shortcuts, we can Undo and Redo the same changes again....
you could see we have a date, order number, item, total, and state. So, to start off, let’s just see what items are sold. I’ll add item on the axis, and then put total into the value area. Now, let me change a value in the source data. I’ll change the very first entry on January 1 from $32 to $3200. Notice, nothing that in the chart has changed. Once I refresh, the new data flows into the chart....
Let’s take a look. Here we have the table of random numbers. Let’s build a conditional format that uses a formula to highlight cells greater than a certain value. Start by selecting all values in the table. Then choose New Rule from the Conditional Formatting menu. Excel displays a window with a number of starting points. Now we need to specify the formula and the format. Let’s enter a formula that checks to see if a value is greater than 500....
Let’s take a look. With normal totals, you might just sum a range and be done. TheSUM functionadds together all the values in the range and reports the result. But what if you want to create a running total? But another option is to set up one formula that you might copy down the entire table. That way, you have fewer unique formulas and less chance of making a mistake....
This makes slicers very useful for building dashboards that are based on pivot tables. In this worksheet, we have a simple pivot table that shows total sales by Product. Next, I’ll add a slicer to filter by Region. Notice that, at this point, the slicer controls only one pivot table. All slicers have a setting that links them to pivot tables, called “Pivot Table Connections”. you’re able to access this setting using the button on the Slicer Tools tab of theribbon....
Let’s take a look. In cell D9, let’s enter the first formula we need: =C9*C6 This works fine. John worked five hours at $11 dollars per hour, so his gross pay is $55.00. Now let’s copy and paste this formula down to the rest of the table. We can see right away that we have some problems. However, the reference to the hourly rate was also changed, which we definitely don’t want....
Excel has an easy way of handling this situation. Let’s take a look. Here we have a small table of data that represents widget sales over a 3-month period. We have the quantity sold of each widget already in the table. We just need to add Sales, which equals the quantity sold x the price. The price appears in column C. For January, we just need a simple formula. The formula for Widget A is D9 * C9....
Abstract Transcript In this lesson we’ll take a look at the Accounting format. Like the Currency format, the Accounting number format is designed for numbers with currency symbols. Let’s take a look. Let’s start off by copying a set of numbers in General format across our table. Unlike Currency, there are no options for negative numbers. The Accounting format places parentheses around all negative numbers by default. Let’s set decimal places to zero....
To add things in Excel, use the “+” sign. Let’s take a look. The first two examples don’t require any cell references and can be input directly. We’ll simply throw in this in directly as = C9 + D6 Next we have H11 plus F7. This time, we’ll point and click to set the cell references. Notice we get a negative value, since the value in H11 is -10. Finally, we need to add together all cell references....
Let’s take a look. This is a pretty basic table with a simple gray border. Let’s upgrade this design to something with a little more polish. First, let’s remove all borders. For this, we can use the preset in the border menu on the ribbon. Now let’sincrease the row heightto 30 for all the rows in the table. This will make it easier to use more color without the table feeling too crowded....
One of the best features of tables is called “calculated columns”. Calculated columns help you enter and maintain formulas in Excel tables. A quick way to copy down the formula is to double-click thefill handle. Since every cell has a price, Excel copies the formula to the bottom. Next, I’ll add a formula to calculate a 7% tax. Again, I can double-poke the fill handle to copy it down. At this point, the first formula is out of sync with the rest....
It’s called Center Across Selection. Unlike merging, Center Across Selection leaves all cells in place but still centers text across columns. Let’s take a look. Recall that Merge & Center physically merges cells and centers the remaining text. Only the value in the upper left cell is maintained. Other text, if it exists, is destroyed during the merge. In contrast, Center Across Selection only centers text; it does not combine cells....
