Follow Better Tech Examples for Free

Subscribe to BTE for free by email or RSS by using the links below and you'll get a free e-book!

Follow BTE for free by RSS. (What is RSS?)

Follow Better Tech Examples for free by Email.

Replace Large Numbers on Excel Chart Axis with M or K Symbols

When making charts in Excel, it's common to to see large numbers, like those in the millions, clutter up the axis.  Wouldn't it be nice to simply abbreviate all those zeroes with the universal symbols K or M instead?  Here's how:
  1. Right click on the chart axis and select Format Axis
  2. In the left list of option in the window that opens, select Number.
  3. In the Category box that appears next, select Custom.
  4. In the Format Code box directly underneath where you highlighted custom, enter the following code in bold exactly as shown [>=1000000]0,,"M";[>=1000]0,"K";0
  5. Click the Add button
  6. Click the Close button.
Ta da! Much easier to read, huh?

Strip Time from Date in Excel

If you have the time appended to a date in Excel, here's the fix.

Type the following formula into an adjacent empty cell:  =MONTH(cell) & "/" & "/" DAY(cell) & "/" YEAR(cell)

Here's a quick interpretation of the formula:

& = concatenates, or joins together, the elements of a formula
"/" = separates date components and could also be "-" or whatever
(cell) = insert cell address here

Then you'll simply highlight the newly created dates, copy, and paste special values to retain the dates without needing a cell reference or formula at all.

How to Group Two Excel Pivot Tables Differently Even if Based on the Same Data

Recently I was making a report in Excel and I needed to group the same data in several pivot tables a couple of different ways.  So I made one pivot, grouped it, made the second pivot, grouped it, but when it came time to admire my work, saw they were both grouped alike.  In other words, they grouped in lock step -- I couldn't group them differently. 

Then I had to teach myself how to group two pivots of the same data in different ways.  The key is to use different data caches for each pivot. 

To make the pivots use different caches, you need to use the Pivot Table Wizard.  In Excel 2007 and later, the Pivot Table Wizard isn't on the Ribbon so you'll have to use a keyboard shortcut to start the wizard and create a pivot table with its own pivot cache. Here are the steps to do so:

  1. Hold down the ALT key and then depress D and then P. This combo starts the Pivot Table Wizard.
  2. Select "Excel List or Database" and click the Next button.
  3. Select the data source for the pivot table and click Next.
  4. Next Excel shows a message box. Be careful! If you click Yes, it'll use the same pivot cache that you already set up for the first pivot table, which saves memory prevents you from creating separate caches upon which to make different groupings for separate pivot tables.
  5. In that same dialog, click "No," so Excel will create a separate pivot cache for the new pivot table.
  6. Complete the wizard and then set up your new pivot table.

You can now change the grouping each pivot table without affecting the other pivot tables that are based on the same data.

How to Group By Quarter in Excel Pivot Tables

Usually when I group in an Excel pivot table I have to do it by months.

But one day I found I had to group by quarter.  Normal quarters are a piece of cake to group by in an Excel pivot.  Custom quarters are not quite so intuitive, but still are are easy.

Grouping by Default Quarters
Right click on the column in your pivot that you want to group on and select the "Group" option.

Then you'll simply select the "Quarters" option while making sure no other option also is selected.

How to Group by Custom Quarters
But what if your quarters don't fall on the January, April, July, October start dates but, say, are shifted up one month?  Then you'll have to avoid the "Quarters" option, which seems odd, and use the days option.  Select your start and end dates in the group dialog and then select "Days" and put in the number of days that bound your quarter (for example, 89, 90, or 91 -- something like that).

How use Word to Find and Replace a Random Number of Spaces with a Single Tab Character

Ever receive or download some file with data that's separated by spaces that you'd like to replace with tabs so you could convert to table but a standard find and replace wouldn't work because the number of spaces involved was different between items?

If so, change multiple spaces into a single tab by doing the following:

  1. Open Find And Replace dialog box.
  2. Hit the More button.
  3. In the Find What text box, enter a single space character by hitting your space bar once and then type following characters, exactly as shown: {2,}
  4. In the Replace With box, enter ^t.
  5. Check the Use Wildcards option. 
  6. Click Replace All and then Close.

Cool, huh?  Now your multiple spaces are single tabs, making conversion to table easy.