Dynamic Data Validation with Tables in Excel

Dynamic Data Validation with Tables in Excel Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong? The answer to these common questions may surprise you. Many spreadsheets I’ve come across have a common problem – inconsistent data entry. If someone enters “United States of America” in a [...]

58 Crazy Excel Stories (that are probably happening in your company right now)!

58 Crazy Excel Stories (that are probably happening in your company right now)! Crazy things happen when you mix untrained users with Excel. It’s not really rocket science.  Crazy things would happen with any complex system if you don’t train people properly. But many companies simply assume that everyone knows Excel, so they [...]

See Celsius in your cells (try saying that quickly 3 times!)

I recently was asked if there's a way of adding the Celsius symbol to a value in Excel. The answer is yes, and the method can be applied to all sorts of useful symbols / notation.  e.g. bullet points, m2, km/hr, bbl, currency symbols etc. This is how.... Pick a blank cell then go to [...]

2017-03-08T03:53:54+00:00 Formatting, Functions|

Stop merging cells!

Stop merging cells! One Microsoft Excel Insider has been quoted as saying that 53% of Excel Users merge cells. There is a standard tool on the toolbar called “Merge and Center”, it “merges” two or more cells into one and then centers the text horizontally within the merged cell. This can cause a number of [...]

Quick Tip: Beautiful Numbers in 10 Seconds

A report is more useful if it is formatted well Steps: Highlight the numbers you want to format Right Click - Format Cells Custom > then copy and paste (or type) the following into the Type box: #,##0_);[Red](#,##0);-??_)   Done If you want to format other numbers with the same format you can always right [...]

2017-03-08T03:53:55+00:00 Formatting|

Feeling Bloated? How to fix inexplicably large spreadsheets

Sticking with the theme of the health of your spreadsheet, have you ever notice spreadsheet become inexplicably bigger? Suddenly becoming 20MB instead of 2MB. One cause is unused rows and columns. How do I check and how do I fix it? Simple, on each worksheet in your file press End then Home. Your cursor will [...]

 Last Day Of The Month + Useful Date Formula

Last Day of the Month + Useful Date Formula How do I display the last date of a month in a cell? By Neale Blackwood There are two methods. The first uses Excel's series feature. If you enter two dates, for example, 31/1/03 in A1 and 28/2/03 in B1, you select both cells and [...]

2017-03-08T03:53:57+00:00 Formatting|

 Merged Cells Alternative

Merged cells alternative When copying and pasting cells, I sometimes get an error message that says: By Neale Blackwood Merged cells can cause difficulties when using Excel. There is a standard tool on the toolbar called "Merge and Center". Many people use this tool to format reports, as it "merges" two or more cells [...]

2017-03-08T03:53:57+00:00 Formatting|

 Stop Zeroes Displaying

 Stop Zeroes Displaying Is it possible to stop zero values displaying in a range? By Neale Blackwood That can be achieved using a Custom Number format. First select the range, then click the Format menu and select Cells (Ctrl + 1 will open the Format Cells dialog box). Click the Number tab and select [...]

2017-03-08T03:53:58+00:00 Formatting|

 View Values And Formula At The Same Time

View Values and Formula at the same time Is it possible to view cells' values and formulae simultaneously? By Neale Blackwood, 1 Dec 08 There is a technique that lets you see the formula and the value at the same time. This is often useful when creating or debugging a spreadsheet. Open a file [...]

2017-03-08T03:53:58+00:00 Formatting|