Tuesday, May 1, 2018

How to Merge and Unmerge Cells in Excel: Tips and Tricks to Know

When you want to combine two cells into one in Excel, you have a few options. Merging cells is one of the best ways to do it. But it has a few drawbacks, too. Even so, you might find that it’s useful for working with text in Excel.
We’ll take a look at how to merge cells in Excel, how to unmerge them, and another method for combining two cells into one without losing the data in the cells.

How to Merge Cells in Excel

Before we get into how to merge cells, let’s talk about exactly what happens when you merge in Excel. In this example, we’ll look at two columns, one for first name and one for last name:

how to merge cells in excel - Two Excel columns before merging

When we merge two of these cells, we end up with the contents of those cells in a single cell, like this:

how to merge cells in excel - A single merged cell in Excel

The two cells have been combined, leaving a single cell that’s twice as wide and only contains one value. Excel only keeps the upper-left value of any merged cells. That’s one drawback to merging in Excel. We’ll talk about how to get around that in a moment.
To merge cells in Excel, select the cells you want to merge. You can select any number of cells; in our example, we’ll select just two. Once you’ve selected the cells, head to the Home tab and click Merge & Center in the Alignment section:

how to merge cells in excel - Excel Merge & Center button

If you’ve selected more than one with text in it, you’ll get a warning:

how to merge cells in excel - Excel merge cells warning

If you want to continue, just hit OK. You’ll then have your merged cell as in the screenshot below. That’s all there is to it.

how to merge cells in excel - Excel merged cell

Merging Columns in Excel

In some cases, you’ll want to merge entire columns instead of just a few cells. Merging columns in Excel is easy; just select both columns and use the same steps.
First, highlight the two columns you want to merge:

Selecting columns to merge in Excel

If you hit the Merge & Center button right now, you’ll end up with one huge cell that has a single value in it. Instead, click the dropdown arrow next to Merge & Center and select Merge Across:

Excel Merge Across button

After clicking Merge Across, you’ll receive another warning from Excel. But instead of a single warning, you’ll get one warning for each row in your selection. Which is a lot. After clicking OK for each row, you’ll have a newly combined column:

Excel merged columns

You might be wondering why you wouldn’t just delete the second column in this case. Well, you probably would. Merging columns doesn’t do a whole lot for you.
Let’s talk about a better way to merge cells in Excel.

Combining Cells With CONCATENATE

The fact that combining cells makes you lose data is a big disadvantage of using Excel’s merging function. However, you can get around that by using a formula. Here’s the syntax of the CONCATENATE function, one of Excel’s most useful text functions:
=CONCATENATE(text 1, [text 2],...)
The function takes multiple text inputs and creates a new cell that combines all of them. This lets you merge cells without losing any data. Let’s use the same spreadsheet as before, with the list of first and last names. Instead of merging the cells and losing the last names, we’ll create a new column and use CONCATENATE to bring in both the first and last names.
Here’s the formula we’ll use in the new columns:
Notice that between B2 and C2 we have a space between two quotes. This is important because it inserts a space between the contents of the cells being merged. If you forget it, you’ll get the first and last names mashed together.
Here’s what it looks like in Excel:

Using CONCATENATE to merge cells in Excel

When I hit Enter, here’s what we get:

Merged cells using CONCATENATE

Much better than our first attempt, isn’t it?
You also have the advantage of being able to modify the combination later, whereas you cannot do that when you merge cells the regular way.

How to Unmerge Cells in Excel

If you decide to merge cells, you’ll also want to know how to unmerge them.
Unfortunately, unmerging doesn’t bring any of your lost data back. When you merge cells in Excel, you’re going to lose some information, and it’s gone forever. Merged cells can create weird spaces in your spreadsheet, though, and unmerging them solves the problem.
To unmerge cells, just select the merged cell, click the dropdown arrow next to Merge & Center, and click on Unmerge Cells:

how to unmerge cells in excel - Unmerge Cells button in Excel

The cells will go back to their original count and size. You’ll still only have the data from the upper-left corner of your selection, though. This is one reason why it’s a good idea to work on a copy of your spreadsheet (or use the CONCATENATE function).

Merging Cells Can Still Be Useful

While you’ll need to use CONCATENATE to merge cells without losing data, there are still some uses of merging in Excel. It’s often used for formatting to clean up a table, for example, which may help you get a better print of your spreadsheet:

Using Merge in Excel for formatting

In general, though, it’s better to use CONCATENATE if you’re looking to merge cells without losing data.
That being said, you may find that you come across a situation where you need to merge cells in Excel. Even an Excel beginner will need to use them every day. So, when you find one of those situations now, you’ll know exactly how to do it.
And don’t forget that you can merge Excel files and sheets too!

Source: https://www.makeuseof.com/tag/merge-unmerge-cells-excel/

Wednesday, June 21, 2017

How to use Microsoft Excel's Conditional Formatting

PCWORLD- Set up your spreadsheet to highlight important data automatically.
Microsoft Excel's conditional formatting is a wonderful "automatic" feature that allows you to formats cells based on the value of those cells or the value of the formulas in those cells. For example, you can specify that all the sales totals in your spreadsheet that exceed $5,000 are highlighted in yellow; or all the dates prior to the current year use a dark-green font; or use a shape or ratings icon to flag all duplicate values above 12,000. The options are endless and, in addition to all the preset formats, you can create your own custom formatting rules.
The best thing about this feature is that it provides a quick snapshot of your spreadsheet when you view it or show it to others. Because the formatting is based on values, you don't have to do anything to make it work except update your data.
A. Format cells that meet these value conditions

1. Open a sales spreadsheet or enter a dozen names with sales totals for the first four months of 2017; that is, Jan, Feb, Mar, and Apr.
2. Highlight the Jan column of sales totals.
3. Select Home > Conditional Formatting > Highlight Cells Rules > Greater Than.
4. Excel displays the Greater Than dialog box. In the field box under Format cells that are GREATER THAN, enter a number (for this example, 5000) and click OK.
5. Note that all the sales totals in column B that are greater than $5,000 are now highlighted in light green with a dark-green font.

01 highlight all cells greater than 5000 PC World / JD Sartain
Format all cells greater than 5000.
6. Use the same instructions above to highlight and format cells that are less than 5,000.

02 highlight all cells less than 5000 PC World / JD Sartain
Format all cells less than 5000.
7. The other options that apply to values are Format Cells BETWEEN two numbers, Format Cells that are EQUAL TO a specific number, and Format Cells that CONTAIN Duplicate or Unique values. Note the highlighted cells in the Mar and Apr columns.

03 highlight cells between two numbers equal to or containing PC World / JD Sartain
Format cells between two numbers, equal to a specific number, or containing unique or duplicate values.
B. Format cells that contain specific text
You can also use the Conditional Formatting feature to make certain text (letters, word, phrases, etc.) stand out.
1. Highlight the first column (A) of names.
2. Select Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
3. Excel displays the Text that Contains dialog box. In the field box under Format Cells that Contain the Text, enter the text you'd like to highlight (e.g., Smith) and click OK.
4. Note that all the Smith names are now highlighted in light red with a dark-red font.

04 format all cells that contain specific text PC World / JD Sartain
Format all cells that contain specific text.
C. Format cells that contain a Date within a range
1. Enter some dates in column F.
2. Select Home > Conditional Formatting > Highlight Cells Rules > A Date Occurring.
3. Excel displays the A Date Occurring dialog box. In the field box under Format Cells that Contain a Date Occurring, enter a date range such as Today, Yesterday, Tomorrow, This Month, Next Month, etc., and click OK.
4. Note that all the dates for April are now highlighted in light red with a dark-red font.

05 format cells that contain a date within a range PC World / JD Sartain
Format cells that contain a Date within a range.
D. Top/Bottom rules
The next section of Conditional Formatting features Top/Bottom rules, which provides formatting for the Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, and Below Average items. The Top and Bottom Percent and the Top and Bottom Items are self explanatory, right? In each highlighted range, Excel formats the highest or lowest numbers and the highest or lowest percent.
1. Highlight column B.
2. Select Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.
3. Excel displays the Top 10 Items dialog. The system defaults to 10, then prompts for a preset format. Select Light Red Fill with Dark Red Text, and click OK.
4. Next, highlight column C and follow the instructions above to format the Bottom 10 Items.

06 format cells for the top or bottom 10 PC World / JD Sartain
Format cells for the Top 10 or Bottom 10 Items in a range.
5. Highlight column D.
6. Select Home > Conditional Formatting > Top/Bottom Rules > Top 10%.
7. Excel displays the Top 10% dialog. The system defaults to 10, then prompts you for a preset format. Select Yellow Fill with Dark Yellow Text, and click OK.
8. Next, highlight column E and follow the instructions above to format the Bottom 10%.
Note: You can enter any number between 1 and 9999 for the Top and Bottom Items and any number between 1 and 999 for the Top and Bottom percent.

07 format cells for the top or bottom 10 percent PC World / JD Sartain
Format cells for the Top 10 or Bottom 10 percent in a range.
9. The Above and Below Average options are calculated like this: Excel averages the values in the highlighted column and determines the "average" number. In column B, the average is $3231. In column C, the average is 5523.50 You can check this by using the command =AVERAGE(B2:B13) and/or =AVERAGE(C2:C13).
10. Highlight column B for the Above Average formats and column C for the Below Average formats.
11. Follow the instructions above to select a preset format for each column.

08 format cells for the abovebelow average values PC World / JD Sartain
Format cells for the Above and Below Average values in a range.
E. Data Bars & Color Scales
Data Bars and Color Scales are formatting tools that spice up your spreadsheets with visuals that mean something. For example, Data Bars compare values—obviously the longest bars represent the highest values and the shortest bars denote the smaller values. Conversely, the Color Scales show a range between the highest, lowest, and mid-range values.
1. Highlight column B and choose a Gradient Fill Data Bar from Conditional Formatting > Data Bars.
2. Highlight column C and choose a Solid Fill Data Bar from Conditional Formatting > Data Bars.
3. Notice that the Data Bars function like a horizontal bar chart inside each cell. Expand the column width to see a better visual for the Data Bars.
4. Highlight column D and choose a Color Scale from Conditional Formatting > Color Scales, then highlight column E and choose a different Color Scale.
5. Notice how the colors change when the numbers break, and note the difference between columns D (Mar) and E (April).

09 use data barscolor scales to spice up your spreadsheets PC World / JD Sartain
Use Data Bars & Color Scales to spice up your spreadsheets with visuals that mean something.
E. Icon Sets
The Icon Sets also show a range of highest, lowest, and midrange values.
1. For example, highlight column B and choose Home > Conditional Formatting > Icon Sets > Shapes and choose the first set of dots on the list. Notice that Excel averaged out the numbers in the list and determined that all 1000s and 2000s are marked with red dots, 3000s and 4000s have yellow dots, and 5000s have green dots.
2. Highlight columns C, D, and E and select one Icon Set from each of the remaining three sets. Notice the difference between how the values are grouped in the Icon Sets with three icons, four icons, and five icons.

10 icon sets presets provide three fourfive levels of formatting copy PC World / JD Sartain
Preset Icon Sets provide three, four, and five levels of formatting.
Note: All the Icon Sets in this group: Directional, Shapes, Indicators, and Ratings provide three, four, and/or five levels of formatting, which again, determine the highest, lowest, and midrange values. You can easily change the formatting levels to more accurately fit your specific needs.
In fact, you can customize all of the Conditional Formatting features using self-defined rules that are easy to create through Excel's simple "walk-through" menus. We'll cover that next.

Source: http://www.pcworld.com/article/3181010/software/how-to-use-microsoft-excels-conditional-formatting.html#tk.rss_howto

Friday, May 20, 2016

How Excel counts business days, holidays and more

Kumar Malyala asked if there’s a way in Excel to count the number of business days between two dates.
It’s easy to find the number of days between two dates in Excel. You just subtract the earlier date from the later one. If you put July 19 in A1, May 5 in A2, and enter =a1-a2 in A3, you’ll get 75—the number of days between May 5 and July 19.
However, if you don’t want Saturdays and Sundays in your total, you’ll have to work a bit harder. And if you need to take holidays into consideration, it gets trickier still.
But not too tricky.
[Have a tech question? Ask PCWorld Contributing Editor Lincoln Spector. Send your query to answer@pcworld.com.]
With or without holidays, you’ll need to use the networkdays function. And no, it has nothing to do with connectivity. The name means net workdays, not network days.
I tested this in Excel 2010 and 2016.
To find the number of weekdays between two dates, simply use the formula =networkdays(Start_date,End_date), with Start_date addressing the cell containing the earlier of the two dates, and End_date addressing the later date. For instance, if you have April 16 in A1 and September 19 in A2, =networkdays(a1,a2) will give you the answer 111.
0516 work days
Keep in mind that networkdays counts the Start_date in its result. If your Start_date is a Monday, and the End_date is the same week’s Friday, networkdays will return the number 5, not 4.
To subtract your office’s holidays from the networkdays results, create a list of company holidays somewhere on the spreadsheet (it can be on a different tab). The actual dates of the holidays should be listed in a column.
You’ll need to create what Excel calls a named range of those holiday dates. Select the dates, then go to the Formula ribbon and pick Define Name (Excel 2010) or Defined Names > Define Name (Excel 2016). (For more on named ranges, read JD Sartain's detailed how-to.)
0516 holiday list
In the resulting dialog box, give the range the name holidays.
Then add the argument holidays to the end of the networkdays formula. In other words, instead of =networkdays(a1,a2), enter =networkdays(a1,a2,holidays)

The weekdays where your office is closed will no longer be counted in the formula.
by Lincoln Spector Contributing Editor

Source: http://www.pcworld.com/article/3056908/software/how-excel-counts-business-days-holidays-and-more.html

Monday, May 9, 2016

How to Find Data in Access 2016

When you want to track down a particular record right now, creating a query for the job is overkill. Fortunately, Access 2016 has a very simple way to find one specific piece of data in your project’s tables and forms: the Find command.
Find is found — big surprise here — in the Find section of the Home tab, accompanied by a binoculars icon. You can also get to Find by pressing Ctrl+F to open the Find dialog box.
Although the Find command is pretty easy to use, knowing a few tricks makes it even more powerful, and if you’re a Word or Excel user, you’ll find the tricks helpful in those applications, too — because the Find command is an Office-wide feature.

Finding anything fast

Using the Find command is a very straightforward task. Here’s how it works:
  1. Open the table or form you want to search.
    Note that the Find command works in Datasheet view and with Access forms and becomes available as soon as a table or form is opened.
  2. Click in the field that you want to search.
    The Find command searches the current field in all the records of the table, so be sure to click the correct field before starting the Find process. Access doesn’t care which record you click; as long as you’re on a record in the correct field, Access knows exactly which field you want to Find in.
  3. Start the Find command.
    You can either click the Find button in the Find section of the Home tab or press Ctrl+F.
    The Find and Replace dialog box opens, ready to serve you.
  4. Type the text you’re looking for in the Find What box.
    Take a moment to check your spelling before starting the search. Access is pretty smart, but it isn’t bright enough to figure out that you actually meant plumber when you typed plumer.

    The Find and Replace dialog box.
    The Find and Replace dialog box.
  5. Click Find Next to run your search.
    • If the data you seek is in the active field, the Find command immediately tracks down the record you want.
      The cell containing the data you seek is highlighted.
      What if the first record that Access finds isn’t the one you’re looking for? Suppose you want the second, third, or the fourteenth John Smith in the table? No problem; that’s why the Find and Replace dialog box has a Find Next button. Keep clicking Find Next until Access either works its way down to the record you want or tells you that it’s giving up the search.
    • If Find doesn’t locate anything, it laments its failure in a small dialog box, accompanied by this sad statement:
      Microsoft Access finished searching the records. The search item was not found.
If Find didn’t find what you were looking for, you have a couple of options:
  • You can give up by clicking OK in the small dialog box to make it go away.
  • You can check the search and try again (you’ll still have to click OK to get rid of the prompt dialog box). Here are things to check for after you’re back in the Find and Replace dialog box:
    • Make sure that you clicked in the correct field and spelled everything correctly in the Find What box.
      You can also check the special Find options to see whether one of them is messing up your search.
    • If you ended up changing the spelling or options, click Find Next again.

Shifting Find into high gear

Sometimes just typing the data you need in the Find What box doesn’t produce the results you need:
  • You find too many records (and end up clicking the Find Next button endlessly to get to the one record you want).
  • The records that match aren’t the ones you want.
The best way to reduce the number of wrong matches is to add more details to your search, which will reduce the number of matches and maybe give you just that one record you need to find.
Access offers several tools for fine-tuning a Find. To use them, open the Find and Replace dialog box by either
  • Clicking the Find button on the Home tab
  • Pressing Ctrl+F
If your Find command isn’t working the way you think it should, check the following options. Odds are that at least one of these options is set to exclude what you’re looking for.

Look In

By default, Access looks for matches only in the current field — whichever field you clicked in before starting the Find command. To tell Access to search the entire table instead, choose Current Document from the Look In drop-down list.
To search the entire table, change Look In.
To search the entire table, change Look In.


Your options are as follows:
  • Any Part of Field: Allows a match anywhere in a field (finding Richard, Ulrich, and Lifestyles of the Rich and Famous). This is the default.
  • Whole Field: This requires that the search terms (what you type in the Find What box) be the entirety of the field value. So Rich won’t find Ulrich, Richlieu, Richard, or Richmond. It finds only Rich.
  • Start of Field: Recognizes only those matches that start from the beginning of the field. So Rich finds Richmond, but not Ulrich.
    This option allows you to put in just part of a name, too, especially if you know only the beginning of a name or the start of an address.
To change the Match setting, click the down arrow next to the Match field and then make your choice from the drop-down menu that appears.
Using the Match option.
Using the Match option.


If you’re finding too many matches, try limiting your search to one particular portion of the table with the help of the Search option. Search tells the Find command to look either
  • At all the records in the table (the default setting)
  • Up or down from the current record
Clicking a record halfway through the table and then telling Access to search Down from there confines your search to the bottom part of the table.
Fine-tune your Search settings by clicking the down arrow next to the Search box and choosing the appropriate offering from the drop-down menu.

Match Case

Checking the Match Case check box makes sure that the term you search for is exactly the same as the value stored in the database, including the same uppercase and lowercase characters.
This works really well if you’re searching for a name, rather than just a word, so that rich custard topping is not found when you search for (capital R) Rich in the entire table.

Search Fields as Formatted

This option instructs Access to look at the formatted version of the field rather than the actual data you typed. By “formatted” this means with formatting applied to the content – numbers formatted as percentages, dates set as short dates vs medium dates, and so on.
Limiting the search in this way is handy when you’re searching for dates, stock-keeping unit IDs, or any other field with quite a bit of specialized formatting.
Turn on Search Fields as Formatted by clicking the check box next to it.
This setting doesn’t work with Match Case, so if Match Case is checked, Search Fields as Formatted appears dimmed. In that case, uncheck Match Case to bring back the Search Fields as Formatted check box.
Most of the time, this option doesn’t make much difference. In fact, the only time you probably care about this particular Find option is when (or if) you search many highly formatted fields.

Source: http://www.dummies.com/how-to/content/how-to-find-data-in-access-2016.html?cid=dn_article

Putting Continuous Accounting to Work

Therese Tucker
Therese Tucker

My March 2 column on continuous accounting, “Stopping the Madcap Sprint to Close the Books,” apparently struck a chord, encouraging many readers to write thoughtful responses. Most wanted to know how continuous accounting “actually works.” This piece is my reply to these inquiries.
First a quick recap of my earlier column: Continuous accounting is a measured and balanced alternative to compressing a month’s (or more) worth of accounting tasks to close the books into a few days at the end of the month, quarter, or year. Rather, the work to perform such closing tasks is conducted incrementally by accountants during the month, quarter, and year.

The benefits include a reduction in the pile-up of accounting work and the stress this causes; lower risk of financial irregularities; and an earlier opportunity for financial planning & analysis to deliver the business forecast. CFO readers intrigued by these benefits wanted to know, as one wrote, “How does this actually work?”

To answer the question, let’s start with today’s inefficient workload procedures. As part of their bookkeeping, accountants in midsize and larger companies manually sift through thousands of spreadsheets generated by different systems. Without an automated system, it’s an uphill climb to methodically tick and tie the ledgers to confirm that every item is accounted for and properly connected to related items. This confirmation process is therefore pushed off to the end of the month, quarter, or year.

Another inefficiency derives from batch processing. When digital documents replaced paper-based accounting records, technological limitations caused the accounting work to be stored up during working hours and executed when computer systems were offline. As the volume of data expanded, the process took more time to run. Ultimately, the system was not ready to use when accountants required its use. Again, the solution was to push off the work.
What’s wrong here? Top-notch accounting talent is underutilized during non-peak periods and overworked during the peak period. To alleviate some of the workload, finance and accounting hires temp workers that eat up the budget.
These many inadequacies and the pains they inflicted — from burned-out accountants and pointless costs to an increased risk of errors, out-of-date results, and zero time for analysis — raise the obvious question: “What if we could account for the business at close at the speed of transactions?”
Well, the answer is, you can.



And Here’s How

The first thing to do is to rethink the internal paradigm of accounting, adjusting the mindset to consider the F&A process as one of continuous improvement. The goal is to optimize the accounting process one task at a time, then rinse and repeat. By building automation and accuracy into the accounting process, accountants are eliminating the time wasted performing redundant exercises and fixing errors. In this voyage, automated technology is a tool to achieve the ends; it is not the beginning of the journey.

Great things are possible once a philosophy of continuous accounting is in place. Instead of bringing up all the data in one batch process as before, the batch is split up into a series of smaller tasks. Tasks are scheduled as early in the accounting process as possible and embedded within the everyday workflow of the accountants. By breaking the tasks into smaller pieces, the completion of these elements becomes routine, a process that takes its cue from agile software development. Tasks are automated where possible and standardized elsewhere.

The last step is to monitor the effectiveness of these process efficiency improvements through predictive analytics, measuring the time it takes to close the books, the total costs per full-time F&A employee, and the average number of days for an accountant to complete a selected assignment.



Continuous Accounting in Action

Sounds all well and good, but readers have asked for “actual examples.” Let’s apply the above steps to a particular F&A task — account reconciliations.
The current state of many finance organizations is to perform the reconciliations at the end of the month, reducing the opportunity to conduct proactive analysis before then. The alternative is to split the batch process into smaller tasks, such as the daily reconciliation of key accounts. To perform this task earlier, scheduled end-of-month reconciliations are shifted to event-based reconciliations, i.e., as they occur. Automation is used to automatically reconcile the key accounts and match these transactions. Accountants are now in the position to investigate anomalies associated with these reconciliations throughout the month, minimizing the period-end rush.

Let’s apply continuous accounting to another routine F&A task — intercompany accounting, the process of balancing the accounts between two different company branches to bring the balances to zero in the general ledger. The current state in most F&A organizations is to wait until the books are closed and entries are booked by these different entities before performing the task. Since the time it takes to record the transactions varies by entity, there’s no time to react to possible discrepancies, culminating in a rushed review at the period-end.
By dividing the batch process into smaller tasks, intercompany transactions can be recorded and approved on each entity’s books in real-time throughout the period. The task no longer waits until the end of the month, quarter, or year, as it is embedded along with the proper controls into daily workflows for automatic approval. By automating this process, accountants are freed to focus on value-added services to the CFO.

As the reader can see, continuous accounting is a philosophical approach, not simply a technological one. Only by first analyzing the current state of F&A can a vastly more efficient future state be designed, followed by its development and continuous improvement.
Therese Tucker is CEO of financial automation software provider BlackLine.

Source: http://ww2.cfo.com/accounting/2016/05/putting-continuous-accounting-work/

Saturday, April 23, 2016

How to Use Quick Analysis in Excel 2016

When you need to do a speedy analysis of your data in Excel 2016, consider using the Quick Analysis feature. Here are some points to keep in mind about Quick Analysis:
  • When you select a range of cells, a small icon appears in the lower right corner of the selected area. This is the Quick Analysis icon, and clicking it opens a panel containing shortcuts to several types of common activities related to data analysis.
  • Click on of the five headings to see the shortcuts available in that category. Then hover over one of the icons in that category to see the result previewed on your worksheet:
    • Formatting: These shortcuts point to conditional formatting options. For example, you could set up a range to make values under or over a certain amount appear in a different color or with a special icon adjacent.

      Open the Quick Analysis panel by clicking its icon. Then choose a category heading and click an ico
      Open the Quick Analysis panel by clicking its icon. Then choose a category heading and click an icon for a command.
    • Charts: These shortcuts generate common types of charts based on the selected data.

      Quick Analysis offers shortcuts for creating several common chart types.
      Quick Analysis offers shortcuts for creating several common chart types.
    • Totals: These shortcuts add the specified calculation to adjacent cells in the worksheet. For example, Sum adds a total row or column.
      Notice that there are separate icons here for rows vs. columns.
      Notice also that in this category there are more icons than can be displayed at once, so there are right and left arrows you can click to scroll through them.

      You can use Quick Analysis to add summary rows or columns.
      You can use Quick Analysis to add summary rows or columns.
    • Tables: You can convert the range to a table for greater ease of analysis. You can also generate several different types of PivotTables via the shortcuts here. A PivotTable is a special view of the data that summarizes it by adding various types of calculations to it.
      The PivotTable icons aren’t well-differentiated, but you can point to one of the PivotTable icons to see a sample of how it will summarize the data in the selected range. If you choose one of the PivotTable views, it opens in its own separate sheet.

      You can convert the range to a table or apply one of several PivotTable specifications.
      You can convert the range to a table or apply one of several PivotTable specifications.
    • Sparklines: Sparklines are mini-charts placed in single cells. They can summarize the trend of the data in adjacent cells. They are most relevant when the data you want to trend appears from left to right in adjacent columns.