Excel Data Validation Rules

Where to find data validation rules in Excel's ribbon? 

Data -> Data Tools -> click on the drop-down with a green tick and a red no-entry sign, then select Data Validation.

You can choose to allow any values, or restrict values e.g. to a list.

OOXML versus OpenDocument

When configuring Microsoft Word on a new machine, you will be prompted if you wish to have Office Open XML (informally OOXML) or OpenDocument (extension .odt for text) as the default format.

Choose the former if you want to be more Microsoft-centric and use all features of Microsoft's ecosystem, choose the latter if you need interoperability with applications like LibreOffice.

OOXML started out as an XML format for Excel and later extended to other applications such as Word.

How do I add a comment to an Excel Spreadsheet?

Review Toolbar -> New Comment.

To review and edit - right click the cell and click "Edit comment".

The X Factor - File Changes introduced in Microsoft Office 2007 and Preserved Henceforth

MS Office 2007 introduced new file extensions - XLSX for Excel, PPTX for Powerpoint and DOCX for Word. These are generally smaller in size than their pre-Office 2007 equivalents. These use Office XML. So, for example, a PPTX file would an XML-based Powerpoint presentation. Note that these file formats disallow macros. The corresponding macro-enabled versions all have the X replaced with an M - hence we have XLSM for a macro-enabled Excel workbook, PPTM for a macro-enabled Powerpoint presentation and DOCM for a macro-enable Word document. In theory, these XML formats allow the files to be consumed by a variety of applications, not just Word, Excel or Powerpoint and allow batch processing software to be built, for example processing automatically hundreds of Word documents.

Opening VBA Editor in Excel 2010 Onwards

File -> Options -> Customize Ribbon -> Check Developer

By default the Developer tab is not visible in Excel!

Query Active Directory in Excel

Excel 2016 comes with something called "Get and Transform" which allows you to query Active Directory stores. This is known as "Power Query" in other versions of Excel. Data discovery - made simple (or fairly simple).

The Length of a Number stored as String in Excel

It's always LEN(X)-1, where X is the cell reference. We need to subtract 1 for the preceding apostrophe!

The Annoying Numbers Stored As Text Error in Excel

Typically in Excel, if we design a worksheet with a number stored as text, then we have made a conscious decision to do so. Consequently it is very annoying to see this error every time we see a number stored as text.

A valid use case would be a product or part number, which may be preceded by zeros, or a telephone number with the same convention. Alternatively we may be considering permutations of ones and zeros in which case preceding zeros are valid informational content.

To get rid of these errors:

File
Options
Formulas
Error Checking Rules - Deselect "Numbers formatted as text or preceded by apostrophe"

Job done.

Turn off Automated Spelling Correction in Excel

Automated spelling correction in Excel is a nuisance especially when dealing with stock tickers. To turn if off do the following:

File -> Options -> Proofing -> Auto-correct Options -> Disable "Replace Text As You Type."

Hide Dotted Lines in Excel Showing Page Breaks

Dotted lines showing page breaks are useful to see how adjusting your spreadsheet impacts the printed copy. However, they are not always aesthetically pleasing. To remove them, do the following:

File -> Options (last item)  -> Advanced (middle item) -> Display Options for this Worksheet (appears below Display) -> Deselect Show Page Breaks.

The Ergonomics of Version Numbers - What Version of Excel am I Running?

The Perennial Navigational Challenge to Operate Microsoft Office

Finding out what version of Excel you are using is not always straightforward as the user interface changes rapidly with each release. Once upon a time, Ferdinand Magellan had to circumnavigate the world, now people must collectively circumnavigate new versions of software applications, and Office, in particular, provides particular challenges.

The Traditional Idiom for Version Numbers for Windows Applications

Traditionally, version information was always stored in the Help->About screen. How Style Guidelines have changed!

The Office 2010 "Backstage View" and Corresponding Shortcut (From Help to the File Menu)

Office 2010 introduces the "Backstage View". Here you will find your Excel version by clicking: File->Help (Alt-F-H). In 2010, you'll see the Product Version (say 2010) in the top-right and the actual Excel version in the bottom right (e.g. Version: 14) and Copyright Notice.

The  Excel 2013 Methodology

Try the same keyboard shortcut above in Excel 2013 and you will be asked to share your worksheet - Ha!  Instead you must to File -> Account -> About.

Sum a Filtered List in Excel 2010

Easy! 

Remove Hyperlinks from Excel 2010 Worksheet (Hint: Eraser Dropdown)

Select the cells with the offending hyperlinks. Click the Home tab in the Ribbon, and the Editing subsection $(or Editing "Group"). The Ribbon is made of groups e.g. Font, Alignment, Number, Editing. Click the "Eraser Dropdown"and select "Clear Formats" (NOTE: NOT Clear All as this will delete the hyperlink text as well).

Where is Sort on the Crazy Excel Ribbon?

It's on the Data tab (just after Page Layout and Formulas). You will see a Sort icon which looks like a box with A|Z Z|A written on it. It's in a "frame" in the Ribbon called "Sort & Filter".

Disable Privacy Warnings in Excel when Saving Workbooks

Go to Developer tab, and select the "Macro Security" button (with the "Roadworks" sign), Privacy Options and un-check everything. Hey Presto, you are done!

Inverting the STANDARD Cumulative Normal Distribution in Excel and C++

For various financial applications, you will need to invert the cumulative standard normal distribution. The correct Excel worksheet function for this is:

NORMSINV

You need to give it a number between 0 and 1. The function NORMSINV is confusing, because the name does not allude to the fact that we are inverting the CUMULATIVE normal distribution. (NORMINV is the cousin of NORMSINV that inverts the Normal Distribution more generally. Consequently, mean and standard deviation parameters must be specified explicitly. NORMSINV can be regarded as a special case of NORMINV that just accepts a probability as parameter).

Here is an algorithm for computing the inverse cumulative normal distribution. Note that the function is a continuous, non-linear (curve rather than a straight line) function and maps the (0,1) open interval on the x-axis into the entirety of the real line (although most visibly between -3 and 3 on the y-axis). It's very important to understand visually what this function is, to avoid confusing it with any other related function.

If you want to do this in Visual C++, then the Boost Math library (written by John Maddock and Paul Bristow) will do the needful.

Boost implements a generalized version of this, using the notion of Quantiles. A quantile can be thought of as the inverse of a Cumulative Distribution Function, returning a value x such that cdf(dist, x) == p. The quantile is also known as the percent point function, or percentile, or the lower critical value of the distribution.

Summary: Quantile = Inverse CDF

To understand the Boost API, we should also understand what is the complement of the cumulative distribution function. This is the probability that a variable take a value greater than x.

INDIRECT, ADDRESS, MATCH

What is INDIRECT for?

INDIRECT: This is for creating cell or range references on-the-fly.
Example: If A1 has the value "B2" then INDIRECT(A1) will return the value in B2.

What happens when you press return in Excel?

After entering data in a cell, the cursor usually moves vertically down to the next cell. The cursor can also move laterally (left or right) after data entry.

This is configurable from Excel Options->Advanced->Direction, but also from VBA as well.

The setting is controlled by the MoveAfterReturnDirection property on the Application object, which can be set to xlDown, xlToLeft or xlToRight. Doing TypeName on xlDown you will see it is defined as a Long. We can therefore define a function with a static Long that can switch input mode via a custom Keyboard Shortcut.

Sub SwitchCursorMode()
Static status As Integer
Dim msg As String
If status = xlDown Or status = 0 Then
status = xlToRight
msg = "Move Right After Data Entry"
Else
status = xlDown
msg = "Move Down After Data Entry"
End If
Application.MoveAfterReturnDirection = status
End Sub

Shove the above into Excel and then assign a keyboard shortcut. The msg string is not needed, just there for debugging.

Automatic Percent Entry

Excel Options->Advanced->Editing Options->Enable Automatic Percent Entry

The Hidden World of Excel Charts

How on earth do you add a title to an Excel chart, which is by default created sans-title? Nothing to be seen in the context menus...mais non, a simple click in the "Plot Area" and a "Layout" option appears in the top menu..from which you can create a new title. But to add problems to more problems, it offers you the option to make the title "Centred Overlay Chart" - how stupid and useless! The option you want is "Above Chart". Madness in Design. The Mad Hatter is at it again.