Gee, I still prefer Microsoft Access.in reference to: http://chandoo.org/wp/2010/06/17/compare-2-lists-in-excel/ (view on Google Sidewiki)
Thursday, December 30, 2010
Chandoo says to Go To Special, Select Blanks and Ctrl+- to delete rows.in reference to: http://chandoo.org/wp/2010/01/26/delete-blank-rows-excel/ (view on Google Sidewiki)
Saturday, December 25, 2010
A support article at Microsoft - this is a quickref sheet. One point - remember that the zebra stripes are dynamic and managed by the table but un-managed and baked in after they are converted to a range.in reference to: Quick Reference Card - Use Excel tables to manage information - Microsoft Office (view on Google Sidewiki)
Saturday, December 4, 2010
The tutorial page.in reference to: http://office.microsoft.com/en-us/excel-help/get-to-know-excel-2010-create-formulas-RZ101862712.aspx (view on Google Sidewiki)
As mentioned at the Excel blogin reference to: http://blogs.office.com/b/microsoft-excel/archive/2010/11/16/switching-to-excel-2010-start-here.aspx (view on Google Sidewiki)
Wednesday, November 24, 2010
Wednesday, November 17, 2010
From the Excel Team Blog....in reference to: http://blogs.msdn.com/b/excel/archive/2010/11/16/switching-to-excel-2010-start-here.aspx (view on Google Sidewiki)
Monday, November 15, 2010
Tuesday, October 26, 2010
I don’t like Microsoft Access macros as much as I like VBA – I do a lot of work in VBA and know it well – although Access macros are useful in limited, simple tasks, anything that requires VBA expressions are not fun because you have to enter a VBA expression in a non VBA IDE.
Notice also, the editing environment is not conducive to using VBA Enumerations in the MSGBOX expression.
Note that Microsoft Access 2010 has enhanced the Macro Designer and added more Macro commands – you can create a macro in Access 2010 that will not survive running the same file under Access 2007. If you used VBA, you will probably need to re-compile to ensure VBA binary compatibility and keep on going.
In Excel 2007 forwards, lists that expand and contract needed OFFSET calculation. Now with the new Tables feature, we don't have to use this old method.in reference to: Dynamic Named Ranges That Expand and Contract Automatically in Your Excel Spreadsheet (view on Google Sidewiki)
Wednesday, October 20, 2010
Funny thing about Excel backgrounds – You’d think that
Page Layout > Background
would apply a background image to the worksheet. It does and it doesn’t. It does display the graphic on-screen but not when you print. I think.
If you want a washed out watermark, what you need to do is to
- go to Page Header (or Page Footer?) section boxes.
- Insert Picture
- Format Picture > Picture > (Image Control) > Color > Washout
Thursday, October 7, 2010
Saturday, October 2, 2010
Powerpivot can grab massive amounts of data for digestion...in reference to: http://blogs.msdn.com/b/excel/archive/2010/10/01/top-5-ways-powerpivot-helps-excel-pros.aspx (view on Google Sidewiki)
Friday, October 1, 2010
An MSDN articlein reference to: http://msdn.microsoft.com/en-us/library/aa140954%28office.10%29.aspx (view on Google Sidewiki)
And hooking VBA code to these eventsin reference to: http://msdn.microsoft.com/en-us/library/aa140279%28office.10%29.aspx (view on Google Sidewiki)
and creating a Multi-Step Wizard in Excelin reference to: http://msdn.microsoft.com/en-us/library/aa192538%28office.11%29.aspx (view on Google Sidewiki)
Haven't used .resize. Maybe I will...in reference to: http://msdn.microsoft.com/en-us/library/aa139976.aspx (view on Google Sidewiki)
Thursday, September 30, 2010
Picking up the thread, enhancements to the formula editorin reference to: Daily Dose of Excel » Blog Archive » Formula Editing Made Easier (view on Google Sidewiki)
Wednesday, September 29, 2010
Saturday, September 25, 2010
Looks like someone else other likes the good ol' Up Arrow that we used to have in classic Windows XP Explorer....in reference to: How to add a “go up to parent folder” button in explorer for Windows 7 and Vista » freewaregenius.com (view on Google Sidewiki)
Tuesday, September 21, 2010
A 6 parter (actually a 5 parter since 4 and 5 are one part, but who's counting?)...in reference to: Introduction to Financial Modeling - What is a Financial Model and how to build one? | Chandoo.org - Learn Microsoft Excel Online (view on Google Sidewiki)
Wednesday, September 8, 2010
PDF Migration Guides, Interactive Menu to Ribbon applets (Silverlight based) and Command Reference Excel workbooks herein reference to: Office 2010 Migration Guides - Excel - Microsoft Office (view on Google Sidewiki)
Saturday, September 4, 2010
A well written but dated book by Stephen Mulder - the Web Designer's Guide to Style Sheets - Hayden Press is no longer updated and the website has disappeared. Mulder wrote summaries for webmonkey - they're very readable.in reference to: Mulders Stylesheets Tutorial | Webmonkey | Wired.com (view on Google Sidewiki)
Tuesday, August 31, 2010
An article by Stephanie Krieger on creating Document Themesin reference to: Creating Document Themes with the Office Open XML Formats (view on Google Sidewiki)
Monday, August 30, 2010
Saturday, August 21, 2010
Friday, August 20, 2010
Thursday, August 19, 2010
Wednesday, August 18, 2010
I like Named Ranges in Excel. They free you from remembering to $A$1 every time you need to refer to an absolute block. And Named Ranges allow you to set up a reference block away from the worksheet that contains the Data Validation in-cell drop down list.
However, one more thing, Named Ranges still don't autoexpand to handle more items added later. Use ranges overlaid onto Lists (Excel 2003) or Tables (Excel 2007 and later). Or use OFFSET and COUNTA formula directly in the Name Manager specification.
Monday, August 16, 2010
Friday, August 13, 2010
Often, in Excel classes, participants ask about Excel functions and formulae. Of course, our job as trainers is to show and tell about functions specifically how to key them in, how to handle keying errors and recover from syntax errors. I tend to tell people that Excel has so many functions (and even more in the Excel 2010 version) that as generalists we don't have the domain intimacy with specific theory used in a function. Easily getting a list of function names in the catalogue is important. Walkenbach has written several Excel books and Excel function books. Here is a listing of the functions, with hyperlinks to the Microsoft Office website.in reference to: The Spreadsheet Page Excel Downloads: Excel 2007 Function List (view on Google Sidewiki)
Monday, August 9, 2010
Interesting. Remember when IT snobs say that Access is lightweight? Not big iron? How about when Access front ends to Windows Azure?in reference to: Access 2010 and SQL Azure - Microsoft Access - Site Home - MSDN Blogs (view on Google Sidewiki)
Saturday, July 24, 2010
Friday, July 23, 2010
Some inventive ways to use Excel conditional formatting and this does not even need the latest Excel version.in reference to: Conditional Formatting in Excel | Chandoo.org - Learn Microsoft Excel Online (view on Google Sidewiki)
Wednesday, July 21, 2010
Ryan McMinn and Dick Moffat show off uploading an Access database to Sharepointin reference to: Developing Access 2010 Hybrid Apps with Dick Moffat | The Access Show | Channel 9 (view on Google Sidewiki)
Monday, July 19, 2010
Saturday, July 17, 2010
It's been a while since I used the MSCAL calendar control. It's been dropped in favour of the Date and Time ActiveX pickerin reference to: Using the calendar control in Access - Access - Microsoft Office (view on Google Sidewiki)
Wednesday, July 14, 2010
Here’s a revised reading list for those who want a leg up to the world of web pages.
- The authoritative and definitive source of information for is the World Wide Web Consortium (otherwise known as W3C). They discuss What is HTML, XHTML and CSS and the draft HTML5 specification.
- Head over to W3Schools for tutorials on HTML, XHTML, HTML5, CSS.
- ILoveJackDaniels.COM has moved on to AddedBytes. There, will find cheatsheets for CSS, HTML (as well as regexp, PHP and Python.
- The Web Design Group is still around with .CHM (Windows Help) and HTML format desktop references for HTML4 and CSS
- Discussions of ems vs pixels at the Web Developer Forum and a cited W3C font Tip for Webmasters and Big Baer's CSS Font Size explained - see also Jon Tan's Incredible Em & Elastic Layouts with CSS and About.Com's Using Points, Pixels, Ems or % for CSS Font Sizes
- Why you shouldn't click here and other W3C Quality Tips
- Colours on the web are indexed. See Big Baer's HTML Named Colours Reference.
Friday, July 9, 2010
MSKB article on how to fix the viewing experience on .chm filesin reference to: Certain Web sites and HTML Help features may not work after you install security update 896358 or security update 890175 (view on Google Sidewiki)
- c:\refs is where I put the .chm files
- I used both the C:\ and the file protocol - I think the file protocol was the one that solved it.
Windows Registry Editor Version 5.00[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x][HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\HHRestrictions]"UrlAllowList"="E:\\refs;file:///E|/refs""EnableFrameNavigationInSafeMode"=dword:00000001"MaxAllowedZone"=dword:00000003[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HTMLHelp\1.x\ItssRestrictions]"UrlAllowList"="E:\\refs;file:///E|/refs""EnableFrameNavigationInSafeMode"=dword:00000001"MaxAllowedZone"=dword:00000003
Wednesday, July 7, 2010
Friday, July 2, 2010
There is a difference. VBA uses Banker'sin reference to: PRB: Round Function different in VBA 6 and Excel Spreadsheet (view on Google Sidewiki)
Microsoft Office Video on how to insert Sparklines in Excel 2010in reference to: Video: Insert sparklines to show data trends - Microsoft Office (view on Google Sidewiki)
Saturday, June 26, 2010
Dependency walker is an old tool but this article explains the diagnosis procedurein reference to: Analyze application failures the easier way with Dependency Walker (view on Google Sidewiki)
Monday, June 21, 2010
Friday, June 18, 2010
are back in 2010. They went missing in 2007. Yay!
And looks like macro recording of charts is now available / has been improved.
Saturday, June 5, 2010
Found this as a link from Allen Wyatt's website.in reference to: Managing and Administrating Building Blocks - Microsoft Word 2010 - Site Home - MSDN Blogs (view on Google Sidewiki)
Saturday, May 29, 2010
Friday, May 21, 2010
Advanced Microsof Office Documents 2007 Inside Out - maybe it will unravel the locked up knowledge on Themesin reference to: Advanced Microsoft® Office Documents 2007 Edition Inside Out (view on Google Sidewiki)
Stephanie Krieger does a show and tell.in reference to: http://mediadl.microsoft.com/mediadl/MSDN/O/Office/en-us/2007OfficeVH2s/odcofficeSK_UsingVbaToControlBuiltInCommands.wmv (view on Google Sidewiki)
Wednesday, May 19, 2010
This is a new feature. Check it out.in reference to: Microsoft Project 2010 : Project 2010: Introducing Inactive Tasks (view on Google Sidewiki)
Tuesday, May 18, 2010
Monday, May 17, 2010
Chandoo's got a concise summary of the major new features.in reference to: What is new in Microsoft Excel 2010? - Overview of Excel 2010 Features and Introduction | Chandoo.org - Learn Microsoft Excel Online (view on Google Sidewiki)
Wednesday, May 12, 2010
Although not linking to concise info, here is Microsoft's one stop webpage for Windows installsin reference to: Install, reinstall, and uninstall Windows - Help & How-to - Microsoft Windows (view on Google Sidewiki)
Tuesday, May 11, 2010
It's quite useful to do SUM with multiple criteria in reference to: Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online (view on Google Sidewiki)
Updated: 21st July 2010
Criteria driven results can also be arrived through use of Array Formulae. Mr. Excel writes about this on his CSE (Control+Shift+Enter) article.
Saturday, May 8, 2010
Friday, May 7, 2010
Thursday, May 6, 2010
I used to be a fan of Mike Lin's Clipomatic. Then my Windows XP became too crowded and my mult-clipboard programs stopped working properly. Now I am on Windows 7 but Clipomatic is sadly out of date and does not cope with the security settings to %Program Files%
Fortunately, ClipX has been updated, trying out the beta 7 x86 release.....
Wednesday, May 5, 2010
Tuesday, May 4, 2010
Saturday, May 1, 2010
It's difficult to figure out what lies after VBA when you are cool with VBA in Office and see that there are nice things you could do but they are in .NET. Aside from the skilling up and hoping that the .NET bits and pieces are installed on end users' machines, it's the case of what features that could be tempting. .NET is evolving at a rapid rate but not every feature is grokkable or even worth grokking to you.
Here are some videos.
Thursday, April 29, 2010
Microsoft Office applications have a speller - a really good one nowadays. But for those other systems that don't have a speller, try TinySpell - it doesn't curvy underline all your words but it does raise an pop up window when it detects you hitting a space after a word.in reference to: Impact (view on Google Sidewiki)
Friday, April 23, 2010
Conditional Formatting can be so conditional that you could write a full book on it. Here's an article from the Excel product teamin reference to: Microsoft Excel 2010 : Conditional Formatting Rules Simplified (view on Google Sidewiki)
Wednesday, April 21, 2010
Here's a quick guide to choosing a relevant Excel chartin reference to: How to Select Right Chart for your Data | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org (view on Google Sidewiki)
Interesting blogpost for new easy feature for photos in Powerpoiont 2010in reference to: Microsoft Office 2010 Engineering : More About Background Removal in Office 2010 (view on Google Sidewiki)
Monday, April 19, 2010
Friday, April 16, 2010
Thursday, April 1, 2010
"Another, more civilized, way of doing this is to open a Size & Position window from the View menu. In this window, you can see the size and position properties of the selected shape. You can then select several shapes, and update their width or height by typing the required value into the box. In order to make those shapes the same as the one you want, you have to select it first so that its properties are shown in the window. Ah, but there is another problem. You can't just hit enter, the edit box recognizes the fact that no change was made and does nothing, you have to retype the value again to have the desired effect. Still it's better than nothing"in reference to: On Everything And Nothing In Particular: Changing shape size in Microsoft Visio (view on Google Sidewiki)
Wednesday, March 31, 2010
Recipester has a fair number of visio video tutorials. Check it out.in reference to: Microsoft Visio : Tutorials, Troubleshooting, Tips and Tricks (view on Google Sidewiki)
For including in variables and Controls.in reference to: Building SQL Statements that Include Variables and Controls in Access 2007 (view on Google Sidewiki)
Thursday, March 25, 2010
It's an issue that to play with Sharepoint 2010, we need a 64bit Quad Core machine with minimum 4Gb of RAM. Sure, current technology and costs are low enough that this will not be an issue for a production server, but for a play/developer machine we just want to use any old thing near us.
In a rapidly evolving featureset though, the people at Microsoft are mapping Access functionality to Sharepoint data and Sharepoint workflows. A lot of current Office gurus who are not Sharepoint will groan and say "Who asked them for this?" but this is for me a great idea - Access is a great desktop tool and not a bad LAN networked tool. But to bring some of the familiarity of Access to Sharepoint is a win-win for both Access and Sharepoint camps.
Time will Tell.
Wednesday, March 24, 2010
I remember years ago, I took a Microsoft VB6 sample and constructed an Access 97 version of HTML Text email using the Web Browser control. I just had a look at it here:
By Gee, that was 2000, it's now 2010 - 10 years ago!
Anyway, Stephan Lebans added more zest to the project and in his HTML editor webpage, he has an example/demo file that pops up a form where you can edit rich text and most importantly embed a graphic.
The new Access 2007 and 2010 Rich Text formatted memo fields and Text Control can't embed a graphic. Or I haven't figured out how.
My next issue is how to use that content and send it via SMTP - you see, if you embed a local file reference with file:///, that won't go - you need to turn it to a cid reference, then send the graphic as well.....
Tuesday, March 23, 2010
Wednesday, March 17, 2010
I've often wanted datasets for Excel and Access demoes and tutorials. Australian Govt agencies have put them online. There are Public Internet Locations and Toilets - gotta woke on that xml import to Excel though - it's constipated.in reference to: http://data.australia.gov.au/414 (view on Google Sidewiki)
Finally Project embraces the Office Fuent UI and the Excel team's given them bonus UI behaviours in the grid.in reference to: http://www.microsoft.com/global/project/2010/en/us/RichMedia/secondary/video_showcase.html (view on Google Sidewiki)
Saturday, March 13, 2010
There are drastic changes in the Access Macro editor (no, not the VBA IDE) in Access 2010. Find out more...in reference to: http://channel9.msdn.com/shows/Access/Tutorials-Access-2010-Macro-Designer/ (view on Google Sidewiki)
Saturday, March 6, 2010
Purchase a qualifying Office 2007 product (with or without a new PC) from an authorized reseller between March 5, 2010 and September 30, 2010. See the eligibility requirements for more information.in reference to: http://office2010.microsoft.com/en-us/tech-guarantee/ (view on Google Sidewiki)
Saturday, February 20, 2010
Microsoft appears to have had a dilemma - Office, tracing its roots back to Word 1.0, Excel 4.0 (and even earlier) has always been COM based. The Microsoft nerd programming community embraced .NET framework ages ago and we're now looking at the pending Visual Studio 2010 and .NET framework 4.0 - that's 4 versions from when .NET started.
However, Office programs remain mired in COM based programming and retains VBA (VB6, the big brother bit the dust long ago). Over time, the Microsoft Office team (allegedly) have had to deal with mismanaged source code and specifications - features were incrementally tacked on to Office programs every year and a consolidated "big book of Office" is hard to come by.
Well, Office 2010 looks like another landmark. For the first time, there will be two mechanisms - the 32bit Office and the 64bit Office to take advantage of the bifurcation in Windows (started with Windows XP). I don't think the plumbing of Office really impacts the end user of Office - yes, there will be more capacity and so on, but fundamentally, if you type a few words into Word, Word should be transparently carry out the tasks, not confront you with what plumbing lies behind it.
So, Office 2o10 will have a 64 bit edition. Hold off those of you who grumble about the fact that "bug number 23748 has not been fixed and will it ever be fixed" - the aim of the game is to progress the plumbing since the plumbers are the ones who put it together.
Since Office 2010 will be available in 64 bit, it must now be obvious that VBA has to be 64 bit capable as well. Thus VBA7. And since they are tinkering with that, they must have added new features to VBA7 and tinkered with the incrementing Object Programming Model of eacg of the Office programs.
Office 2007 is impacting business with The Ribbon - that's the obvious aesthetic change. We're finally getting training assignments for Office for veteran users - Office since '95 has not had a visual change.
It does not dawn on many veteran users and IT Pros that the Office team has also implemented significant architectural change - the driving force was a new User Interface, new XML based file formats and an evolving stance on an integrated result document visual (better looking fonts, consolidated colours and styles, new shared charting and graphic objects engine).
So, Office 2007 has proven to be a landmark and a motivator for upgrading Office in the corporate offices. Looks like Office 2010 will up the ante - the UI may not change that much but there's going to be a lot of underlying plumbing changes. I wonder how many Excel macros will break? Get your free Office 2010 Beta (some priviledged users have the Release Candidate) to have an fore taste.
Oh, in case you haven't been playing, there is also the on-the-go options - Office Webapps and Office Mobility - but are they any relation to Office classic?
Monday, February 15, 2010
When we were using MS-DOS and later FAT, FAT32 on Windows up to 9x. Then Microsoft invented NTFS and although the utility is called CHKDSK, it really isn't DOS CHKDSK, it is a utility to diagnose and repair NTFS structures. Since NTFS is a complex structure, CHKDSK complexity has increased a lot - I thought it was me who was having issues with my Windows XP post SP3 machine with CHKDSK - after a run to fix problems, the whole volume would be irretrievably corrupted.
Now there is a Microsoft Knowledge Base article detailing its failings - and for me, it's not just pre-SP3, it can also happen post SP3
Tuesday, February 9, 2010
The Excel Solver Add-in is a mathematical tool supplied by Solver.com. The free version supplied with Excel is a one-time tool - to generate a bunch of scenarios, you can use VBA to supply the automation.
Obviously, you're advised to understand the fundamentals of the optimisation engine before you set it to work with real world figures.
Monday, February 8, 2010
The Pivot Table user interface has changed (again) in Excel 2007. The Pivot Table interactive screen elements and Word’s Mail Merge workflow are not in any way irritating. What is irritating is that Microsoft feels they need to evolve the user interface when they progress the Office version.
For those who love the classic way of dragging and dropping fields from horizontal to vertical (and vice versa), it can be puzzling that Excel 2007 forces use of the new vertical Task Pane. The good news is that you can still switch on that drag and drop behaviour of fields on the worksheet.
Thursday, January 28, 2010
Tuesday, January 19, 2010
Shauna's comprehensive article about using Styles and generating a Table of Contentsin reference to: http://www.shaunakelly.com/word/toc/CreateATOC.html (view on Google Sidewiki)
Saturday, January 9, 2010
Chandoo has a video on Youtube demonstrating this feature.in reference to: http://chandoo.org/wp/2010/01/08/powerpivot-review/ (view on Google Sidewiki)