Difference between revisions of "Spreadsheet Tricks"
Jump to navigation
Jump to search
(Pivot Tables) |
(Working With Text + Links) |
||
| Line 1: | Line 1: | ||
{{RightTOC}} <!-- See: Manual:$wgRawHtml --> | {{RightTOC}} <!-- See: Manual:$wgRawHtml --> | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
== Miscellaneous == | == Miscellaneous == | ||
| Line 16: | Line 8: | ||
* [http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024 Add Comments to a Formula in Excel for Your Future Reference] | * [http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024 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.<br />Example: <code>=SUM(E82:E83)+N("Tutorial found here: http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024")</code> | : The N() function comes in handy if you want comments embedded within the formula for your own notes, reference, etc.<br />Example: <code>=SUM(E82:E83)+N("Tutorial found here: http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024")</code> | ||
| + | |||
| + | == Working With Text == | ||
| + | * [http://www.techrepublic.com/forums/questions/excel-how-can-you-restrain-text-from-flowing-into-adjacent-blank-cells-1/post-f5f962ab-d1b1-11e2-bc00-02911874f8c8/ 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 === | === Mixing Text & Numbers === | ||
| Line 23: | Line 18: | ||
* [http://www.youtube.com/watch?v=JUB0E5r_ERw Return Cell Value as Number with Excel N Function] (Video) | * [http://www.youtube.com/watch?v=JUB0E5r_ERw Return Cell Value as Number with Excel N Function] (Video) | ||
: One of many video lessons from [http://www.youtube.com/user/contextures?feature=watch Contextures Inc.]. | : One of many video lessons from [http://www.youtube.com/user/contextures?feature=watch Contextures Inc.]. | ||
| + | |||
| + | == Links == | ||
| + | * [http://www.extendoffice.com/documents/excel/957-excel-link-textbox-to-cell.html How to link textbox to a specific cell in Excel?] | ||
| + | : Example: While text box is selected, input a formula like <code>=Contractors!$C$2</code> to link the text box to the content of cell C2 on the "Contractors" tab. | ||
| + | |||
| + | * [http://www.extendoffice.com/documents/excel/958-excel-hyperlink-to-sheet.html Create hyperlink in a cell to another sheet in the same workbook] | ||
| + | |||
| + | == Pivot Tables == | ||
| + | * [http://office.microsoft.com/en-us/excel-help/pivottable-i-get-started-with-pivottable-reports-in-excel-2007-RZ010205886.aspx 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. | ||
=== Array Formula In Google Spreadsheet === | === Array Formula In Google Spreadsheet === | ||
| Line 51: | Line 56: | ||
* [http://piratepad.net/front-page/ Piratepad.net] | * [http://piratepad.net/front-page/ Piratepad.net] | ||
* [https://www.piratepad.ca/ PiratePad.ca] | * [https://www.piratepad.ca/ PiratePad.ca] | ||
| + | |||
| + | == Excel Add-Ins == | ||
| + | * [http://www.dutchcode.com/ 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 == | == References == | ||
{{Reflist}} | {{Reflist}} | ||
Revision as of 19:17, 21 February 2014
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")
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.
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
- Ethersheet is an open source tool to make collaborating on spreadsheets fast, easy and secure. You can also download the code on GitHub.
- Unfortunately, EditGrid's service is ending May 1, 2014.[1]
Other Edit Pads (Non-Spreadsheet)
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.