Monday, July 20, 2009

Laying out the carpet for to recipients for your Excel files

I was looking at the MSKB 942385 and then downloaded the Office 2007 Service Pack 1 Changes_all.xls package. Irritating, that file is. Excel is now a great grid and table tool – it’s natural formatting, data handling (filtering, sorting) abilities exceeded Word’s model a long time ago. So whenever you need to deliver a dense grid of data, Excel is the way to go. Of course, Access would be as good but not everyone has Access on their machine. But, people still have a Word mindset – if you want to deliver for example 400 over rows of info to your recipient, leave that word processor approach of laying it out nice so that people can print (do you help them print?) but help them more pro-actively. Facilitate their hunting for gems in that 400 row grid, ensure that they can hit “print” and it prints well.

Download that file and play along with me. See what you can do to help the recipient along.

Suggested Best Practices

  1. Forget that danged banner proclaiming that the file is what it is – you can put a brief title in the Excel header / footer. Additionally, you can repeat the banner in the worksheet tab
  2. Provide Folder Path / Filename info as well as Page x of y in the Excel Header/Footer
  3. Ensure that the grid has a column of category names. Remove arbitrarily placed Category Headers from the grid.
  4. Put the data in an Excel Table. So that the recipient can sort/filter easily
  5. Freeze the column title / row titles. Dang! Frozen worksheets can’t go into the new Page Layout View.
  6. Set relevant Print Titles so that the paper follows your frozen worksheet titles.

Now, was it that hard to extend some courtesy to the recipient? What other tips for any Office program can you think of that will facilitate recipients?

Friday, July 17, 2009

The Excel 2007 .XLSB file format Tags:

The Excel Team Blog has this to say about the curious .XLSB file format.

Even though we've done a lot of work to make sure that our XML formats open quickly and efficiently, this binary format is still more efficient for Excel to open and save, and can lead to some performance improvements for workbooks that contain a lot of data, or that would require a lot of XML parsing during the Open process.

(In fact, we've found that the new binary format is faster than the old XLS format in many cases.)

Also, there is no macro-free version of this file format – all XLSB files can contain macros (VBA and XLM). 

Runtime performance - once loaded into memory, the file format has no effect on application/calculation speed


Reblog this post [with Zemanta]

Thursday, July 16, 2009

Interesting Business Intelligence Tool – Gemini

Just saw the YouTube demo of the Gemini project (SQL Server and Excel) handle 20 million data rows and related tables (on a netbook PC) in an impromptu expression, interactively, in a few seconds.

Wednesday, July 15, 2009

Put it in a table

Microsoft Office Excel 2007

Image via Wikipedia

Excel 2007 has enhanced the object formerly known as Lists (in 2003) – they’re now called Tables. Tables are now super cool, the greatest thing in Excel since, oh, AutoFiltering.

How do you make a Table?

I guess the obvious way is to Insert > Table.

But I much prefer placing your cell cursor on a cell in the midst of a dense rectangular shaped block of data cells and doing the Home > Format As Table action.

What can Tables do for you?

Let me count the ways

  1. A Table can have banded rows and/or columns. This colouring is independent of Direct Formatting or Cell Styles
  2. A Table auto-extends in columns if you type a new heading
  3. A Table auto-extends if you insert a new column or row in the midst of the cells
  4. An auto-extending table also extends any range names that you have assigned to column blocks or row blocks
  5. A table has a TableName and has ColumnNames which can be used in new Structured Formulae.
  6. If you type a formula in an empty cell in a column of the table, Excel 2007 tries to give you a Structured Formula (MyTable[ThisRow][Salary] * 9% instead of the classic A2*9%
  7. Completing a formula in an empty cell in a column of the table will cause Excel 2007 to fill the rest of the cells in that column with relevant formulae.
  8. A table does not mind you inserting a new row at the bottom of the table and it auto extends the banded shading as well as the Table’s cell addresses to that new row.
  9. A table acts as a a suggestion to be the source for a Pivot Table.
  10. A table overcomes the guessing glitch Excel 2007 has with regard column titles which look like numbers. For example if your column titles were CountryName, 2008, 2009, when you select the whole block to make an Excel chart, Excel 2007 thinks 2008 is a Datapoint and not a Data Series Name. So you then have to unconvince it by excluding the column titles of your block and manually editing the Data Series Name. Or maybe you have to apply Numeric Formatting as Text.
  11. A table supports a Totals Row which can have the aggregate statistic, normally SUM, AVERAGE, COUNT, etc…
  12. You can refer to totals of Table Columns by using the Expression fragment – SUM(TableName[ColumnName]) – the Table Name and Column Name appear in the autocomplete list when you progressively type the formula into the cell.
  13. A Table has the Remove Duplicates Command
  14. A Table has the AutoFilter drop down arrows in the column titles row – you can do all the AutoFilter activities

What you can’t do with a Table

  1. You can’t carry out the Data > Subtotals command in a Table. It’s greyed out. You can convert the Table back to a Range and then apply AutoFilters.
  2. You can’t use the TableName syntax in a Data Validation of a List. Is there a workaround?

Curious things about a Table

  1. If you change the Accent1, 2, 3… colours in a Theme, that affects Table Colours in an unexpected manner.
  2. If you want to use the Table smarts but don’t like the coloured banding and coloured borders in a Table, you can clear this colouring. There is one choice in the gallery of Table Formats called “None” but this choice is not visible on the Home Tab of the Ribbon. It is visible in the (Table Tools) Design Tab of the Ribbon – as well, there is a Clear All text item in that Gallery of Table Formats.
Reblog this post [with Zemanta] Tags: ,

Monday, July 13, 2009

Friday, July 10, 2009

Spotted Book: Business Analysis with Microsoft Excel

You can get the paper or the Adobe Acrobat version of Conrad Carlberg’s book.

Instead of teaching how to push buttons in Excel, he delivers working with Excel in the context of accounting and finance. Tags: ,,,

Spotted Book – slide:ology

I tend to browse the bookshelves when I shop. There are some interesting books out there. Nancy Duarte has a really worthwhile presentation book called slide:ology. Her blog features some extended content freebies which are worth looking at and downloading themselves.