Difference between revisions of "Spreadsheet Tricks"

Revision as of 11:05, 28 March 2015

Dashboards

Excel Dashboards

100+ Excel Dashboards

Winners of excelforum.com dashboard competition 2014

Winners of 2014 dashboard competition.

Miscellaneous

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.

Fusion Tables

Fusion Tables Help

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

Google Spreadsheets

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.

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.

@@ Line 81: / Line 81: @@
 == Quick Edit Sheets ==
-* [https://ethercalc.org EtherCalc]
+Moved this section to: [[Office Productivity Hacks#Quick_Edit_Sheets|Office Productivity Hacks#Quick Edit Sheets]].
-: EtherCalc is a web spreadsheet. Your data is saved on the web, and people can edit the same document at the same time. Everybody's changes are instantly reflected on all screens. No sign-up required; start writing instantly. Drag and drop a .csv, .ods, or a .xlsx file to import it.
-* [https://ethersheet.org/ Ethersheet]
-: Ethersheet is an open source tool to make collaborating on spreadsheets fast, easy and secure. You can also [https://github.com/ethersheet-collective/EtherSheet download the code on GitHub].
-=== Dearly Departed Quick Edit Sheets ===
-* [http://www.editgrid.com/ EditGrid]
-: Unfortunately, EditGrid's service is ending May 1, 2014.<ref>[http://www.editgrid.com/ EditGrid service is ending May 1, 2014]</ref>
-=== Other Edit Pads (Non-Spreadsheet) ===
-* [http://etherpad.org/ Etherpad.org]
-* [http://www.firepad.io/ Firepad]
-: Firepad is an open source collaborative text editor you can add to your own website.
-* [https://www.piratepad.ca/ PiratePad.ca]
-* [http://piratepad.net/front-page/ Piratepad.net]
-* [http://piratepad.nl/ PiratePad.nl]
-* [https://www.protectedtext.com ProtectedText]
-: Your notes. Protected. Enter any URL, e.g. protectedText.com/anything - you find it - its yours! Encrypt all your notes, access anywhere. Simple. Fast. Free. No ads. Secure - don't trust us, check the code yourself.
-* [https://code.stypi.com/ Stypi]
-* [http://sync.in Sync.in]
-: Sync.in is a web based word processor for people to collaborate in real-time. When multiple people edit the same document simultaneously, any changes are instantly reflected on everyone's screen. The result is a new and productive way to collaborate with text documents, useful for meeting notes, brainstorming, project planning, training, and more.
-* [https://titanpad.com TitanPad]
-: TitanPad lets people work on one document simultaneously. "We are rescuing EtherPad for your use." No sign-up required; start writing instantly.
 == Excel Add-Ins ==