Difference between revisions of "Spreadsheet Tricks"
Jump to navigation
Jump to search
(Dashboards - Excel Dashboards: 100+ Excel Dashboards) |
(→Quick Edit Sheets: EtherCalc ; Firepad ; ProtectedText ; Stypi ; Sync.in ; TitanPad) |
||
| Line 81: | Line 81: | ||
== Quick Edit Sheets == | == Quick Edit Sheets == | ||
| + | * [https://ethercalc.org EtherCalc] | ||
| + | : 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] | * [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]. | : 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] | * [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> | : Unfortunately, EditGrid's service is ending May 1, 2014.<ref>[http://www.editgrid.com/ EditGrid service is ending May 1, 2014]</ref> | ||
| Line 89: | Line 92: | ||
=== Other Edit Pads (Non-Spreadsheet) === | === Other Edit Pads (Non-Spreadsheet) === | ||
* [http://etherpad.org/ Etherpad.org] | * [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] | * [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 == | == Excel Add-Ins == | ||
Revision as of 09:05, 28 March 2015
Dashboards
- 100+ Excel Dashboards
- Winners of 2014 dashboard competition.
Miscellaneous
Notes & Comments
- Use Excel's data validation feature to share meaningful information about specific cells with users.
- 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
- 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))
- Example:
- Array Formula: CTRL > SHIFT > ENTER
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))
- Example:
- 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.)
- Example:
- Example:
=INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$26,MATCH(G2,$A$2:$A$26),)))
- Example:
Working With Text
Mixing Text & Numbers
- 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.
- One of many video lessons from Contextures Inc..
Links
- Example: While text box is selected, input a formula like
=Contractors!$C$2to link the text box to the content of cell C2 on the "Contractors" tab.
Pivot Tables
- This course includes one self-paced lesson and one practice session for hands-on experience.
Fusion Tables
- Fusion Tables is an experimental data visualization web application to gather, visualize, and share larger data tables.
Google Spreadsheets
- 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)
- Example:
- A powerpoint-like presentation.
Array Formula In Google Spreadsheet
<html><iframe width='950' height='300' frameborder='0' src='https://docs.google.com/spreadsheet/pub?key=0Ap9dh6jmi4yndEp1Q1M4cXk0QTFOTmpRWW5mb25kNkE&output=html&widget=true'></iframe></html>
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
<html><iframe width='500' height='350' frameborder='0' src='https://docs.google.com/spreadsheet/ccc?key=0Aumujjc9cTZhdE92TExvcjU5NVZBbFk2Ul82dFI1ZUE&single=true&gid=0&range=J2%3AQ12&output=html&widget=true'></iframe></html>
- Sources
- See Also
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.
- Ethersheet is an open source tool to make collaborating on spreadsheets fast, easy and secure. You can also download the code on GitHub.
Dearly Departed Quick Edit Sheets
- Unfortunately, EditGrid's service is ending May 1, 2014.[1]
Other Edit Pads (Non-Spreadsheet)
- Firepad is an open source collaborative text editor you can add to your own website.
- 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.
- 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.
- 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
- 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.