Archive for May, 2009

Array formula to check if a number is prime [just for fun]

Published on May 29, 2009 in Learn Excel

I am math-geek-wannabe, if there ever is such a category. During my 3rd year of graduation I went and purchased the volume 2 of Donald Knuth’s Art of Computer Programming and thus began my love with all things random and prime. I never really became the math-geek I always wanted to, instead I became an insurance expert with tons of passion for data and visualization. But when I get a chance to poke with randomness or numbers, I always lap it up with joy. And that brings us to an interesting array formula trick to check if a number is prime or not.

(assuming the number is in the cell B2) type the below formula and

=IF(MIN(MOD($B$5,ROW(INDIRECT(“2:”&INT(SQRT($B$5))))))=0,”not prime”,”prime”)

hit ctrl+shift+enter and bingo, it tells you if the number is prime or not. Read on to find how it works

Continue »

Intraday Candlestick Charting [Stock Charts]

Published on May 28, 2009 in Charts and Graphs
Intraday Candlestick Charting [Stock Charts]

Gene asks me in an email, “I’m trying to graph candlestick charts in Excel for 10 minute candles. Excel seems to allow daily only with its stock templates. Can you point me to any resources for creating intraday candle charts?”. Of course, you can create intraday candlestick charts just the way you would create normal candlesticks, just change the axis options once you are done. Read the post to find more.

Continue »

Hide Formula Errors While Printing [Quick tip]

Published on May 27, 2009 in Excel Howtos, Learn Excel
Hide Formula Errors While Printing [Quick tip]

Here is a quick excel printing tip. Use the page setup options from print preview area of the spreadsheet to select how you want to handle the formula errors when the worksheet is printed. You can set it to blank from the “sheet” tab of the page setup dialog in Microsoft excel

Continue »

Do you know these Excel 2007 Productivity Secrets (Hint: Coffee is not one of them)

Published on May 26, 2009 in Excel Howtos, Learn Excel
Do you know these Excel 2007 Productivity Secrets (Hint: Coffee is not one of them)

Do you know these excel 2007 productivity secrets? (1) How to turn on the clipboard pane so that you can do rapid copy pasting (2) How to lock a feature for repeated use (3) How to copy charts as pictures (4) How to features not on ribbon to the quick access tool bar (5) How to change the default file save settings so that excel always save files in earlier version and much more. Go ahead and read the secrets and become excel guru.

Continue »

Excel Links – Dilbert is Recession-proof Edition

Published on May 25, 2009 in excel links

In this week’s excel links, we will learn how to jump to next cell during data entry, an array formula trick to include all values conditionally, about auto-filters in excel and much more.

Continue »

Now Edit Google Spreadsheets using MS Excel

Published on May 21, 2009 in Excel Howtos, Google Spreadsheets - Tip & Tricks
Now Edit Google Spreadsheets using MS Excel

Do you like the cool features of Google docs spreadsheets but too comfortable with MS Excel’s UI? Well, the supercool OffiSync add-in is for you. It is developed for Oudi Antebi an former marketing manager at Microsoft.

Once you finish the installation of the addin you will see a new ribbon in the corresponding applications (word, excel and powerpoint).

Continue »

Signup for Microsoft Excel 2010 Technical Preview [Office 2010]

Published on May 20, 2009 in excel links
Signup for Microsoft Excel 2010 Technical Preview [Office 2010]

Are you curious to take a peek at future? You should sign up for the MS Office 2010 Technical Preview.

You can also stay up to date about the MS Office 2010, visit the http://www.office2010themovie.com/

I have signed up for the preview, but in the weekend I will check my MSDN subscription to see if there is something for MVPs to install and see. Then I can post some screenshots.

Continue »

Create a Dynamic Chart in Excel in 2 Minutes [spreadcheats]

Published on May 19, 2009 in Charts and Graphs, Featured, Learn Excel
Create a Dynamic Chart in Excel in 2 Minutes [spreadcheats]

Here is a really fun and simple way to create dynamic charts in Excel. It uses data filters in an innovative way and creates the dynamic effect. To create the dynamic charts, just follow these 5 steps:

(1) Prepare your charts: Make as many charts as you want. Lets say 3.
(2) Set up the area where dynamic charts will be loaded: Just take 3 cells in a row and adjust the row height and column width such that the charts can be fit inside snugly. Also, type the chart names (1 for each cell) in the cell. Let us say, the charts you have are for Costs, Sales and Profits, just type these names in the cells.
Read more…

Continue »

and then Dilbert is fired too

Published on May 18, 2009 in blogging
and then Dilbert is fired too

Bye, bye Pointy Haired Dilbert. Welcome Excel @ Work. Take a look at our new improved site. It is easier to read posts, comment, or find what you are looking for.

Continue »

Share Your Excel Tips for Your Week – Open Thread

Published on May 15, 2009 in Learn Excel

We have 24 hours more to go before your week ends. So go ahead and share your tips, the thread is open, Use the comments below to share your tips. Teach us something new. We are waiting…

Continue »

Excel Tips Submitted by You [Part 4]

Published on May 14, 2009 in Learn Excel

Today is our last installment of your week @ PHD. We have some exciting tips on using VBA, date formulas and index-match syntax.

Continue »

Excel Tips Submitted by You [Part 3]

Published on May 13, 2009 in Excel Howtos, Learn Excel

In the third installment of your week at PHD, we have a fantastic tutorial on R1C1 style of formula referencing, a nifty vlookup hack and adding web toolbars to excel UI. We have one more day to go, so rush!

Continue »

Excel Tips Submitted by You [Part 2]

Published on May 12, 2009 in Charts and Graphs, Learn Excel

In today’s installment of “your week @ PHD” we will learn how to use array formulas to refine text search, a bunch of very useful keyboard shortcuts, and a very simple tip on how to get ready when you want to make a chart. All really juicy tips, so read on.

Continue »

A Good Chart is a Story [Charting Principles]

A Good Chart is a Story [Charting Principles]

A good chart tells a story. Here is a fantastic example of what a good chart is. The chart itself is very simple and easy. But it brilliantly juxtaposes two interesting pieces of data : Obesity rates in countries and Time spent eating per day, to tell a story.

Continue »

Excel Tips Submitted by You [Part 1]

Published on May 11, 2009 in Excel Howtos, Learn Excel

The first installment of “your week @ PHD” features 4 excel tips shared by our readers: (1) A macro to unhide all sheets (2) a KPI Dashboard with VBA and Charts (3) Using Excel’s Find to Save time at work (4) An Array Formula to Solve a Tricky Problem.

Continue »