Spreadsheet Tricks

Dashboards

Excel Dashboards

100+ Excel Dashboards

Winners of excelforum.com dashboard competition 2014

Winners of 2014 dashboard competition.

Microsoft Excel

Miscellaneous Tricks

Notes & Comments

An alternative to Excel comments

Use Excel's data validation feature to share meaningful information about specific cells with users.

Add Comments to a Formula in Excel for Your Future Reference

The N() function comes in handy if you want comments embedded within the formula for your own notes, reference, etc.
Example:

=SUM(E82:E83)+N("Tutorial found here: http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024")

Find Value; Return Column Header

Find Average, Return Column Header of Number Closest To Average

What it does: Finds the average of each row, then determines which column header comes closest to the average and returns that column's header.

Example: =INDEX(B$1:E$1,MATCH(MIN(ABS(B2:E2-AVERAGE(B2:E2))),ABS(B2:E2-AVERAGE(B2:E2)),0))

Array Formula: CTRL > SHIFT > ENTER

Find Minimum & Return Column Header

What it does: Finds the minimum in a row and returns the text that appears in that column's header.

Example: =INDEX($B$1:$E$1,MATCH(MIN(B2:E2),B2:E2,0))

Find Value in Row and Return Column Heading

What it does: Finds a value in a row and returns column header.

Example: =LOOKUP(2,1/($B2:$E2=$G2),$B$1:$E$1) (Dave's modification.)

Example: =LOOKUP(2,1/($D2:$Z2="x"),$D$1:$Z$1) (One of the original source formulas.)

Find row, find value, then return column heading

Example: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$26,MATCH(G2,$A$2:$A$26),)))

Working With Text

Excel: How can you restrain text from flowing into adjacent blank cells? -- Message 11 of 15: If you don't need to see all the text...

Mixing Text & Numbers

TEXT function

Describes the formula syntax and usage of the TEXT function in Microsoft Office Excel. Excel's TEXT function returns a value converted to text with a specified format.

Return Cell Value as Number with Excel N Function (Video)

One of many video lessons from Contextures Inc..

Links

How to link textbox to a specific cell in Excel?

Example: While text box is selected, input a formula like =Contractors!$C$2 to link the text box to the content of cell C2 on the "Contractors" tab.

Create hyperlink in a cell to another sheet in the same workbook

Pivot Tables

PivotTable I: Get started with PivotTable reports in Excel 2007

This course includes one self-paced lesson and one practice session for hands-on experience.

Google Spreadsheets

How to Color Alternate Rows in Google Sheets

Google Sheets do not support zebra stripes (yet) but you can use conditional formatting combined with a simple Google Formula to create a formatted table.

How to Import Web Data into Google Docs

Directly import tabular data from web pages and edit it inside a Google Spreadsheet.

Example: =ImportHTML("http://www.labnol.org/internet/tips-for-tech-startups/19483/", "list", 2)

Web Scraping with Google Docs

A powerpoint-like presentation.

Fusion Tables

Fusion Tables Help

Fusion Tables is an experimental data visualization web application to gather, visualize, and share larger data tables.

Array Formula In Google Spreadsheet

Embedding Spreadsheets & Parts of Spreadsheets

Embed SkyDrive Spreadsheet

<html><iframe width="650" height="450" frameborder="0" scrolling="no" src="https://skydrive.live.com/embed?cid=0D58D956AB4BC9E0&resid=D58D956AB4BC9E0%21117&authkey=AH7Glp3oB3WJ4Ag&em=2&AllowTyping=True&wdDownloadButton=True"></iframe></html>
iFrame code for the spreadsheet on SkyDrive

Embed Google Spreadsheet

Sources

Inserting Portions of a Google Spreadsheet into a Google Sites Page

See Also

Share spreadsheet with link to a sheet/cell

Quick Edit Sheets

Moved this section to: Office Productivity Hacks#Quick Edit Sheets.

Excel Add-Ins

Timeslider

The Timeslider is a historical data engine for Microsoft Excel. The add-in allows you to store and retrieve historical data directly from the cells of your spreadsheet.

References