How to do mathematically accurate Venn diagrams in Excel 2007

Question: How to do mathematically accurate Venn diagrams in Excel 2007 (as distinct from the ones in Smart Art)

Answer:  Widely discussed question on the web – there doesn’t appear to be a plug in for this

Looked in the Office Marketplace but couldn’t find anything close (The office marketplace also lacks decent search tools, so I might have missed it…)

There is an article on mathematically accurate Venn diagrams – which details building them up step by step at http://blog.data-miners.com/2008/10/creating-accurate-venn-diagrams-in.html

I haven’t tried it – but it looks like it would work.

Steven

Twitter Lists make Twitter manageable. Here are some I made earlier…

Folks

Twitter is an undervalued resource for leads to IT and Business resources. But as I am only able to review my Twitter account every couple of days, I felt I wasn’t getting value out of it, just seeing masses of posts. So I have set up some lists, which filter the accounts you follow by categories you create.  I know I am the last person to discover this feature, but really useful, all the same!

You might find them useful, so here they are: Continue reading “Twitter Lists make Twitter manageable. Here are some I made earlier…”

Excel Charts – Small and Large values on the same axis

This is a question that came up recently. My first thought was add a secondary axis, but in this case the small and large values were in the same series. I thought there might be something available as a third party add-in for Excel.

Solutions to Try

I did find an answer by Jon Peltier which looks good and gives step by step detail, but also appears to be a lot of work. This article provides links to alternative approaches at tushar-mehta.com and andypope.info – which has a zipped example to look at. If you are seriously  into graphs you should visit Jon Peltier’s blog. Continue reading “Excel Charts – Small and Large values on the same axis”

PowerPivot for Excel 2010 and SharePoint 2010

On Friday, I was catching up on my podcasts and listing to Hilton Giesenow‘s The MOSS Show and in Episode 34 Hilton interviews Pej Javaheri from the SharePoint B.I. team at Microsoft about Business Intelligence and SharePoint.

What got my attention was  PowerPivot which is an add-in for Excel or SharePoint that allows you to connect to large tables of data to do pivot table analysis.

PowerPivot Tools as seen on Excel 2010 ribbon
PowerPivot Tools as seen on Excel 2010 ribbon
PowerPivot Window Tools
Tools Shown in PowerPivot for Excel window

Continue reading “PowerPivot for Excel 2010 and SharePoint 2010”

VLOOKUP tip sheet and more

I am often recommending the Microsoft Excel Blog as a useful resource to intermediate and advanced excel users. They have some good VLOOKUP articles there, that are worth passing on: Continue reading “VLOOKUP tip sheet and more”

How do you get a sheet name to print in a Cell?

This question often comes up when I am showing how to link between sheets. Continue reading “How do you get a sheet name to print in a Cell?”

Excel resources for 77-602

General Advice

From my recent experience with another exam certification process I recommend:

  • Learn the software thoroughly
  • Poke around in the features you don’t use much
  • Learn the names of menu options and icons and the sequences to get to key tools
  • Don’t overlook the big picture – when and why  to use a particular tool or function
  • Use the exam objectives to identify your weak areas
  • Do as much hands on as you can to reinforce learning from courses and reading

Continue reading “Excel resources for 77-602”

Over 1000 views

My YouTube channel has had over 1000 views. I know movies of cute puppies will get  this many views will get that many views in 10 minutes… but its a big deal for me. So see Excel, Word, PowerPoint, Acrobat and other tips at http://www.youtube.com/user/TrainAScope

The True Value of Excel Training

A big part of what I do is Excel training particularly Intermediate, Advanced and custom courses. It is not unusual when you have just finished a demonstration or an exercise using a feature to hear a groan or a gasp from somewhere in the group, followed by comments like “I could have used this last week doing report x” or “This process will save me x hours every week” or “that will enable me to do a report I didn’t think we could do” .

As an aside, this is when you need the lesser known excel function =gobacktolastweek(1), where the argument or parameter is the number of weeks you need to go back in time, so you can use your new skill and save a bunch of time when you needed it. Continue reading “The True Value of Excel Training”

Blogs as a resource

Everything has a time management angle, finding useful information efficiently saves us time and helps us get things done faster. Corporate Blogs are a useful source of tips, articles, samples, templates and seminars. They also provide leads to other sources of information. Here’s a few of interest: Continue reading “Blogs as a resource”

Adding a Second Axis to an Excel Chart and Microsoft’s Excel Blog

Just a short post because the article I am linking to covers this topic so well. This is very much a frequently asked Excel question.

The article, Combining Chart Types, Adding a Second Axis is on the Microsoft’s own  Excel Blog. Continue reading “Adding a Second Axis to an Excel Chart and Microsoft’s Excel Blog”

Preserving Number formatting using Excel data in a Word Mail Merge

If you need to preserve number formats when merging data from an Excel list into a Word mail merge, the following articles will prove useful. I think for most purposes editing the merge field from { MERGEFIELD “Price” } to { MERGEFIELD “Price” \# $#,###.00 } will be the best way. This is discussed with other options in the first article.

Steven

Grouping by Dates in Pivot Tables Excel 2010/2007

How to summarise data in Pivot Tables using Dates. Very useful if you need to summarize point of sale data, maintenance or expense records by month, week or fortnight (14 days) Continue reading “Grouping by Dates in Pivot Tables Excel 2010/2007”