Wednesday, December 25, 2013

Making formulae easier to follow in Excel

In the old days, we would write really long formulae with nested functions. Pre Excel 5.0 and in other products, we did not have the niceties of VBA as a supplementary classic programming language so we used to torture ourselves.

Fast forward to 2013 and Excel generations 2007 and later have niceties in display and useage that reduce the pain of long formulae. Courtesy of +Bill Jelen’s discussions on social media, we know that we can use:
  • you can use spaces in the formula. Instead of typing A1+A2, you can type A1 + A2 chunking out the formula and improving readability. You can’t put a leading space before the first = sign. You can’t put a space between the function name and the bracket e.g. SUM( is ok, SUM ( is not
  • you can use Alt+Enter to insert line breaks in the formula bar. Remember that in Excel 2007 onwards, you can pull down the floor of the formula bar, increasing the number of lines Excel will display. This allows more classic programming language display.
  • you can use the Name Box when you build the formula in the Function Builder. We know that. However, did you know that you can use the Function Builder repeatedly in a formula with nested functions?

Tuesday, December 17, 2013

Screen Capture in the Windows 8

There are several options to screen capture classic desktop (programs):

  • PrtScr to save to clipboard
  • the Windows Snipping Tool
  • Microsoft Office 2010 (and later) programs have an Ribbon Command – Insert > Screen Shot > Screen Clipping
  • Greenshot – a very nifty free utility that can capture, save to file automatically, upload to various photo gallery sites automatically
  • the Windows Problem Steps Recorder (records a screenshot every keystroke sequence or mouse action you carry out until you stop.
  • and a host of third party free and paid utilities

However, the Windows 8 Apps Tiles Layer breaks your workflow – If you use one of the utilities above, after the screenshot, Windows returns to the classic Desktop. If you return to the Tiles layer, some Tile Apps lose state.

Use Windows logo key + PrtScr and the screenshot will save to My Pictures/Screenshots as a PNG file automatically, without switching to the Classic Desktop.

Friday, December 13, 2013

Free Intro to VBA Excel

I often look for reference material for people intending to pick up Visual Basic for Applications (VBA) and program Microsoft Excel. Just today, encountered this compilation by Hooman Askari-Nasab

Enjoy!

Tuesday, November 12, 2013

HTML & CSS Resources

I sometimes still get involved with beginners to the web. Yes, nearly everyone knows how to Facebook, buy from eBay or Google for this and that but this is with the consumer hat on – fewer people have a basic knowledge of the atomic elements that make up a webpage. Here are some leads.
W3Schools has been there for a long time. It is still well presented reference and there are some areas where you can edit some HTML interactively and see the result.
Shay Howe’s Beginner’s Guide and Advanced Guide covers significant points.
Learn CSS Layout is a deconstructive tutorial.
jQuery from the source.
Adobe Dreamweaver reference and Help center
Scan your webpage and produce several screenshots with different browsers using BrowserStack

Microsoft Office on the Mac

Ok, Ok, I’m pure Microsoft Windows user. Been that since I Windows was born. I am forced to use a Mac very rarely and haven’t hit the comfort zone in this environment. But participants at classes sometimes ask

But where is the Ctrl key on the Mac?
https://exceljet.net/keyboard-shortcuts

How do I Right Mouse Click?
http://www.wikihow.com/Right-Click-on-a-Macbook

Where are the function keys?
https://exceljet.net/blog/how-to-use-mac-function-keys-with-excel

Thursday, October 31, 2013

Working in the Outline View in Microsoft Word

It is sad that Microsoft has sought to de-emphasise Outline View in Microsoft Word 2013 – it may be that they are optimising the user interface of these programs for Windows 8 touch tablets (Surface, Surface Pro and so on).

For putting structured thoughts to paper (think academic Thesis, comprehensive proposals and arguments), outlining is a very powerful tool and closely related to Buzan Mind Maps.

Since you are thinking out, you don’t want paper layout, fonts, or how it appears (presentation with diagrams and so on) to get in the way, you just want to write and re-organise.

Switching Views

Microsoft Knowledgebase 290938 has the following recommendations:

  • Alt+Ctrl+P for Print Layout View
  • Alt+Ctrl+O for Outline View
  • Alt+Ctrl+N for Draft View

ShortcutWorld has a visually pleasant list of keystrokes.

Whilst in Outline View

  • Ctrl+Tab to insert a Tab Character when in Outline View (since the Tab and Shift+Tab combination is busy)
  • Numeric keypad Slash (/) to toggle display of character formatting

Dummies.com has a short and sweet list of Keyboard Shortcuts by Dan Gookin.
Tip: Ctrl+Shift+N to force the current paragraph to Normal Style

Don’t forget to visit Word MVP FAQ for some experienced insight tips.

Saturday, October 26, 2013

What's happened to Draft Mode, Ma?

I've had Office 2013 for a while, just not taken it for a drive much, the most common Office versions I encounter in corporate and educational training is 2010. I also use Office 2007 in a small business with a mixed Office environment. So I finally get to install Windows 8.1 and Office 2013, fresh.

Now, typing in Word 2013, I realise that the bar of mini icons on the bottom right of the Word window is missing the Draft Mode icon and the Outline Mode icon. They're still there in the View Tab of the Ribbon, but missing from the easy access mini icons.



Why make a fuss? Outline Mode is crucial to brain storming and re-organising the document by structure, not by pages. You can now do some of that organisation using the Document Map Pane on the far left but Outline View has lots of usability like the Style Pane.

Draft Mode frees your mind from concentrating on page formatting and margins, giving a clean screen. You don't obsess about word wrap line endings and nitty gritty formatting detail. Alternatively, for structural formatting, you can see the Style Pane, Hard and Soft Page Breaks without nausea caused by vertical jiggling.

So, you'll have to use the View Tab or add them to your Quick Access Bar

Monday, September 30, 2013

Multiple Consolidation Ranges in Excel

So I was watching this +Bill Jelen video and noticed how he was normalizing data - his data was not normalized and he used a Mike Alexander technique - he fed the data into a Pivot Table and then used the Pivot Table to generate an extract, thus normalizing the data in a new sheet. Have a read of the background Microsoft documentation on Multiple Consolidation Ranges.



Citing the full pathname of the document file

Wednesday, September 11, 2013

Focus on producing Combo Boxes

Of all the Form Controls, the one that users get excited about is the Combo Box. I've taught the technique for simple combo boxes over and over again but haven't documented it formally. In the meantime, here are some reading resources:

Wednesday, August 28, 2013

Excel 2013 and Power Pivot

Getting Power Pivot

Power Pivot was introduced as a free, downloadable add-in for Microsoft Excel in version 2010. In Excel 2013, the way to get Power Pivot became complex, difficult and murky. This has improved. +Bill Jelen details in this video, how you can buy or subscribe to an Excel licence that has Power Pivot


But why would you want Power Pivot? What does it do? It brings the relational model to Excel.




Saturday, August 17, 2013

Microsoft Access 2013 - No Can Does

Microsoft Office 2013 has been a sleeper version. Whilst Office 2007 created an impact by implementing the Office Ribbon User Interface and Office 2010 has become the version that corporates, educational institutions and governments embraced, Office 2013 (or Office 365) has been relatively quiet. So quiet that I have not encountered clients who run it or want to discuss version specific functionality. So Office 2013 has been sleeping on my machines.

In my first runs with Access 2013, I encountered the first and major no can do - it would not open the famous Access 2000 vintage NWIND.MDB that I have used for years to introduce, demonstrate and train people. Documentation on the web gives even more no can do-es. So, take a seat, maybe a stiff drink and let's see what Access 2013 does not support any more.

Access 2013 does not open or import the classic Access .mdb.

  • Access 2010 and 2007 supported .mdb well - even those secured by Workgroup Security. And all previous Access versions would import older versions of Access database files. Not this one.  You need to convert the .mdb to .accdb in Access 2010 or 2007.
  • In Microsoft's words: "the Jet 3.x IISAM driver is no longer available." The last version of Jet 3.x was 3.6 that I encountered with Access 2003.

Access 2013 does not create web databases (.accdb stored on Sharepoint Server)

Well, that was a short life. Access 2010 introduced the idea of creating Access databases that could be deployed to Microsoft Sharepoint servers and interacted with using a web browser. Microsoft has abandoned that idea. 

Access 2013 does not support .adp files

.ADP files - Access Data Project files were introduced in Access 2000. At that time, the idea was that Microsoft Access had a successful and admired Forms and Reports facilities - the Tables and Queries too, were not half bad in terms of user interface. So they removed the native Access JET engine and glued on a thin framework to use the Access screens with the SQL Server back end. Unfortunately everyone realised that more than half the beauty of Access was the intelligence and close integration of JET with the Access screens - without JET, .ADP files were weak in robustness and power. Microsoft has been walking away from that idea since Access 2003, so no tears to be shed.

Access 2013 does not support PivotCharts and PivotTables natively

When I first saw PivotTables grafted into Access 2003, I was quite excited, having seen the success of PivotTables in Excel over several generations. Unfortunately, PivotTables didn't "fly" - they were sluggish in performance, they didn't behave or produce results like genuine Excel PivotTables did and I never used that feature. No loss.

Access 2013 does not support dBase .dbf files

This feature is not regretted except for nostalgia. When Access 1.0 was born, the Number One desktop database was Ashton Tate, later Borland dBase III and dBase IV. dBase was a veteran product famous from the era of dBase II on early CPM-80 computers (even Apple ][s) and ushered the age of relational or pseudo relational databases onto personal desktop use. From those birth days, dBase market share plunged as it failed to dominate and excel in Microsoft Windows (dBase was extra premium cost, Access was bundled free in the Office Pro Suite). Of course in those days, Access had to be able to import from .dbf files but dBase as a commonly used program disappeared years ago.

Access 2013 does not support Data Collection teamwork with Microsoft Outlook

In Access 2003 or earlier, Microsoft had the idea of having an Access database, then using Microsoft Outlook to send out the data entry form, the recipient would key in one record and respond by replying to the sender. The sender's Outlook would detect the response and working with Access, route the data into the Access database. A sweet idea of hop-skip-and-jump and not needing corporate IT support for this action. Except that not the world is dominated by the internet and people not using Outlook to send or respond. I took this for training and demo but did not see it used in production. 

Access 2013 does not support Database Replication

In the time of Access 97, the PCs were generally standalone and disconnected. Web apps were not as common. As a hop-skip-and-jump idea, Microsoft designed quite a sophisticated replication system so that disconnected PCs could accept data entry and then the files could be exchanged and merged. The level of complexity in the idea and in the implementation defeated users.

Access 2013 does not support Source Code Control

Some corporates and third party vendors did use Microsoft Access for multi-person team programming. With the proliferation of different programming tools, .NET apps, web apps, it is likely that this feature may not be missed.

Access 2013 does not have an SQL Server Upsizing Wizard

Access 97 or earlier offered an SQL Server Upsizing Wizard to help people migrated tables and queries to Microsoft SQL Server. A good idea but of course the differences between Access and SQL Server are large enough to require quite a bit of enrichment of migration features. Very quickly, third party migration utilities outstripped this wizard in ability.

Access 2013 does not have a Package Deployment Wizard

Once people got used to Access as a productive, useful system, there were often requests to have a one button installer that could install Access runtime and place the Access datafiles on the client's PC. This request might still surface, the way forward is to simply install Access runtime separately, place the Access datafile and create shortcuts to run the system.



Thursday, August 1, 2013

Outlook 2007 (and later) HTML and CSS rendering

In Microsoft Office 2007 and later, Microsoft decoupled Internet Explorer from Microsoft Office - thus the rendering engine for HTML/CSS email is Microsoft Word. Of course, Word isn't a HTML editor so here's an article on what it does (or does not do)

Saturday, July 20, 2013

The History of Excel

+Bill Jelen just highlighted this video of the history of Excel on Google+



Memories

It brings back memories of the names we have heard about. The famous Charles Simonyi (who also gave us the Hungarian Notation), names like Jeff Raikes, Jon Devaan.

Multiplan  wasn't just "a" spreadsheet. It was "the" spreadsheet for Microsoft. I experienced it on CP/M and MS-DOS. I didn't like it because I have this dyslexic type issue with Row 1 Column 1 - I keep getting my rows and columns mixed up and I guess most of the world too. So the Visicalc / Supercalc / Lotus 1-2-3 / Symphony family became much more popular because that family uses the A1 notation - the same notation that maps often use (like Melbourne's Melways).

But I had a healthy respect for it. On 64k RAM machines, it pioneered the idea of multiple file Consolidation - a feature that Excel carries to this day. And that R1C1 notation is a strong part of the Excel Object programming model. You can refer to a cell as Range("A1") or you can refer to a cell as Cells(1,1). In programming rather than hands on use, the ability to interpose Cells(x,y) where x and y are index variables that can be iterated in an array, is so much more preferable than thinking  FOR X=1 TO what number is AZ ?

For those who never experienced the Lotus 1-2-3 Macro language, it was at first, more approachable because it copied keystrokes exactly and directly - there was no mouse then, no other program running side by side to distract the program. We used to dream in /RE~

But VBA + Excel Object Model + DAO (Data Access Objects) are so much more powerful, detailed and clearer to understand and write once you get it

The Office Suite

Jeff Raikes says that proposing that Microsoft sell Word + Excel + Powerpoint + Access as a suite of programs was real brave. To me in those days, it was a no-brainer. Because the genre of an integrated single program with multiple facets had run its course. Remember Lotus Symphony (which was a spreadsheet that tried to be a single table database, wordprocessor, chartist, console window, presenter), Context MBA (which seemed to only live on HP computers), Ashton Tate Framework (I actually produced a dot matrix Company report) ? Or Microsoft Works? Nah, the better way was to just package separate specialised programs in a sales package rather than a technology solution.

Run Everywhere

Charles Simonyi said that Multiplan was the most portable program they had. It ran everywhere. So Microsoft used their end user product to encourage their compiler product teams to produce executable binaries on many platforms.  Come back to the present. Office desktop runs on Windows 8 Intel. Office RT is not the full suite of products and runs on ARM processors on Windows 8 RT. Office Web Apps run on Skydrive in the cloud. But none of the single products like Excel, actually have all the features that Office desktop has. Maybe that's what's wrong with Microsoft today - they don't have the magic to make things work.

Thursday, July 18, 2013

The Lorem Ipsum Generators

It's a quiet secret that if you type =RAND() as the first letters in a Word document, Microsoft Word generates some ipsum for you. I often use this when I run training classes on Word. Sometimes I need that for other programs as well.

Thanks to +Adrian Tritschler who highlighted several other web based generators:

http://www.cipsum.com/

Collaboratively administrate empowered markets via plug-and-play networks. Dynamically procrastinate B2C users after installed base benefits. Dramatically visualize customer directed convergence without revolutionary ROI.

http://cbsg.sourceforge.net/cgi-bin/live


The resource quickly synergizes win-win solutions, relative to our peers, while pursuing this route will enable us to broaden our enterprise-wide, future-oriented and dynamic executions.


Tuesday, July 16, 2013

Playing with Excel 2013

In a recent discussion with +Bill Jelen  (Mr. Excel himself), we drew the impression that Excel 2013 is not flooding the market place like Excel 2007 and even more dominantly Excel 2010. I've had Excel 2013 for a long while but haven't used it in production because my clients are running either Excel 2007 or Excel 2010. So tonight, testing out some training material (written for Excel 2010), I came across these quirks in Excel.


  • Excel 2013 uses a Single Document Interface (SDI). Each workbook opens in a separate window. This is long overdue because a fair number of power users are on two screens and Excel pre 2013 loads all the workbooks into the same window unless you run Excel empty as another instance and then use File . Open to load the next workbook you want to use. So SDI is good. However, it is said to give macros that switch workbooks some issues.
  • Excel 2013 has an Quick Analyze pop up icon at the bottom right corner of a dense block of cells. This offers options to carry out charting, creation of tables, pivot tables, conditional formatting, add subtotal row. However, it seems to automatically choose the columns in your block of cells to carry out this operation. I can't yet see the usefulness in that.
  • Excel 2013 seems to have lost the tin stubs at the end of the scroll bars. I like using them to split a window into panes. The Command Ribbon . Views Tab still has the Split Window icon.
  • For the new generation Excel, 2007 and later, creation of a table in the workbook blocks the use of the Views . Custom Views feature.
  • You can use Custom Lists for Advanced Sorting in Excel 2007 and later. But Custom Lists are stored in the specific Windows registry of the machine (MSKBID 213796). You can produce lists on a worksheet, then run a macro to take those lists and push them into the registry of another machine.
       Application.AddCustomList ListArray:=Sheets("Sheet1").Range("A1:A50")


More as I encounter

Friday, June 21, 2013

Getting your files to and from Cloud Storage

Microsoft Skydrive

Microsoft Skydrive implements a file transfer interface that is neither WebDAV nor ftp. So the usual WebDAV and ftp clients like CyberDuck won't work.

Microsoft Office 2013

Microsoft Office 2013 client programs on Windows 8 can file open / save directly to Skydrive. In the File Save and File Open dialogs, you can nominate one or more Skydrive accounts to connect to. There is an app called Skydrive Pro but this syncs between your PC and Sharepoint.

Windows 8

Windows 8 has a Tile App called Skydrive. However, this app is locked to your Windows 8 username / account which may not be your preferred Skydrive account. That is, you might have more than one account and your Windows 8 account on Skydrive may not be the one you want to reach.

Internet Explorer / Web Browser

Using your Web Browser, you can connect to any of your Skydrive accounts and upload / download files. However unlike Google's Drive via Google Chrome browser, you cannot upload a folder - you have to create each folder and individually tag each of the files in a bulk file upload.

SD Explorer

SD Explorer is a third party program that extends Windows Explorer - your Skydrive folders and files virtually appear, cached. You can drag and drop local files to and from your Skydrive content.

Google Drive

Web Browser

Use Google Chrome Browser. The other web browsers suffer handicaps. Using the web browser interface you can upload and download files and folders. Multiple files on Google Drive will be downloaded as a single compressed .zip file At the time of upload be careful to nominate that the files not to be converted to Google Docs format if you want to preserve the Microsoft Office refinements. Documents converted to Google Docs format do not count to your Google Drive quota limits, are editable by Google Docs in the web browser however.

Cyberduck

Cyberduck is a third party Windows or Mac desktop client that offers FTP, WebDAV and Google Drive connectivity.

Google Drive desktop app

The Google Drive desktop app for Windows will synchronise a local folder nest that you nominate with the Google Drive. It runs in the background and appears as an icon on the system tray.

Wednesday, June 5, 2013

Jensen Harris and the Windows 8.1 Preview

I've often wondered what Jensen Harris looks like - he's the one behind the Office 2007 and later Ribbon and the Modern UI tiles in Microsoft Phone, Tablet and Windows 8. Here he is.

Friday, May 17, 2013

Fix: Powerpoint 2010 / 2013 doesn't like video embed code

Issue

Powerpoint 2010 or 2013 allow you to run a YouTube video from the web, embedded in your slide show presentation. This avoids the need to switch from Powerpoint to your web browser, play the video and switch back. There's a catch however. Recent changes in the embed code that YouTube provides are no longer digestible by the Insert > Video dialog offered by Powerpoint.

Workaround

  1. Use the Old Embed Code from YouTube.
  2. Remove the Version=3 portion of this code (occurs twice in the code) before pasting into the Insert > Video > Online Video textbox. You can edit the code in a text editor like Windows Notepad or Wordpad if you need an editor.
This information is derived from PPT Alchemy and a visual walkthrough is on this YouTube video:

Video by Ron Bosch

Monday, April 22, 2013

Chart This!

Thanks to +Mike Elgan for drawing attention to the Mother Jones Chart.

http://www.motherjones.com/mojo/2013/02/chart-generational-attitudes-sushi-gay-marriage
Generational Attitudes on Sushi and Gay Marriage Correlate Almost Perfectly
Some of the data is drawn from Public Policy Polling.

Now, that Public Policy Polling page: More Food Issues That Divide America contains simple stats that would thrill any Intro to Charting with Excel 101 class.

Wednesday, April 3, 2013

Excel's poorly documented Conditional Formatting

Nearly every class I lead, I say that one could write several chapters on conditional formatting (maybe +Contextures Inc. or +John Walkenbach or +Helen Bradley  might. Chapters that have detailed examples, screenshots and so on.

One thing that has been puzzling me is how to use the very pretty and eye catching effectiveness of conditional formatting with floating reference cells. - It's obvious how to conditionally colour a range against a fixed number (e.g. 50) or a fixed cell (e.g. $B$2) but it's more useful to compare say, 1990 figures against 2013 figures, row by row.

The user interface is not obvious for doing this and I just happened to see Susan Harkins's article on Techrepublic.

To further clarify the syntax, here's my screenshot:




Community Support Forums

Microsoft used to work with Usenet forums but some years ago, decided to go their own way. Here are the official Community peer-to-peer forums:

Excel IT Pro: http://social.technet.microsoft.com/Forums/en-us/excel/threads
Word IT Pro: http://social.technet.microsoft.com/Forums/en-us/word/threads
Outlook IT Pro: http://social.technet.microsoft.com/Forums/en-us/outlook/threads
Office 2013 IT Pro: http://social.technet.microsoft.com/Forums/en-us/officeitpro/threads
Office 2010 IT Pro: http://social.technet.microsoft.com/Forums/en-us/officeitproprevious/threads
Older than Office 2010 IT Pro: http://social.technet.microsoft.com/Forums/en-us/officeitprolegacy/threads

Unofficial Google Groups Forums (formerly Usenet)
Access: https://groups.google.com/forum/?fromgroups#!forum/comp.databases.ms-access
Access: https://groups.google.com/forum/?fromgroups#!forum/microsoft.public.access
Excel: https://groups.google.com/forum/?fromgroups#!forum/microsoft.public.excel





Wednesday, March 27, 2013

Validation Rules for Fields and Controls in Microsoft Access

Often, we tend to use simple or no validation rules in Microsoft Access fields. I just wanted to block punctuation in content because I wanted to use the content as a a way of generating query column names (data driven architecture). Scrambled for the web and here's the official article with examples.

To allow digits and numbers but not punctuation, try:
Is Null OR Not Like "*[!((a-z) or (0-9))]*" 

Thursday, February 28, 2013

Microsoft Office and the Subscription Model

It's no secret that the generation method of earning income from selling software does not generate constant income. So many major companies want us to go onto a subscription sales model - you rent software on a monthly basis and they get assured income.

Microsoft Office 365 is a subscription model and yet it is / can be,  local installed software (Microsoft's strength) not just feature poor webapps. Here's a video detailing this:



Fast forward to the 6th minute for a quick demo and then start the video from scratch. The transcript is here and you can participate in a trial evaluation.

Wednesday, February 27, 2013

Musings on Conditional Counts / Sums

The COUNTIFS() function allows you to use more than one criteria for the statistic. Often, we think of this as for example, counting the row entries where Country is “Australia” and State is “NSW”

e.g.

  A B C
1 Australia NSW 10
2 Australia VIC 20
3 Australia NSW 30
4 USA Alabama 40
5 USA New York 50

So in this case, there are two rows of Australia, NSW

and the COUNTIF expression is

=COUNTIF(A1:A5,”Australia”,B1:B,”NSW”)

However, we can also use multiple AND criteria on one parameter e.g. Count how many entries are between 20 and 30

=COUNTIF("C1:C5”,">=20”,”C1:C5”,”<=30”)

In a related vein, for the visual Conditional Formatting, we can create more than one Rule on a Worksheet. Often, we may use a different Rules for different parts of the Worksheet. But, there’s nothing stopping you from using more than one Rule (as long as the Rules don’t overlap in scope) on the same cell block.

conditional

In case you want to look around:

Here’s a Microsoft produced video tutorial on SUMIF and COUNTIF functions:

and a downloadable Powerpoint Presentation on nested IFs