All articles in 'Excel Howtos' Category

Printing Excel Reports via a Word Document

Published on Feb 17, 2011 in Excel Howtos, Posts by Hui
Printing Excel Reports via a Word Document

Transferring data regularly between Excel and Word for the production of Word based forms or reports cab be tedious if it involves much more than a few numbers.
This post looks at a technique for automating this process.

Continue »

Quickly Compare Data using Row Differences

Published on Feb 14, 2011 in Excel Howtos
Quickly Compare Data using Row Differences

Lets say you have some data in 2 columns and you want to compare row by row to spot the differences. Of course you can write a formula or apply conditional formatting. But there is a quick and dirty solution that works just as fine.

Continue »

Are You Trendy ? (Part 3)

Are You Trendy ? (Part 3)

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 »

Are You Trendy? (Part 2)

Are You Trendy? (Part 2)

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.

Continue »

Worksheet Properties via a Menu

Published on Jan 25, 2011 in Excel Howtos, hacks, Huis, Posts by Hui
Worksheet Properties via a Menu

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.

Continue »

Are You Trendy ?

Are You Trendy ?

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.

Continue »

Find the Average of Closest 2 Numbers out of 3 [formula challenge]

Published on Jan 19, 2011 in Excel Howtos
Find the Average of Closest 2 Numbers out of 3 [formula challenge]

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 »

How to Embed Youtube videos in to Excel Workbooks?

Published on Jan 11, 2011 in Excel Howtos
How to Embed Youtube videos in to Excel Workbooks?

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 »

Using Array Formulas to check if a list is sorted.

Published on Jan 7, 2011 in Excel Howtos
Using Array Formulas to check if a list is sorted.

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 »

How to Filter Odd or Even Rows only? [Quick Tips]

Published on Jan 5, 2011 in Excel Howtos
How to Filter Odd or Even Rows only? [Quick Tips]

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 »

The Selection Pane

Published on Dec 2, 2010 in Excel Howtos, Huis, Learn Excel, Posts by Hui
The Selection Pane

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 »

Using WordArt in Excel

Published on Nov 25, 2010 in Excel Howtos, Posts by Hui
Using WordArt in Excel

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.

Continue »

Splitting a number into integer and decimal portions

Published on Nov 22, 2010 in Excel Howtos, Learn Excel
Splitting a number into integer and decimal portions

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 »

Scheduling Variable Feed Sources

Published on Nov 18, 2010 in Excel Howtos, Posts by Hui
Scheduling Variable Feed Sources

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.

Continue »

Highlighting Data Points in Scatter and Line Charts

Published on Nov 11, 2010 in Charts and Graphs, Excel Howtos, Posts by Hui
Highlighting Data Points in Scatter and Line Charts

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.

Continue »