How to Solve Headphones Jack Moves, Music Pauses Problem

Does your media player pause when you move your headphones even slightly?

If so, the problem is likely due to lint and dust that has built up in the headphone jack.  You need to clean out the debris. The easiest way is to use some scotch tape.  Simply roll up a little bit of scotch tape into a long, pointy cylindrical shape and stick into the jack and move it around.  Then remove and note the amount of dust and lint removed.  Do this a couple of times with fresh tape each time.  Once the tape comes out clean, you've likely solved the problem.  To verify that you have, plug in your headphones, move them around, and see if the player no longer pauses as a result of the slightest movement of the phones. 

If you want to prevent this from happening again, try buying some headphone jack covers from Amazon.

Excel Tip: How to Easily Remember the Index-Match Formula

Lots of experts tout Index-Match as a better look up formula than venerable Vlookup.  However, it's a little hard to remember its formula (which is a mash up of the Index and Match formulas).

Here's how I do it:

=INDEX(Range with values I don't have, (MATCH(Look-up value, Range with values I do have, “0″))

Everything I have goes in MATCH; what I don't have goes in INDEX.  For example:
  • Range with values I don't have = Range of product IDs
  • Look-up value = Product Name
  • Range with values I do have = Range of product names
The 0 at the end of the formula means you're looking for an exact match.  Check out the Index-Match article linked above for a fuller explanation.

How to Bold a Cell in Excel Based on Another Cell's Contents

Ever want to bold a cell in Excel based on the contents of another cell?  Here's how...

Say you need cells in column P to be bolded if column A contains the word "total."  To do this you'd:
  1. First, select the cells that you want to which you want to apply conditional formatting.
  2. Then click Conditional Formatting from the top ribbon.
  3. Next select New Rule.
  4. Then click "Use a formula to determine which cells to format."
  5. Enter this formula in "Format values where this formula is true": =ISNUMBER(SEARCH("total",A1))=TRUE 
  6. Select your format; in this example, you'd use bold.
After this all the cells that have the word "total" in them cells against them will be bolded.

If you want to learn more about conditional formatting in Excel based on values in other cells, check out this excellent in-depth article at AbleBits.  If you want to add to that knowledge on how to format a cell conditionally based on its own text, check out this Microsoft Office article.

Excel Tip: Abbreviate Large Chart Axis Numbers

Charts are important way of summarizing data in Excel.  For them to work best, they need to be uncluttered.  One way to clean up a chart is to replace large numbers with an abbreviated version.  For example, you could replace 1,600,000 with 1.6M.

To do that, right-click on the chart's axis and select Format Axis.  Then click on Number in the dialog's left column and add this code in the Format Axis's Custom section:


If you're curious how Custom Formats work in Excel, check out these these great articles on the topic:

Excel Tip: How to Not Show Zero Values in Percentages

Often not showing zero values in Excel makes data easier to read.  Percentages by default display zeroes; if you want to replace that with a dash, blank, etc. use the following formula in the Custom Format section:

0.00%;-0.00%;"-"  (you can replace the "-" with blanks (" ") or anything else)

The easiest way to bring up the dialog is by hitting CTRL+1, which will get you what you see here:

If you're curious how Custom Formats work in Excel, check out these these great articles on the topic: