Difference between revisions of "Spreadsheet Tricks"

From TeleCafeWiki
Jump to navigation Jump to search
(→‎See Also: Update section.)
(Find Common Text Value From Multiple Columns)
Line 8: Line 8:
  
 
== Microsoft Excel ==
 
== Microsoft Excel ==
 +
=== 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.
 +
 
=== Keyboard Shortcuts ===
 
=== Keyboard Shortcuts ===
 
* [http://www.exceltip.com/tips/250-ms-excel-keyboard-shortcuts.html 250 MS-Excel Keyboard Shortcuts]
 
* [http://www.exceltip.com/tips/250-ms-excel-keyboard-shortcuts.html 250 MS-Excel Keyboard Shortcuts]
Line 36: Line 40:
  
 
=== Miscellaneous Tricks ===
 
=== Miscellaneous Tricks ===
−
==== Notes & Comments ====
+
==== Find Common Text Value From Multiple Columns ====
−
* [http://www.techrepublic.com/blog/microsoft-office/an-alternative-to-excel-comments/ An alternative to Excel comments]
+
* [http://superuser.com/questions/811009/excel-how-do-i-find-common-text-values-from-multiple-2-columns Excel- How do I find common text values from multiple (>2) columns?]
−
: Use Excel's data validation feature to share meaningful information about specific cells with users.
+
: What it does: Finds the common text value across multiple columns that matches the corresponding value in the selected column. For instance, if you're trying to find a text value in Column C that has a corresponding match in both Columns A & B, you could use a formula like the one shown below.
−
 
+
:: Example: <code>=MIN(MAX(($A$2:$A$53=$C2)*1),MAX(($B$2:$B$44=$C2)*1))</code>
−
* [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]
+
:''Array Formula: CTRL + SHIFT + ENTER''
−
: 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 ====
Line 47: Line 50:
 
: 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.
 
:: Example: <code>=INDEX(B$1:E$1,MATCH(MIN(ABS(B2:E2-AVERAGE(B2:E2))),ABS(B2:E2-AVERAGE(B2:E2)),0))</code>
 
:: Example: <code>=INDEX(B$1:E$1,MATCH(MIN(ABS(B2:E2-AVERAGE(B2:E2))),ABS(B2:E2-AVERAGE(B2:E2)),0))</code>
−
:''Array Formula: CTRL > SHIFT > ENTER''
+
:''Array Formula: CTRL + SHIFT + ENTER''
  
 
* [http://www.mrexcel.com/forum/excel-questions/37642-formula-return-column-header.html Find Minimum & Return Column Header]
 
* [http://www.mrexcel.com/forum/excel-questions/37642-formula-return-column-header.html Find Minimum & Return Column Header]
Line 60: Line 63:
 
* [http://www.mrexcel.com/forum/excel-questions/551308-find-row-find-value-then-return-column-heading.html Find row, find value, then return column heading]
 
* [http://www.mrexcel.com/forum/excel-questions/551308-find-row-find-value-then-return-column-heading.html Find row, find value, then return column heading]
 
:: 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>
 +
 +
=== 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]
 +
 +
==== Notes &amp; 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.
 +
 +
* [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("<nowiki>Tutorial found here: http://lifehacker.com/add-comments-to-a-formula-in-excel-for-your-future-refe-510113024</nowiki>")</code>
  
 
=== Working With Text ===
 
=== Working With Text ===
Line 70: Line 86:
 
* [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 ===
 
=== Pivot Tables ===
Line 113: Line 123:
 
== Quick Edit Sheets ==
 
== Quick Edit Sheets ==
 
Moved this section to: [[Office Productivity Hacks#Quick_Edit_Sheets|Office Productivity Hacks#Quick Edit Sheets]].
 
Moved this section to: [[Office Productivity Hacks#Quick_Edit_Sheets|Office Productivity Hacks#Quick Edit Sheets]].
−
 
−
== 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.
 
  
 
== See Also ==
 
== See Also ==

Revision as of 08:05, 8 June 2015

Dashboards

100+ Excel Dashboards
Winners of 2014 dashboard competition.

Microsoft Excel

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.

Keyboard Shortcuts

Download – List of 250 Ms-Excel Keyboard Shortcuts (PDF)
Download – List of 250 Ms-Excel-xlsx File (XLSX)
MS Excel Keyboard Shortcuts:

Shown below is a list of the 10 most important Excel keyboard shortcuts you must know for analyzing data, writing quick formulas, formatting data, switching between sheets, etc.

1. Shift + F10 – The Right Click pop up open
Most of the time users suffer to have the right click shortcut while working on any database. By pressing shift + F10, you can open the right click menu. You can use up / down arrow keys to move to various options. The right arrow key is used to open fly-out menu. Hit enter when you get to the item you want.
2. Ctrl + 1 – Open “Format Cells” dialog box
It is one of the most influential and time saving shortcuts in Excel. It enables the user to skip all the long procedure to format cells, charts, text boxes, etc. to make life easy.
3. Ctrl + 5 – Applies or remove strike-through
We all make list on excel and wants to cross it off once it’s done. It gives us feel like we have finished our tasks. Select a cell or range and then press CTRL + 5 to apply strike-through to the cell / range.
4. Ctrl + 9 – Hide the complete row
In Excel, move anywhere in a row you want to hide and then press CTRL + 9.The entire current row is hidden. You can also use Shift+Up arrow or Shift+Down arrow to select several rows.
5. Ctrl + SHIFT + L – Turn on / off filter
One of the powerful and useful shortcuts (CTRL + SHIFT + L) while working on large data set especially when you set few filters on, and then want to return back to the full view of data clearing all filters.
6. Ctrl + * – Select the current region
Select the current range by pressing CTRL + asterisk key, which is the complete data set, in all directions from the current cell until Excel hits the edge of the worksheet.
7. F4 – To repeat last action
F4 is one of the best shortcuts in Excel and there are various ways to use it. It saves the user from keystrokes and mouse involvement. If you are not editing a cell, it repeats the last command or action. For example, if you have just deleted a row and you want to delete another row, simply select the range where you want to delete the next row and press F4.
8. F4 – Set cell references or add dollar sign to a reference
F4 cycles through all 4 types of cell references (absolute, mixed reference (2x) and relative). It helps to change the reference style from relative (A1) to absolute ($A$1). It is very useful when you want to take formula from one cell to other cell but want to keep the same reference in the formula.
9. Ctrl + T – To Insert a Table
In Excel, Tables are very flexible and easy to work. Especially to convert bunch of data into table.To make it in table, select any cell in the spreadsheet (with our without data) and press CTRL+ T. If your spreadsheet contains header cells, you might need to check the My Table Has Headers option.
10. Shift + F2 – Insert / edit comment
This shortcut will help you to add or edit the comment easily.

Miscellaneous Tricks

Find Common Text Value From Multiple Columns

What it does: Finds the common text value across multiple columns that matches the corresponding value in the selected column. For instance, if you're trying to find a text value in Column C that has a corresponding match in both Columns A & B, you could use a formula like the one shown below.
Example: =MIN(MAX(($A$2:$A$53=$C2)*1),MAX(($B$2:$B$44=$C2)*1))
Array Formula: CTRL + SHIFT + ENTER

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),)))

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.

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..

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 OneDrive 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>
Share it: Embed an Excel workbook on your web page or blog from OneDrive

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.

See Also

References

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