it's been (ahem) a while ....

It's been ahem a while, I am now full time with Activia training so I don't have chance to publish anything on my Blogsite, more is the pity. anyway go visit us on www.activia.co.uk

Jamie J

Shortcuts to copy a filtered or sub totalled selection of data

Once you have setup a Subtotal sheet you are ready to capture the data you are looking at using copy but it copies ALL the data not just the results from your subtotal.

In Excel 2007 and 2010 its hard to find a way to copy visible data only

Use the QAT menu dropdown to customise the menu and find the command called SELECT Visible Cells, add it to your toolbar.

Or use the shortcut below:

ALT +; to select the data then  CTRL + C then go to Sheet 2 then CTRL + V to paste.

Blogging from Windows Live Writer

Using Live writer to create a blog post.

 

Adding pictures is a breeze !

Office 365 / 13 is here for preview install

http://www.microsoft.com/office/preview/en

Based on Office 2010 but Cloud integrated its Microsoft's new toy for users.

Install is quick and easy so go for it and see what it can do for you !!

J

Windows 8 Release Preview is here to download

http://windows.microsoft.com/en-US/windows-8/download/

GULP over 3GB for the 64 bit version, I hope you have fast broadband, I get Infinity on the 17th Aug.

Experts Exchange

http://www.experts-exchange.com/

Have a look at this useful website , experts exchange ideas and post solutions.

wow 10,273 page views, thanks everyone !!

Jamie

Catch me on LinkedIn

Hi folks,
You can keep up with me on LinkedIn also follow the link below:

http://uk.linkedin.com/in/jjsurf

wikiversity .. a place to learn for free and its fun

http://en.wikiversity.org/wiki/Introduction_to_Programming/About_Programming

this link takes you to page 1 of the programming course on wikiversity

That pipe key defined

http://en.wikipedia.org/wiki/Vertical_bar

Rarely used but much loved the pipe key, I use it in Excel to reveal formulas with CTRL + `

press again to reveal data.

thanks
J

donate for wiki

https://wikimediafoundation.org/w/index.php?title=L11_1114_WMUK_Jimmy/GB&utm_source=B11_1114_Foundation_WMUK_Jimmy&utm_medium=sitenotice&utm_campaign=C11_1114_WMUK&language=en&uselang=en&country=GB&referrer=http%3A%2F%2Fen.wikipedia.org%2Fw%2Findex.php%3Ftitle%3DSpecial%253ASearch%26search%3Dpipe%2Bkey


the above gibberish takes you to a link to donate to wikipedia, please consider it to maintain a great service.
J

The # key and its history

http://en.wikipedia.org/wiki/Number_sign

Did you know some people call it the pound key ? also the Octothorp ????

weird !

:)
J

Whats that key for again ??

http://www.computerhope.com/keys.htm

the above link simply explains what all the keys on your keyboard are called and what they typically do for you !

Simples

JJ

Grr how do you show Percentage Changes

This is not an obvious one but I was aksed how can I show a percentage CHANGE between two values,
eg.
Sales in 2009 were 75K and by 2010 had risen to 125K what is the % change ?

The Formula you can use is as below:

Thus returning a value of 67%

nice one !!!

JJ

Charts and MORE Charts

http://processtrends.com/downloads.htm

Take a look at the above link for lots of specialist Chart types not normally available in Excel.

thanks

JJ

Another IF ISERROR example

IF ISERROR and VLOOKUPs

=IF(ISERROR(VLOOKUP(E6,CostTable,2,FALSE)*E6),0,VLOOKUP(E6,CostTable,2,FALSE)*E6)

use the above to solve your Excel 2003 Vlookup error requirements, this enables SUM calcs to still give you an answer  and replaces error cells with zeros.

J

dynamic searching with VLOOKUPS, MATCH and INDEX

http://office.microsoft.com/en-us/excel-help/dynamic-searching-using-vlookup-match-and-index-HA001154902.aspx

IF ISERROR VLOOKUP

http://www.exceltip.com/images/screenshots/810.gif

Another problem was how to use an IF ISERROR combo in Excel 2003 as this was much simplified
in Excel 2007/10 with the IFERROR Function.

see the screen shot above for a solution.

JJ

Waterfall Charts, wet your appetite !

A recent question about waterfall charts in Excel got me thinking "How do they do that ?"
so after a little searching here is a great link to a fix for it in Excel.

http://chandoo.org/wp/2009/08/10/excel-waterfall-charts/

PS
Download the template to play with it for yourself.

Jamie