All articles in 'Learn Excel' Category

We have a macbook at home. Like all latest macbooks, this one too came with a trail version of iWork. Even though I have used iWork before, this time I wanted to compare iWork numbers with Excel. In this post, I want to highlight 7 really cool features for iWork and how Microsoft excel can benefit from implementing the same.

Over the last few years, there has been much debate about the merits and perils of Microsoft Ribbon UI in Excel 2007. Personally I think ribbon is a good way to explore an application. I have gotten used to it since I tested excel 2007 for first time. Now, during the rare occasions I work […]

In how to get tickmarks in excel, Jon commented,
“[…] Better yet, use real checkboxes, so the user can change them with the mouse.”

That got me thinking,

In excel, you can add a checkbox to spreadsheet using developer tools. But what if you need a whole bunch of checkboxes, one in each cell?

Here is tricky scenario, faced by Basil, our forum member, “I want to have Excel display a wing ding check mark when a user types “y” in a cell. I have been trying to do a substitute formula but putting the symbol in an unused portion of the spreadsheet and calling it to the selected cell but I can’t get it to work. Any thoughts?”

We can use either cell formatting or conditional formatting to achieve this.

Tracking issues and risks is where most of the project management time goes. Once the project planning and organizing activities are in good shape, most of the project management activities are around risk management and issue tracking. In this installment of project management using excel, we will learn how to create a simple issue tracker template using excel and how to analyze issues using excel.

Here is a quick productivity tip if you work with same set of excel (or word or powerpoint) documents everyday. Just pin the documents to recent documents list in the Office Menu in MS Office 2007. See this: More excel 2007 productivity tips & double click tricks.

Excel has a very useful feature called “cell comments” using which you can add comment to a cell. This is a very good way to gather remarks and review comments when a workbook is shared with colleagues and others. But what if you have typed a ton of cell comments and now want a way to extract them and do something with that data?

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

We all know that incell charts are a very cool way to explore and visualize data. Personally I like them so much that I have written several tutorials on it here. Today we will see how a Job dashboard on “50 best cities for finding a job” originally prepared by Indeed job search engine can be recreated in Excel using In-cell charts. The final outcome is something like this.

Here is a quick excel formula tip to start your week. Use ROWS() and COLUMNS() formulas next time you need sequential numbers. What does ROWS() excel formula do? ROWS excel formula takes a range as an argument and tells you how many rows are there in that range. For. eg. ROWS(A1:A10) gives 10. How can you […]

Starting this month I will announce one member of our little community as member of month. It is to honor the contribution they made.

Jeff Weir is our member of month for July, 2009. He not only commented more than 40 times in the last month, but he even wrote a marvelous guest post on the chart busters series. I have learned several valuable excel and charting tips from him in the past few weeks. I am sure some of have too. Thank you Jeff.

Also in the post we have some excel links worth checking.

Here is an excel formula quick tip that can come handy when you need to count the number of unique values in a range of cells. Assuming we have a list of values in the range:B5:B15 and we want to know how many unique values are there,
you can use the almighty SUMPRODUCT formula like this: SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15)).

Read the rest of this post to understand how the formula works. You can also find resources to work with duplicate values in excel.

Timesheets are like TPS reports of any project. Team members think of them as an annoying activity. For managers, timesheets are a vital component to understand how team is working and where the effort is going. By using Microsoft Excel capabilities you can create a truly remarkable timesheet tracking tool.

In this installment of project management using excel series, we will learn 3 things about timesheets and resource management using Excel
1. How to setup a simple timesheet template in excel?
2. How to make a more robust timesheet tracker tool in Excel?
3. How to use the timesheet data to make a resource loading chart?

I wrote an excel formulas e-book that makes learning 75 most frequently used excel formulas as simple as eating pie. If you are wondering the book is worth your investment, read these wonderful reviews the book has received from fellow excel bloggers in the community. Jimmy on Code for Outlook and Excel and Tony on Support Analytics.

Time for blowing my own trumpet and patting my own back over my pointy hair. I feel very proud to announce that our little community at Pointy Haired Dilbert now has its five thousandth member.

Take a minute and pat yourself on the back. This is an achievement because of you. Go ahead, I am waiting.

Ok, enough patting. Time for some gifts and fun.

We have 2 contests to celebrate the occasion. This is the first one. I will announce the second contest tomorrow. Read the rest of this post to find out more about the twitter formula contest