Articles from April 2012



Convert Excel records from columns to rows

Data can arrive to use in many formats and one of those is where the records are by column, not by the traditional row format.  Learn how to convert data from column to rows in Excel 2010…     Records by column When records are stored by column the Excel file will look something like [...]

Share

Named Ranges in Excel 2010

Learn how to create a descriptive name to refer to a range of cells on an Excel worksheet with this guide for Excel 2010 users…     What is a Named Range? A Named Range refers to creating a descriptive name to refer to a range of cells, by doing so the descriptive name can [...]

Share

How to calculate the number of working days between two dates in Excel 2010

Calculating the number of working days between 2 dates in Excel 2010 is a breeze when you know how, learn how with this guide…     Dates In a previous post we covered calculating the difference in days between two dates in Excel 2010 and now it’s time to look at a slightly different calculation, [...]

Share

How to display Excel numbers as millions M

Do you have an Excel dashboard or Excel report showing 1,560,472 in the total rather than 1.56M?  Get shot of those poorly formatted values, save some vital Excel dashboard space, and help your users understand the true meanings of your dashboard by learning custom formats… Overview A common question I get asked is how to [...]

Share

How to insert a header and footer in Excel 2010

This guide will show you how to insert a header and footer in Excel 2010 files.  It’s a simple trick to learn so read on and you will be able to insert a header and footer in Excel 2010 in no time at all…     What are Header’s and Footer’s? It’s worth explaining the [...]

Share

How to use Hyperlinks in Excel 2010

Hyperlinks are a useful tool to understand in Excel 2010, particularly in large reporting suites or Excel dashboards where they can be used as an effective navigation tool, find out how to make the most of hyperlinks in Excel 2010…     What is a Hyperlink A hyperlink takes the user directly to another piece [...]

Share

Excel – Fix cell with #VALUE Error

Learn how to fix cells that show #VALUE after calculation.  This guide explains why the #VALUE error occurs and will teach you how to prevent it occurring on your reports…     What causes the #VALUE Error The #VALUE error will appear as the result of a calculation within your Excel file, the cause is [...]

Share

How to make Excel macros run automatically on file open

Macros, whether recorded or written using VBA, are an increasing part of the Excel analysts toolkit. A useful trick to learn is to make your macros or scripts run automatically when the Excel file opens. Read on to find out how…     Situations There are lots of times where having a macro run automatically [...]

Share

Convert text case in Excel 2010

If you have ever been faced with text fields that contain poor formatting or capitalisation then you will find this guide on how to convert text fields to different cases a useful read…     Convert text case with Excel 2010 Data comes in many formats, sometimes the correct format; other times a seemingly random [...]

Share

How to copy data to another worksheet with Excel VBA

When writing VBA for automating report production the ability to copy and paste data from one worksheet to another is a valuable tool.  Learn how to do this with this how-to guide….     First Steps The first thing you need to decide is what range of data you wish to copy and from which [...]

Share