All articles in 'Excel Howtos' Category
In the final post of the Are You Trendy? series we will look at the use of Excel Charts and associated Trendlines for trend analysis and I will give you a free tool (Normally valued at $200, I wish) which will allow interactive assessment of a Charts Trendline Y value for any X value.Continue »
Does your data hold hidden secrets?
In Part 2 of Are You Trendy? we will examine the Excel functions that assist us in Trend Analysis.
The Properties button on the Developer Tab in Excel 2007/10 can be used to access a number of Worksheet properties normally only available through VBA and even a few which aren’t available through VBA.
Lets take them for a spin.
Often you may have a set of data and need to know what an intermediate or future value of that data may be.
This week we will investigate 3 methods of tackling this problem using Excel.
In this post we’ll look at manual forecasting.
Today I am asking you a tricky formula question. This is asked by Ionel on the Introduction to VLOOKUP, OFFSET & MATCH Formulas post. The question is, I have data in three columns: A,B,C and I want to get the average of the closest two values out of three in each row. Could you help […]Continue »
Often, while creating a complex model or dashboard, you may want to include additional training material in the workbook. So let us learn how to embed flash movies, Youtube videos etc. in to Excel workbooks.
To Embed Flash Movies, Youtube Videos in to Excel, follow these steps.Continue »
Today, we will learn an interesting array formula trick to test if a list is sorted or not. During last one week, I got 2 requests from different clients for some excel related work. Both of them had one thing in common. To test whether a list is sorted or not. So I got thinking, […]Continue »
Ashish sends out this SOS thru email, “I need your help in putting filters . Can we filter the cells on the basis of their even or odd character. i.e in the table of 1-1000, i wish to filter, 1,3,5,7,…”
An odd request, I must say. But nevertheless, possible in Excel.
Read this quick tip, If you want know how to filter odd or even rows only in a list of values.Continue »
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.