Posts Tagged ‘ excel ’

Excel Pivot Table or CrossTab to Flat List

July 12, 2010
By admin
Excel Pivot Table or CrossTab to Flat List

I often find myself struggling to deal with data that has been rendered almost unusable by the data provider who has converted it to a cross tab format. Usually they think they are helping and have probably started with a nice flat list then spent ages formatting it so the data spreads out across...
Read more »

Tags: , , , , , , ,
Posted in Excel, Geeky Corner | 2 Comments »

Excel CrossTab Table to Flat List

July 29, 2009
By admin
Excel CrossTab Table to Flat List

I had a query on my sumproduct tutorial that was effectively asking if I could work backwards from an excel crosstab or data table to get back to the simple flat list. Converting a table like this: Back to the data sheet like this: If you didn’t have to do this sort of task...
Read more »

Tags: , , , , , , ,
Posted in Excel, Geeky Corner | No Comments »

Macros to Consolidate Worksheets

July 24, 2009
By admin

I’ve got a spreadsheet with tabs for each individual directorate. Within the tabs are tables with the same layout but different sizes (number of rows). I want to group together all the used data on each tab and create a single consolidated sheet of continuous data. Obviously I need a macro to copy and...
Read more »

Tags: , , , , , ,
Posted in Excel, Geeky Corner | 1 Comment »

Vlookup Errors – The Third Way

April 20, 2009
By admin

I’ve just published a post detailing a method of removing the errors returned by vlookup or hlookup utilising conditional formatting. I had previously been using the double vlookup method to replace errors with blanks or zeros but then discovered that these excessive lookup functions had a tendency to bloat my spreadsheets. While the conditional...
Read more »

Tags: , , , , ,
Posted in Excel, Geeky Corner | No Comments »

Spreadsheet Efficiency: VLookup and Conditional Formatting to Remove Errors

April 20, 2009
By admin
Spreadsheet Efficiency: VLookup and Conditional Formatting to Remove Errors

I’ve started working at a new hospital and it seems to be plagued by unwieldy spreadsheets that have a tendency to crawl through saves and then sometimes refuse to open. They are riddled with links and lookups to external spreadsheets and the whole thing feels fairly precarious. I’ve been reading a bit about good...
Read more »

Tags: , , , , , ,
Posted in Excel, Geeky Corner | 2 Comments »

Adding an Image to the Header or Footer of Every Sheet

January 11, 2008
By admin

From Excel 2002 onwards you can enter an image, probably a logo, into the header or footer of your worksheet. This code enables you to create a macro that will automate the addition of the logo to every sheet in your workbook. Sub MultipleHeader() ‘created 10/01/2008 by Angela Wolff Application.ScreenUpdating = False ‘ stops...
Read more »

Tags: , , , , , ,
Posted in Excel, Geeky Corner | No Comments »

Scroll Bar Chart

October 17, 2007
By admin
Scroll Bar Chart

This is a very neat advanced Microsoft Excel technique that enables the spreadsheet user to alter the visible range displayed. This can be achieved by defining the start point within the spreadsheet data series and also the number of data points to display in the Excel spreadsheet chart. All the examples are available to...
Read more »

Tags: , , , , , , ,
Posted in Excel, Geeky Corner | 1 Comment »

Dynamic Chart

October 16, 2007
By admin
Dynamic Chart

This Microsoft Excel tutorial, shows an example of how to create a dynamic chart by moving the charting range as data is added to the spreadsheet. Here we start with monthly data for Retailer A, showing Petcare sales complete up to July. A standard Excel line chart of this data would be shown thus:...
Read more »

Tags: , , ,
Posted in Excel, Geeky Corner | No Comments »

Sumproduct

September 27, 2007
By admin
Sumproduct

All the worksheets (and some extras) shown in the microsoft excel tutorial below are included in the example spreadsheet, available to download Sumproduct.xls This is quite a special excel spreadsheet function but the online help available from microsoft is unfortunately pretty poor. It works as an advanced form of the SUMIF formula where you...
Read more »

Tags: , , ,
Posted in Excel, Geeky Corner | 2 Comments »

Named Ranges and the SUMIF Function

September 26, 2007
By admin
Named Ranges and the SUMIF Function

Named ranges are useful in Excel as their use can make formulas much more understandable. You can name: areas of the spreadsheet, such as your data columns and rows or individual cells that may act as constants, for example the VAT value I’ve used the following named ranges in the SUMIF example below: As...
Read more »

Tags: , , , ,
Posted in Excel, Geeky Corner | No Comments »

flickr rss

IMG_0774Diktamos GorgeIMG_3222IMG_3220IMG_3207IMG_3205

What I'm Doing...

Posting tweet...

Ads



Reading List