I’ve been working in Excel quite a bit lately. Here are some useful tips:

• When using multiple data rows in Pivot Tables, it automatically subtotals each row, which can be somewhat annoying. To disable this feature, use the following:

  Select the ‘product’ label in the PivotTable and from the PivotTable menu, try: PivotTable>Field Settings…>Subtotals>None (select) Source

• When inserting Excel charts in Word, sometimes you want them to be rotated to landscape view. This can also be achieved simply by doing the following:

  In Word, click where you want the spreadsheet. On the menu bar, select ‘Insert’>’Break’, and under ‘Section break types’ check ‘Next Page’. Insert your spreadsheet, set this page (ie. this section) up as landscape (File>Page Setup…). After the spreadsheet, insert another section break, reset the next section to portrait. Source

• There are some other useful tips about Pivot Tables that I can’t recall now, but here’s one of them:

  When inserting a “Calculated Item or Field” into a pivot table, the formulas use the “sum” of each item you select…this can’t be changed, apparently. Not the original ref, but a good source nonetheless

  You don’t even know how long I fought trying to figure out why my formulas weren’t working at all…
• Often times, the need comes up to count the number of unique values in a list. This can be pretty easily done, although it’s not immediately obvious:

  Use this forumla: =SUM(1/COUNTIF(Range1,Range1)). Now this is very important: Press Ctrl-Shift-Enter rather than only Enter; this will tell Excel to make it an array formula. You can use names or ranges in the countif function. Source

  Microsoft had a much more complicated solution that didn’t work for me.
• This issue comes up quite a bit for me as I frequently combine various workbooks and use autofilter quite a bit. Sometimes, though, it doesn’t display the count in the status bar…it just displays “Filter Mode”. Microsoft to the rescue on this one:

  1. On the Tools menu, click Options. 2. In the Options dialog box, click the Calculation tab. 3. On the Calculation tab, click Manual under Calculation. 4. Filter the list to see the filter results in the status bar. Source

I think that’s all for now. Maybe more later!