All articles in 'Learn Excel' Category

Excel Links of the Week – Do you want to run 10k with me edition

Published on Nov 17, 2008 in Learn Excel, personal

It has been almost 9 months since I crossed the 5k running mark for the first time in my life. Running is one of the most enjoyable things I do. I have been running between 5 and 7 kilometers ever since. I wanted to run 10 kilometers and finally I am confident of doing it. […]

Continue »

Seperating digits from a number [excel formulas]

Published on Nov 14, 2008 in Learn Excel
Seperating digits from a number [excel formulas]

Reader Joann asks through email: Take the number 117490. I need to break it down5 times: 11749 1174 117 11 1 You can use FLOOR() function to get the desired result, like this: =FLOOR(your-number/10^digit-no,1) See the image below:

Continue »

Is your (chart’s) underwear showing?

Published on Nov 14, 2008 in Charts and Graphs, Humor, Learn Excel
Is your (chart’s) underwear showing?

When I read Is your website’s underwear showing?, I couldn’t control the urge to write this one. So the big screaming friday question everyone… Is your chart’s underwear showing? What is my chart’s underwear? you may ask. My dear reader, it is the background color (that grayish shade excel adds by default). Also commonly known […]

Continue »

How to present changes in Market Share using Charts?

Published on Nov 14, 2008 in Charts and Graphs, Learn Excel
How to present changes in Market Share using Charts?

Most of us are comfortable with numbers, but we are confused when it comes to convert the numbers to charts. We struggle finding the right size, color and type of charts for our numbers. The challenge is two fold, we want to make the charts look good (we mean, really… really good) but at the […]

Continue »

Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

Published on Nov 12, 2008 in Learn Excel
Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series. Using COUNTIF() to replace pivot tables: We all know that you […]

Continue »

Excel Formula for Finding Repeated Words in a Cell

Published on Nov 11, 2008 in Learn Excel
Excel Formula for Finding Repeated Words in a Cell

If you ever need to know whether a particular word is repeated in a cell (for eg: a file extension or telephone number or name of the customer) here is how you can find it. =if(lower(cell-with-text)<>SUBSTITUTE(lower(cell-with-text),lower(word-to-check),””,2),”Yes”,”No”) We are using substitute() to replace the second occurance of a particular word and then compare this with original […]

Continue »

Excel Links of the Week – All Downloads at One Place Edition

Published on Nov 10, 2008 in excel links
Excel Links of the Week – All Downloads at One Place Edition

25+ Excel Templates – Download and Use [plug] Have you ever wondered what it would be like to get all the excel downloadables in Pointy Haired Dilbert in one simple page? Well, I did and created this simple excel templates – download page. It has 27 templates currently and I will add more as and […]

Continue »

Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

Published on Nov 6, 2008 in Analytics, Learn Excel
Extracting Unique, Duplicate and Missing Items using Formulas [spreadcheats]

Often I wish Microsoft had spent the effort and time on a data genie (and a set of powerful formulas) that can automate common data cleanup tasks like extracting duplicates, makings lists unique, find missing items, remove spaces etc. Alas, instead they have provided features like clippy which are intrusive to say the least. So […]

Continue »

Select & Show One Chart from Many

Published on Nov 5, 2008 in All Time Hits, Charts and Graphs, Featured, hacks, Learn Excel
Select & Show One Chart from Many

Yesterday I have learned this cool excel charting trick and I cant wait to share it with you all. The problem: I have too many charts & want to show one based on selection You have made 3 charts to show your company performance in the last 8 years. But you don’t want to clutter […]

Continue »

Relative vs. Absolute References in Formulas [spreadcheats]

Published on Nov 4, 2008 in Learn Excel
Relative vs. Absolute References in Formulas [spreadcheats]

This is the first installment of the spreadcheats series. I have used excel an year and half before I learned about the relative and absolute references. It was such a joyous feeling to find out that you can actually write one (or just a few) formulas and use the power of auto-fill to do the […]

Continue »

Excel Links of the Week [Nov 3]

Published on Nov 3, 2008 in excel links

The World’s First Music Video in Excel ACDC Rock n Roll Train This is totally cool. ACDC released an ASCII version of their “rock n roll train” video through excel spreadsheet. Why? So that you can download it at your office and watch it while your boss thinks you are working. 😀 I think this […]

Continue »

Best of Pointy Haired Dilbert – Oct 2008

Published on Nov 3, 2008 in blogging, Learn Excel

October has been another fantastic month since I stared this blog. We had 100k page views, the RSS subscriber count nearly touched 1500. There were 33 posts and 280 comments (WOW). Even our Facebook fan club reached the milestone of 10 members. For a loooong time myself and a friend whom I persuaded to join […]

Continue »

Introducing Spreadcheats – Become Supergood with Excel in 30 days

Published on Oct 31, 2008 in blogging, Learn Excel
Introducing Spreadcheats – Become Supergood with Excel in 30 days

Starting next week, PHD is going to feature a 30 post series on spreadcheats. The purpose of these posts is to make day to day spreadsheeting a breeze. Each post will be less than 200 words in size and aspires to make your day at office a little bit more productive. I have already identified […]

Continue »

Adding Box Plots to Show Data Distribution in Dashboards [Part 6 of 6]

Published on Oct 29, 2008 in Analytics, Charts and Graphs, Learn Excel
Adding Box Plots to Show Data Distribution in Dashboards [Part 6 of 6]

This is a Guest Post by Robert on Visualization Techniques for Excel KPI Dashboards. This 6 Part Tutorial on Management Dashboards Teaches YOU: Creating a Scrollable List View in Dashboard Add Ability to Sort on Any KPI to the Dashboard Highlight KPIs Based on Percentile Add Microcharts to KPI Dashboards Compare 2 KPIs in the […]

Continue »

How to NOT spend $ 150,000 and still dress up your charts

Published on Oct 28, 2008 in Charts and Graphs, Featured, Learn Excel
How to NOT spend $ 150,000 and still dress up your charts

By now everyone and their grandmother must have known about how Republican National Committee has spent $ 150,000 on Sarah Palin’s clothing and make up. I am a big fan of clothes. So much that I wear them everyday. But not all of us have a committee or fund raisers to dress up ourselves, none […]

Continue »