All articles in 'Excel Howtos' Category
New to Excel 2007 is the Picture Selection Pane.
This is a neat little tool which allows quick sorting and editing of the visibility of pictures and other objects on a worksheet.Continue »
WordArt is a quick and easy to use tool to allow Excel users to add pizzazz to the presentation of your worksheets.
This is a quick tutorial in the use of WordArt.
Here is a quick formula tip to start another awesome week.
Often while working with data, I need to split a number in to integer and decimal portions. Now, there are probably a ton of ways you can do this. But here are two formulas I use quite often and they work well.
Assuming the number is in cell A1,
- Integer part =INT(A1)
- Decimal part =MOD(A1,1)
These formulas work whenever my data has only positive numbers (which is the case 90% 0f time). But if I am dealing with a mix of positive and negative numbers, …Continue »
In many industries, bulk commodities are received or delivered in batches or parcels of various quantities and with various properties.
This post will look at one option for scheduling these commodities within Excel.
Introducing a method of allowing data points to be interactively highlighted in Excel Scatter / X-Y Charts and Line Charts.
You will see a lot of these style charts in various places where you want to highlight various aspects of the chart to your audience. It is a great technique for complex scientific and engineering charts where you may have hundreds or thousands of points.
SituationVLOOKUP is great for extracting information from a huge data table based on what you are looking for. But what if you need to extract more than one column of information? For eg. Lets say you have salesperson’s name in left most column, and monthly sales figures in next columns, one for each month. Now, you want to find the total sales made by a given sales person. How do you go about it? Read more to find how to solve this.Continue »
Once you start using Excel to develop systems, budget, forecast and large tables of data you may come across the dilemma of “How do I know this is right” or “How do I truth check this”.
This post will add a tool to your arsenal to help you out.
SituationNot always we want to lookup values based on one search parameter. For eg. Imagine you have data like below and you want to find how much sales Joseph made in January 2007 in North region for product “Fast car”? Read more to find how to solve this.Continue »
There is no argument that VLOOKUP is a beautiful & useful formula. But it suffers from one nagging limitation. It cannot go left.
Let me explain, Imagine you have data like below. Now, if you want to find-out who is the sales person who made $2,133 in sales, there is no way VLOOKUP can come to rescue. This is because, once you search a list using VLOOKUP, you can only return corresponding items from the column at right, not at left.
One easy fix would be move the sales data to the left of person name. But this is an annoying fix, because, god knows you may want to lookup based on profit values or something else in future. A better alternative is,…
Read more to find how to solve this.Continue »
I like to leave certain error messages in place because they can show you what your data is doing, but they look horrible when you print out reports. This will show you an alternative method for dealing with error messages.Continue »
Learn how to create a birthday reminder worksheet in excel in this video post. You can also download a template to keep track of upcoming birthdays and anniversaries.Continue »
Today I want to introduce a new excel feature to you, called as Picture link.
Well, picture links are not really new, they are called as camera snapshots in earlier versions. They provide a live snapshot of a range of cells to you in an image. So that you can move the image, resize it, position it wherever you want and when the source cells change, the picture gets updated, immediately.Continue »
Often, we need to input special symbols like €£¥©½» in to our Excel sheets. Now, how do we do that? Simple, you can use Insert > Symbol to add several different kinds of symbols. See this animation to understand how you can add symbols to an excel cell. (the file is kind of big, so […]Continue »
I have a happy news to share with you!!! For the first time since starting this website, we got 500,000 + page views in the month of September 2010. Half-a-million! Thank you so much. To put this in context, in Sept 2009, we got 245,000 views. Traffic grew by more than 100% in last 12 […]Continue »
We all have been there. You started to build a simple workbook to keep track of an ongoing project or dashboard or something equally complicated. Even before you realize the workbook has 23 sheets and 41 named ranges all going from one place to another, like flying spaghetti monster, only less awesome. Now, how do […]Continue »