Difference between revisions of "Spreadsheet Tricks"

From TeleCafeWiki
Jump to navigation Jump to search
(→‎Google Spreadsheets: How to Color Alternate Rows in Google Sheets)
m (Minor clarification, fixes.)
Line 7: Line 7:
 
: Winners of 2014 dashboard competition.
 
: Winners of 2014 dashboard competition.
  
−
== Miscellaneous ==
+
== Microsoft Excel ==
−
=== Notes & Comments ===
+
 
 +
=== Miscellaneous Tricks ===
 +
==== Notes & Comments ====
 
* [http://www.techrepublic.com/blog/microsoft-office/an-alternative-to-excel-comments/ An alternative to Excel comments]
 
* [http://www.techrepublic.com/blog/microsoft-office/an-alternative-to-excel-comments/ An alternative to Excel comments]
 
: Use Excel's data validation feature to share meaningful information about specific cells with users.
 
: Use Excel's data validation feature to share meaningful information about specific cells with users.
  
 
* [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("<nowiki>Tutorial found here: http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024</nowiki>")</code>
  
−
=== Find Value; Return Column Header ===
+
==== Find Value; Return Column Header ====
 
* [http://www.excelforum.com/excel-formulas-and-functions/937047-find-average-return-column-header-of-number-closest-to-average.html Find Average, Return Column Header of Number Closest To Average]
 
* [http://www.excelforum.com/excel-formulas-and-functions/937047-find-average-return-column-header-of-number-closest-to-average.html 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.
 
: 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.
Line 33: Line 35:
 
:: Example: <code>=INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$26,MATCH(G2,$A$2:$A$26),)))</code>
 
:: Example: <code>=INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$26,MATCH(G2,$A$2:$A$26),)))</code>
  
−
== Working With Text ==
+
=== 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...]
 
* [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 &amp; Numbers ===
+
==== Mixing Text &amp; Numbers ====
 
* [http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx TEXT function]
 
* [http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx 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.
 
: 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.
Line 43: Line 45:
 
: 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 ==
+
=== Links ===
 
* [http://www.extendoffice.com/documents/excel/957-excel-link-textbox-to-cell.html How to link textbox to a specific cell in Excel?]
 
* [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.
 
: 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.
Line 49: Line 51:
 
* [http://www.extendoffice.com/documents/excel/958-excel-hyperlink-to-sheet.html Create hyperlink in a cell to another sheet in the same workbook]
 
* [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 ==
+
=== 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]
 
* [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.
 
: This course includes one self-paced lesson and one practice session for hands-on experience.
−
 
−
== Fusion Tables ==
 
−
* [https://support.google.com/fusiontables/#topic=1652595 Fusion Tables Help]
 
−
: Fusion Tables is an experimental data visualization web application to gather, visualize, and share larger data tables.
 
  
 
== Google Spreadsheets ==
 
== Google Spreadsheets ==
Line 67: Line 65:
 
* [https://docs.google.com/presentation/d/1HrCFCMh5XxF4JSma_A5-QpAvlyFhpeBzWkZT7CvEsS4/edit#slide=id.p Web Scraping with Google Docs]
 
* [https://docs.google.com/presentation/d/1HrCFCMh5XxF4JSma_A5-QpAvlyFhpeBzWkZT7CvEsS4/edit#slide=id.p Web Scraping with Google Docs]
 
: A powerpoint-like presentation.
 
: A powerpoint-like presentation.
 +
 +
=== Fusion Tables ===
 +
* [https://support.google.com/fusiontables/#topic=1652595 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 ===
 
=== Array Formula In Google Spreadsheet ===

Revision as of 13:38, 30 March 2015

Dashboards

100+ Excel Dashboards
Winners of 2014 dashboard competition.

Microsoft Excel

Miscellaneous Tricks

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))
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))
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: =INDEX($B$1:$E$1,MATCH(H2,INDEX($B$2:$E$26,MATCH(G2,$A$2:$A$26),)))

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$2 to 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.

Google Spreadsheets

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.
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)
A powerpoint-like presentation.

Fusion Tables

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

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

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

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.

See Also

References

Retrieved from "https://www.telecafe.org/smw/index.php?title=Spreadsheet_Tricks&oldid=607"