All articles in 'Learn Excel' Category

Check out two fabulous implementations of tweetboards in excel. Download the workbooks and play with them yourself. Thanks Fernando and Lee for sharing these workbooks with us.

Excel has very powerful formulas and add-ins for performing almost any kind of statistical analysis. Today we will learn how you can make a statistical distribution of test scores using excel. There is a downloadable workbook too, for you to take a look at the formulas and play with them.

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

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

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.

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.

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.

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…

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…

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

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!

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.

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.

That is right. Next week is your week on this blog. I will post content that is shared by you. So go ahead and share your tips and tricks with us. Read more to find out how you can share your tips with the community.

Here is a fun way to edit formula ranges. Just select the formula, press F2 and when you see the borders around the ranges used in that formula start dragging / resizing these borders to edit the formula range.