I’m always on the lookout for well written Excel tips for crunching large amounts of data.  Seems many of the litigators I know need to analyze large datasets.  Allen Wyatt’s ExcelTips Online is a terrific resource.  He offers a weekly e-mail newsletter, and 2 RSS feeds – Daily Nuggets and the Weekly Classic.

Check out this tip – Deriving Monthly Median Values – the example looks at sixty years worth of data and offers two strategies for finding the monthly median value.  He explains as he goes, which makes his tips even more valuable.  I believe you could take this tip and run with it to suit your needs.

Advertisements

Quick Excel Tips

July 17, 2006

A few Excel tips on a hot summer afternoon …

  • Press Alt + Enter to force a new line in a cell.
  • To quickly select all the cells in a spreadsheet, click the “cell” above the Row 1 cell, to the left of the Col A cell [see the fat plus sign in the screen cap below?]
    xl_sl_all.png
    Or, use Ctrl + A , the universal Windows keyboard shortcut for Select All.
  • Quickly set column widths as wide as they need to be – place the mouse pointer between the column headings [see screen cap below] – and double-click.
    xl-best-width.png
    Select contiguous multiple columns by pressing and dragging the mouse across column headings – watch the pointer – it should take on the appearance of a downward pointing arrow.
    xl-sel-mul-cols.png
    Select non-contiguous columns by holding down Ctrl key while using the mouse to select various columns.  Once the above selections have been made, you can set column widths as mentioned in the 1st part of this tip.

Excel workbooks can contain many spreadsheets – indeed the max is limited only by memory. Navigating a workbook containing many spreadsheets can be annoying – scrolling back and forth looking for the spreadsheet you want to work with, or using keyboard shortcuts to flip through many sheets is really inefficient.

Here’s a quick way to jump to the spreadsheet you want to work with:

Right-click any of the navigation arrows (located towards the lower left of the workbook window). Choose the desired spreadsheet from the resultant shortcut menu. Fast!

xl_sel_ssheet.png

A list in Excel is a “chunk of data”. Usually the data has headings across the top of the chunk. A list should contain no empty rows or columns, it can contain empty cells (because sometimes you don’t have the data necessary to fill in the cell.)

Examples of lists – mailing list; list of sales people including their territory and sales figures for several years; Top 100 Businesses in your area including # of employees, sales, name of CEO.

Although we often think of Excel as a tool for performing calculations, some lists may contain only text (such as a name and address list.) The value of creating and maintaining this type of list in Excel vs. Word is the many tools available to sort, filter, subtotal, extract, merge, and otherwise evaluate and manipulate the list data. Read the rest of this entry »

The Best Computer Tips and Software Deals on the Internet! Visit WorldStart’s home page and peruse their archived computer tips, newsletters, and software store. I receive their MS Office and Computer Tips newsletters and enjoy the variety of information and how well it’s presented.

Get your viewers here:

Word 2003 viewer – replaces Word 97 and all previous viewers – you can open, view, print and copy all MS and Mac Word files, and files saved in many other formats.

Excel 2003 – replaces Excel 97 viewer – you can open, view, print, copy Excel 2003, 2002, 2000, and 97 workbooks.

PowerPoint 2003 – replaces PowerPoint 97 – MS says this lets you view presentations created in PowerPoint 97 and on up. No mention of copying or printing.

We’ll be posting soon about some of the intricacies and myths of WordPerfect to Word.

Have you ever struggled with centering a title above an Excel spreadsheet?

Creative approaches abound – typing extra spaces in front of the title to “push” it over, dragging a text box until it seems to be centered above the spreadsheet, etc. Creative, yes. Time consuming, YES! Practical, NO! Once you widen or narrow some columns, that title you so carefully forced to “center” above the spreadsheet will be awry.

Here’s the real deal …
Read the rest of this entry »