The AutoSum tool is in the Excel 2016 Editing group on the Home tab of the Ribbon. Look for the Greek sigma symbol. This little tool is worth its weight in gold when it comes to entering functions.
In addition to entering the SUM, AVERAGE, COUNT, MAX, or MIN functions, it also selects the most likely range of cells in the current column or row that you want to use as the function's argument and then automatically enters them as the function's argument.
Nine times out of ten, Excel selects (with the marquee or moving dotted line) the correct cell range to total, average, count, and so forth. For that tenth case, you can manually correct the range by simply dragging the cell pointer through the block of cells to sum.
Simply select the AutoSum button followed by Sum on the drop-down menu on the Home tab when you want to insert the SUM function into the current cell. The quicker method to select this function is to press Alt+= (the Alt key plus the equal to symbol on the top row).
If you want to use the AutoSum button to insert another function, such as AVERAGE, COUNT, MAX, or MIN, you need to click its drop-down button and select the name of the desired function on its pop-up menu (click Count Numbers on the menu to insert the COUNT function). If you select the More Functions command on this menu, Excel opens the Insert Function dialog box as though you had clicked the fx button on the Formula bar.
In the following figure, check out how to use AutoSum to total the sales of Jack Sprat Diet Centers in row 3. Position the cell pointer in cell E3 where the first-quarter total is to appear and then select Sum on the AutoSum drop-down menu (or press Alt+=). Excel inserts SUM (equal sign and all) onto the Formula bar; places a marquee around cells B3, C3, and D3; and uses the cell range B3:D3 as the argument of the SUM function.


Now look at the worksheet after you insert the function in cell E3 (see the following figure). The calculated total appears in cell E3 while the following SUM function formula appears in the Formula bar:

The worksheet with the first-quarter totals calculated with AutoSum.
=SUM(B3:D3)
After entering the function to total the sales of Jack Sprat Diet Centers, you can copy this formula to total sales for the rest of the companies by dragging the fill handle down column E until the cell range E3:E11 is highlighted (as shown).
Look at the following figure to see how you can use AutoSum to total the January sales for all the Mother Goose Enterprises in column B. Position the cell pointer in cell B12 where you want the total to appear. Select Sum on the AutoSum's drop-down menu, and Excel places the marquee around cells B3 through B11 and correctly enters the cell range B3:B11 as the argument of the SUM function.
For those of you who don't have the time or patience for adding totals to your Excel 2016 worksheet tables with AutoSum and AutoFill, Excel 2016's Totals feature on the Quick Analysis tool is just the thing. The Quick Analysis tool offers a bevy of features for doing anything from adding conditional formatting, charts, pivot tables, and sparklines to your worksheet tables.
And it turns out Quick Analysis is also a whiz at adding running totals and sums to the rows and columns of your new worksheet tables.
To use the Quick Analysis tool, all you have to do is select the worksheet table's cells and then click the Quick Analysis tool that automatically appears in the lower-right corner of the last selected cell. When you do, a palette of options (from Formatting to Sparklines) appears right beneath the tool.
To add totals to your selected table data, simply click the Totals button. You can then use your mouse or Touch Pointer to have Live Preview show you totals in a new row at the bottom by highlighting Running Total or in a new column on the right by highlighting Sum (shown here). To actually add the SUM formulas with the totals to a new row or column, you simply click the Running Total or Sum button.
Using Totals on the Quick Analysis tool to add a row of running monthly totals or a column of quarterly totals to a selected worksheet table.
 To add the running totals to the sample worksheet table shown in Figure 2-20, you simply select the table of data, A2 through D11, and click the Quick Analysis tool followed by the Totals and Running Total buttons. Add a column of quarterly running totals down the rows in the cell range E3:E11 by selecting the Quick Analysis tool again and then selecting Totals followed by the Sum option (displaying the Sigma on a shaded column) that is to the immediate right of the Running option. Finally, enter a Qtr1 Total heading at the top of the column in cell E2, and you're done!
The Format as Table feature in Excel 2016 is so automatic that the cell cursor just has to be within the table of data prior to you clicking the Format as Table command button in the Styles group on the Home tab.
Clicking the Format as Table command button opens its rather extensive Table Styles gallery with the formatting thumbnails divided into three sections — Light, Medium, and Dark — each of which describes the intensity of the colors used by its various formats.
As soon as you click one of the table formatting thumbnails in this Table Styles gallery, Excel makes its best guess as to the cell range of the data table to apply it to (indicated by the marquee around its perimeter) and the Format As Table dialog box, similar to the one shown here, appears.
Selecting a format from the Table Styles gallery and indicating its range in the Format As Table dialog box.    

This dialog box contains a Where Is the Data for Your Table text box that shows the address of the cell range currently selected by the marquee and a My Table Has Headers check box.
If Excel does not correctly guess the range of the data table you want to format, drag through the cell range to adjust the marquee and the range address in the Where Is the Data for Your Table text box. If your data table doesn't use column headers or, if the table has them, but you still don't want Excel to add Filter drop-down buttons to each column heading, deselect the My Table Has Headers check box before you click the OK button.
Excel 2016 lets you create your own custom styles to add to the Tables Styles gallery and use in formatting your worksheet tables. Once created, a custom Table Style not only applies just the kind of formatting you want for your worksheet tables but can also be reused on tables of data in any worksheet you create or edit in the future.
You can even designate one of the custom styles you create as the new Table Style default for your workbook so that it's automatically applied when you later format a data table in its worksheets with the Tables option on the Quick Analysis toolbar.
To create a custom Table Style, you follow these steps:
  1. Format the data in your worksheet as a table using one of the existing styles.
  2. On the Design contextual tab of the Tables Tool tab, click the Table Styles More drop-down button and then select the New Table Style option near the bottom of the gallery.
    The New Table Style dialog box shown in here appears.
    Use the options in the New Table Style dialog box to create a new custom table style to add to the Table Styles gallery.

    1. Replace the generic, table style name, Table Style 1, with a more descriptive name in the Name text box.
    2. Modify each of the individual table components in the Table Elements list box (from Whole Table through Last Total Cell) with the custom formatting you want included in your new custom table style.
      To customize the formatting for a table element, select its name in the Table Element list box. After you select the element, click the Format button to open the Format Cells dialog box where you can change that element's font style and/or color on its Font tab, the border style and/or color on its Border tab, or the fill effect and/or color on its Fill tab.
      Note that when customizing a First or Second Column or Row Stripe element (that controls the shading or banding of table's column or row), in addition to changing the fill for the banding on the Fill tab of the Format Cells dialog box, you can also increase how many columns or rows are banded by increasing the number in the Stripe Size drop-down list that appears when you select one of the Stripe elements.
      As you assign new formatting to a particular table element, Excel displays a description of the formatting change below the Element Formatting heading of New Table Style dialog box, as long as that table element remains selected in the Table Element list box. When you add a new fill color to a particular element, this color appears in the Preview area of this dialog box regardless of which component is selected in the Table Element list box.
    3. (Optional) If you want your new custom table style to become the default table style for all the data tables you format in your workbook, select the Set as Default Table Style for This Document check box.
    4. Click the OK button to save the settings for your new custom table style and close the New Table Style dialog box.
Excel 2016's Flash Fill feature gives you the ability to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes. The series of entries appears in the new column, literally in a flash (thus, its name, Flash Fill).
The second Excel 2016 detects a pattern in your initial data entries, the rest of the entries in that series immediately appear in blank cells in rows below that you can then enter with a single keystroke. And the beauty is that all this happens without the need for you to construct or copy any kind of formula.
The best way to understand Flash Fill is to see it in action. In the following figure, you see a new data table consisting of four columns. The cells in the first column of this table contain the full names of clients (first, middle, and last), all together in one entry. The second, third, and fourth columns need to have just the first, middle, and surnames, respectively, entered into them (so that particular parts of the clients' names can be used in the greetings of form e-mails and letters as in, "Hello Keith," or "Dear Mr. Harper,").
Data Table containing full names that need to be split up in separate columns using Flash Fill.
Rather than manually enter the first, middle, or last names in the respective columns (or attempt to copy the entire client name from column A and then edit out the parts not needed in First Name, Middle Name, and Last Name columns), you can use Flash Fill to quickly and effectively do the job. And here's how you do it:
  1. Type Keith in cell B2 and complete the entry with the down arrow or Enter key.
    When you complete this entry with the down-arrow key or Enter key on your keyboard, Excel moves the cell pointer to cell B3 where you only have to type the first letter of the next name for Flash Fill to get the picture.
  2. In Cell B3, only type J, the first letter of the second client's first name.
    Flash Fill immediately does an AutoFill-type maneuver by suggesting the rest of the second client's first name, Jonas, as the text to enter in this cell. At the same time, Flash Fill suggests entering all the remaining first names from the full names in column A in column B.
  3. Complete the entry of Jonas in cell B3 by pressing the Enter key or an arrow key.
    The moment you complete the data entry in cell B3, the First Name column's done: Excel enters all the other first names in column B at the same time

Total Pageviews

2016 © BiztechCS
Planer theme by BiztechCS