Difference between revisions of "Spreadsheet Tricks"

From TeleCafeWiki
Jump to navigation Jump to search
(Find Value; Return Column Header)
 
(44 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
{{RightTOC}} <!-- See: Manual:$wgRawHtml -->
 
{{RightTOC}} <!-- See: Manual:$wgRawHtml -->
−
== Miscellaneous ==
+
== Dashboards ==
 +
* [http://www.exceltip.com/excel-dashboards Excel Dashboards]
 +
: 100+ Excel Dashboards
  
−
=== Notes &amp; Comments ===
+
* [http://excelforum.com/winner/winner.html Winners of excelforum.com dashboard competition 2014]
−
* [http://www.techrepublic.com/blog/microsoft-office/an-alternative-to-excel-comments/ An alternative to Excel comments]
+
: Winners of 2014 dashboard competition.
−
: Use Excel's data validation feature to share meaningful information about specific cells with users.
+
 
 +
== Embedding Spreadsheets & Parts of Spreadsheets ==
 +
=== 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
 +
* [https://sites.google.com/a/googleapps.com/sitesbp/site-development/tips-tricks/inserting-portions-of-a-google-spreadsheet-into-a-google-sites-page Inserting Portions of a Google Spreadsheet into a Google Sites Page]
 +
; See Also
 +
* [http://productforums.google.com/forum/#!msg/docs/HHDMCcfSvEM/rrh9iv5XdPYJ Share spreadsheet with link to a sheet/cell]
 +
 
 +
=== Embed <s>SkyDrive</s> 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><br />
 +
[https://support.office.com/en-ca/article/Share-it-Embed-an-Excel-workbook-on-your-web-page-or-blog-from-OneDrive-804e1845-5662-487e-9b38-f96307144081 Share it: Embed an Excel workbook on your web page or blog from OneDrive]
 +
 
 +
== Google Spreadsheets ==
 +
''Also See: [[User:Dave/Web_Hack_Notes_(Non-Wiki)#Google_Sheets_as_CMS]]''
 +
 
 +
* [http://igoogledrive.blogspot.in/2012/08/conditional-format-to-drop-down-list.html Conditional format to a drop down list item in spreadsheet]
 +
: Step-by-step guide to conditionally formatting a drop-down list in Google Sheets.
 +
 
 +
* [http://www.labnol.org/internet/alternate-color-rows-in-google-sheets/28701/ How to Color Alternate Rows in Google Sheets]
 +
: 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.
 +
 
 +
* [http://www.labnol.org/internet/import-html-in-google-docs/28125/ How to Import Web Data into Google Docs]
 +
: Directly import tabular data from web pages and edit it inside a Google Spreadsheet.
 +
:: Example: <code>=ImportHTML("<nowiki>http://www.labnol.org/internet/tips-for-tech-startups/19483/</nowiki>", "list", 2)</code>
 +
 
 +
* [https://support.google.com/docs/answer/3093339?hl=en Docs editors Help: IMPORTHTML]
 +
: Imports data from a table or list within an HTML page.
 +
:: Example: <code>=IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4)</code>
 +
 
 +
* [https://docs.google.com/presentation/d/1HrCFCMh5XxF4JSma_A5-QpAvlyFhpeBzWkZT7CvEsS4/edit#slide=id.p Web Scraping with Google Docs]
 +
: A powerpoint-like presentation.
 +
 
 +
=== Link To Google Spreadsheets ===
 +
* [https://webapps.stackexchange.com/questions/44473/link-to-a-cell-in-a-google-spreadsheet-via-url Link to a cell in a Google Spreadsheet via URL?]
 +
: 2017-11-29: Editor's Note -- Try this link: [https://docs.google.com/spreadsheets/d/13PuCx8zKUjXvofFYBGzoOYog7UHpvLzCgxMLF9INnr8/edit#gid=1812582007&range=A3 Go to cell A3 of Sheet2]
 +
 
 +
* [https://productforums.google.com/forum/#!topic/docs/nYOSkroiHbU;context-place=topicsearchin/docs/authorid$3AAPn2wQcyXHY5Y7svWi8kx9AxKGGK1FnHEiBJEEu1ecbr6j6u484uqU3MMwVK3K1KLSRsACDU5uo4%7Csort:date%7Cspell:false How do I link to a specific tab with Google Sheets?]
 +
: 2017-11-29: Editor's Note -- Have not tested yet.
 +
 
 +
* [https://productforums.google.com/forum/#!topic/docs/HHDMCcfSvEM Share spreadsheet with link to a sheet/cell]
 +
: 2017-11-29: Editor's Note -- Have not tested yet. Solution is at bottom of list.
 +
 
 +
=== 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 ===
 +
<html><iframe width='950' height='300' frameborder='0' src='https://docs.google.com/spreadsheet/pub?key=0Ap9dh6jmi4yndEp1Q1M4cXk0QTFOTmpRWW5mb25kNkE&output=html&widget=true'></iframe></html><br />
 +
 
 +
== [[wikipedia:LibreOffice Calc|LibreOffice Calc]] ==
 +
* [http://stackoverflow.com/questions/31036760/how-can-i-update-libreoffice-calc-cells-in-real-time-from-a-mysql-database How can I update LibreOffice Calc cells in real-time from a MySQL database?] (StackExchange)
 +
 
 +
* [http://stackoverflow.com/questions/27102600/libreoffice-com-mysql-jdbc-driver-cannot-be-loaded LibreOffice: 'com.mysql.jdbc.driver' cannot be loaded]
 +
: This tutorial shows how to download and install the <tt>mysql-connector-java-x.x.x-bin.jar</tt> file in order to connect LibreOffice to MySQL.
 +
 
 +
== 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 ===
 +
* [http://osxdaily.com/2012/03/27/making-sense-of-mac-keyboard-symbols/ Making Sense of Mac Keyboard Symbols]
 +
: Ever wondered what those Mac keyboard symbols mean and what they translate to? Find help here.
 +
 
 +
* [https://exceljet.net/keyboard-shortcuts 222 Excel keyboard shortcuts for PC and Mac]
 +
: Windows shortcuts in dark gray on the left, Mac shortcuts in white on the right.
 +
:: See also: [https://exceljet.net/blog/the-54-excel-shortcuts-you-really-should-know The 54 Excel shortcuts you really should know] and [https://exceljet.net/blog/how-to-use-mac-function-keys-with-excel How to use Mac function keys with Excel].
 +
 
 +
* [http://www.exceltip.com/tips/250-ms-excel-keyboard-shortcuts.html 250 MS-Excel Keyboard Shortcuts]
 +
: [http://www.exceltip.com/wp-content/uploads/2015/05/250-Ms-Excel-Keyboard-Shortcuts.pdf Download – List of 250 Ms-Excel Keyboard Shortcuts] (PDF)
 +
: [http://www.exceltip.com/wp-content/uploads/2015/05/250-MS-Excel-Keyboards-Shortcuts.xlsx Download – List of 250 Ms-Excel-xlsx File] (XLSX)
 +
 
 +
* [http://akselrod-academy.com/paste-special/ Paste Special Excel Shortcut: Copy Formula, Paste Values, Copy Format, Excel Transpose, and more…]
 +
: There are many Excel shortcuts, but few are as versatile as the Paste Special command.
 +
 
 +
;<big>MS Excel Keyboard Shortcuts<nowiki>:</nowiki></big>
 +
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.
 +
 
 +
=== Learn Excel ===
 +
* [http://www.skilledup.com/articles/free-excel-tutorials Learn Excel Without Spending a Penny: 40 Free Excel Tutorials & Reference Sites]
 +
: Includes links to resources, like the following:
 +
::- [http://excelribbon.tips.net/ ExcelRibbon.Tips.Net] - Microsoft Excel Tips, Tricks, and Ideas!
 +
::- Duke's Fuqua School of Business - Ecklund Excel Review - [https://faculty.fuqua.duke.edu/~pecklund/ExcelReview/ExcelReview.htm Excel Review Materials]
  
−
* [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://www.exceltip.com/tips/introduction-to-use-advance-filter.html Introduction to Advance Filter]
−
: 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>
+
: Filtering is a simple, however, amazing & powerful way to analyze data. Advance filter are quite easy to use. Here’s how you can use Excel’s advanced filtering capabilities.
  
−
== Working With Text ==
+
* [http://www.exceltip.com/tips/7-ways-to-use-advance-filter.html 7 Ways to Use Advance Filter]
−
* [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...]
+
: Advance Filter is the most powerful feature of Excel. The advanced filtering feature in Excel allows you to quickly copy unique information from one data list to another. It allows the person to quickly remove duplicates, extract records that meet certain criteria. It works great when we use wildcards, within 2 date criteria.
  
−
=== Mixing Text &amp; Numbers ===
+
=== Miscellaneous Tricks ===
−
* [http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx TEXT function]
+
* [http://superuser.com/questions/562659/extract-date-from-string-in-excel extract date from string in excel]
−
: 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.
+
: For instance, to separate date from full path:<br />(e.g. <span style="color:blue"><tt>Directory\Sub-Directory\12-27-2016</tt></span> ''to'' <span style="color:blue"><tt>Directory\Sub-Directory\</tt>&nbsp;&nbsp;&nbsp;<tt>12-27-2016</tt></span>)
 +
:: Extract Path: <tt>=LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))-1)</tt>
 +
:: Extract Date: <tt>=SUBSTITUTE(A1,B1,"")</tt>
  
−
* [http://www.youtube.com/watch?v=JUB0E5r_ERw Return Cell Value as Number with Excel N Function] (Video)
+
* [http://stackoverflow.com/questions/3145980/how-to-insert-double-or-single-quotes How to Insert Double or Single Quotes]
−
: One of many video lessons from [http://www.youtube.com/user/contextures?feature=watch Contextures Inc.].
+
: Wrap String in Double Quotes: <tt>=""""&A2&""""</tt>
 +
: Wrap String in Single Quotes: <tt>="'"&A2&"'"</tt>
  
−
== Links ==
+
* [https://www.extendoffice.com/documents/excel/3278-excel-reverse-concatenate.html How to reverse the concatenate function 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?]
+
: As we all known, the Concatenate function in Excel can help us to combine multiple cells into one cell, but, sometimes, you want to do the opposite of Concatenate function, it means not combine cells, but split cell value into multiple separated cells. Have you ever thought of reversing the Concatenate function 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: <tt>=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))</tt>
  
−
* [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://stackoverflow.com/questions/9776611/insert-picture-into-excel-cell Insert picture into excel cell]
 +
: I can get the pictures to "float" around my worksheet, but I need to put them into a cell. How can I do this?
  
−
== Pivot Tables ==
+
==== Find Common Text Value From Multiple Columns ====
−
* [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://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?]
−
: This course includes one self-paced lesson and one practice session for hands-on experience.
+
: 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 &amp; 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>
 +
:''Array Formula: CTRL + SHIFT + ENTER''
  
−
== 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.
 
:: 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 47: Line 158:
 
:: 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>
  
−
=== Array Formula In Google Spreadsheet ===
+
=== Links ===
−
<html><iframe width='950' height='300' frameborder='0' src='https://docs.google.com/spreadsheet/pub?key=0Ap9dh6jmi4yndEp1Q1M4cXk0QTFOTmpRWW5mb25kNkE&output=html&widget=true'></iframe></html><br />
+
* [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]
 +
 
 +
; Moving worksheets containing formulas without linking back to source document.
 +
* [http://answers.microsoft.com/en-us/office/forum/office_2007-excel/paste-formulas-without-source-workbooks-filename/679dc626-907a-e011-9b4b-68b599b31bf5 Paste formulas without source workbook's filename]
 +
 
 +
==== 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 ===
 +
* [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://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.
 +
 
 +
* [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.].
 +
 
 +
==== Calculating Numbers in Cells Containing Text &amp; Numbers ====
 +
{| class="wikitable" style="color: black; background-color: #FFFAFA;"
 +
|+ Sum Numbers in Cells Containing Text & Numbers - <tt>IFERROR</tt> Version - Should work in Excel 2007 and above (PC) & Excel 2011 and above (Mac).
 +
! scope="col" |
 +
! scope="col" | A
 +
! scope="col" | B
 +
! scope="col" | C
 +
! scope="col" | D
 +
! scope="col" | E
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 1
 +
! scope="col" style="color:white; background-color: blue;" | String
 +
! scope="col" style="color:white; background-color: blue;" | Result
 +
! scope="col" style="color:white; background-color: blue;" | What It Does
 +
! scope="col" style="color:white; background-color: blue;" | Formula
 +
! scope="col" style="color:white; background-color: blue;" | Array?
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 2
 +
| 5 red 12 yellow 10, green 5 9
 +
| style="text-align:center; background-color: orange;" | 77
 +
| Sums all numbers found in cells A2:A4. Ignores text.
 +
| <code>{=SUM(IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A$2:$A$4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1,0))}</code>
 +
| Yes<small><br /><tt>CTRL+Shift+Enter</tt>&nbsp;(PC)<br /><tt>CMND+Shift+Enter</tt>&nbsp;(Mac)</small>
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 3
 +
| red 3, blue 9
 +
| style="text-align:center; background-color: yellow;" | 12
 +
| Sums just the numbers in A3.
 +
| <code>{=SUM(IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1,0))}</code>
 +
| Yes<small><br /><tt>CTRL+Shift+Enter</tt>&nbsp;(PC)<br /><tt>CMND+Shift+Enter</tt>&nbsp;(Mac)</small>
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 4
 +
| yellow 21, blue 3
 +
| style="text-align:center; background-color: yellow;" | 24
 +
| Sums just the numbers in A4.
 +
| <code>{=SUM(IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1,0))}</code>
 +
| Yes<small><br /><tt>CTRL+Shift+Enter</tt>&nbsp;(PC)<br /><tt>CMND+Shift+Enter</tt>&nbsp;(Mac)</small>
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 5
 +
| colspan="5" | '''Explanation:''' The formulas shown above are [http://www.excel-easy.com/functions/array-formulas.html array formulas]. After typing an array formula into the [http://www.computerhope.com/jargon/f/formulab.htm formula bar], finish by pressing <tt>CTRL+Shift+Enter</tt>&nbsp;(PC) or <tt>CMND+Shift+Enter</tt>&nbsp;(Mac).<br />
 +
'''Note:''' The formula bar indicates that this is an array formula by enclosing it in curly braces <tt>{&nbsp;}</tt>. Do not type these yourself. They disappear whenever you edit the formula, making it necessary to press <tt>CTRL+Shift+Enter</tt>&nbsp;(PC) or <tt>CMND+Shift+Enter</tt>&nbsp;(Mac) whenever you edit the formula. The curly braces <tt>{&nbsp;}</tt> in the examples shown above are displayed only to show what the formula looks like as it appears in the formula bar.<br />
 +
'''Source:''' [http://www.mrexcel.com/forum/excel-questions/505622-text-numbers-same-cell-sum-numbers.html text and,numbers in same cell, sum of numbers ?]<br />
 +
'''Hat Tip:''' [http://www.tablesgenerator.com/ Tables Generator]
 +
|}
 +
 
 +
{| class="wikitable" style="color: black; background-color: #FFFAFA;"
 +
|+ Sum Numbers in Cells Containing Text & Numbers - <tt>IF(ISERROR</tt> Version - Should work in Excel 2003 and above (PC) & Excel 2008 and above (Mac).
 +
! scope="col" |
 +
! scope="col" | A
 +
! scope="col" | B
 +
! scope="col" | C
 +
! scope="col" | D
 +
! scope="col" | E
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 1
 +
! scope="col" style="color:white; background-color: blue;" | String
 +
! scope="col" style="color:white; background-color: blue;" | Result
 +
! scope="col" style="color:white; background-color: blue;" | What It Does
 +
! scope="col" style="color:white; background-color: blue;" | Formula
 +
! scope="col" style="color:white; background-color: blue;" | Array?
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 2
 +
| 5 red 12 yellow 10, green 5 9
 +
| style="text-align:center; background-color: orange;" | 77
 +
| Sums all numbers found in cells A2:A4. Ignores text.
 +
| <code>{=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A$2:$A$4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE($A$2:$A$4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))}</code>
 +
| Yes<small><br /><tt>CTRL+Shift+Enter</tt>&nbsp;(PC)<br /><tt>CMND+Shift+Enter</tt>&nbsp;(Mac)</small>
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 3
 +
| red 3, blue 9
 +
| style="text-align:center; background-color: yellow;" | 12
 +
| Sums just the numbers in A3.
 +
| <code>{=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))}</code>
 +
| Yes<small><br /><tt>CTRL+Shift+Enter</tt>&nbsp;(PC)<br /><tt>CMND+Shift+Enter</tt>&nbsp;(Mac)</small>
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 4
 +
| yellow 21, blue 3
 +
| style="text-align:center; background-color: yellow;" | 24
 +
| Sums just the numbers in A4.
 +
| <code>{=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))}</code>
 +
| Yes<small><br /><tt>CTRL+Shift+Enter</tt>&nbsp;(PC)<br /><tt>CMND+Shift+Enter</tt>&nbsp;(Mac)</small>
 +
|-
 +
! scope="row" style="height:25px; width:25px;" | 5
 +
| colspan="5" | '''Explanation:''' The formulas shown above are [http://www.excel-easy.com/functions/array-formulas.html array formulas]. After typing an array formula into the [http://www.computerhope.com/jargon/f/formulab.htm formula bar], finish by pressing <tt>CTRL+Shift+Enter</tt>&nbsp;(PC) or <tt>CMND+Shift+Enter</tt>&nbsp;(Mac).<br />
 +
'''Note:''' The formula bar indicates that this is an array formula by enclosing it in curly braces <tt>{&nbsp;}</tt>. Do not type these yourself. They disappear whenever you edit the formula, making it necessary to press <tt>CTRL+Shift+Enter</tt>&nbsp;(PC) or <tt>CMND+Shift+Enter</tt>&nbsp;(Mac) whenever you edit the formula. The curly braces <tt>{&nbsp;}</tt> in the examples shown above are displayed only to show what the formula looks like as it appears in the formula bar.<br />
 +
'''Source:''' [http://www.mrexcel.com/forum/excel-questions/505622-text-numbers-same-cell-sum-numbers.html text and,numbers in same cell, sum of numbers ?]<br />
 +
'''Hat Tip:''' [http://www.tablesgenerator.com/ Tables Generator]
 +
|}
 +
 
 +
=== 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.
 +
 
 +
=== SQL &amp; Excel ===
 +
* [https://www.youtube.com/watch?v=t6U2OF0dj48 Excel Lesson 31 - Retrieve Data From MySQL Database Using MS Query] (YouTube tutorial.)
 +
: Very good tutorial! Learn how to retrieve data from MySQL Database using Microsoft Query, Join multiple tables & link the exported data to a drop down list to retrieve data dynamically based on the value of drop down list.
 +
:: Additional Excel video lessons from [https://www.udemy.com/user/deepdave/ this instructor]: [http://www.needforexcel.com/#!videos/c10fk Excel & VBA Video Tutorials]
 +
 
 +
* [https://www.youtube.com/watch?v=g4sT-X1UrQA Excel Lesson 34 - Import Data Using SQL Queries & Stored Procedures From SQL Server To Excel] (YouTube tutorial.)
 +
:  Learn How To Import Data Using SQL Queries & Stored Procedures From SQL Server To Excel. Database connectivity can be a very powerful tool, with which you can get real time refresh-able data which you can use for data analysis, creating dashboards etc. This method does not use VBA Coding and hence can be learnt by any excel user who does not know VBA Programming.
 +
 
 +
* [http://www.temida.si/~bojan/MPS/materials/Data%20Analysis%20Using%20SQL%20and%20Excel.pdf Data Analysis Using SQL and Excel] (Book - Large, slow-to-download PDF.)
 +
 
 +
* [http://forums.mysql.com/read.php?10,254825 Connecting to MySQL from Excel 2007] (MySQL Forums)
 +
: Answer: [http://forums.mysql.com/read.php?10,254825,254856#msg-254856 Re: Connecting to MySQL from Excel 2007]
 +
 
 +
* [http://stackoverflow.com/questions/25039573/how-to-connect-ms-excel-2007-with-mysql How to connect ms excel-2007 with mysql.] (StackExchange)
 +
: To connect Excel to MySQL, you need ODBC driver. This StackExchange answer combines two of the submitted answers for the solution.
  
−
== Embedding Spreadsheets & Parts of Spreadsheets ==
+
* [http://stackoverflow.com/questions/18798522/run-sql-on-excel-table Run SQL on excel table] (StackExchange)
−
=== 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><br />
 
−
[http://shrib.com/i9YCCq5t iFrame code for the spreadsheet on SkyDrive]
 
  
−
=== Embed Google Spreadsheet ===
+
=== Using SQL Statements in Excel ===
−
<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>
+
* [http://stackoverflow.com/questions/315504/tricks-for-generating-sql-statements-in-excel Tricks for generating SQL statements in Excel] (StackExchange)
−
; Sources
+
: '''Q:''' Do you have any tricks for generating SQL statements, mainly INSERTs, in Excel for various data import scenarios?
−
* [https://sites.google.com/a/googleapps.com/sitesbp/site-development/tips-tricks/inserting-portions-of-a-google-spreadsheet-into-a-google-sites-page Inserting Portions of a Google Spreadsheet into a Google Sites Page]
+
: '''A:''' (Spaces added for visibility - remove before inserting.)
−
; See Also
+
:: <code>=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")</code>
−
* [http://productforums.google.com/forum/#!msg/docs/HHDMCcfSvEM/rrh9iv5XdPYJ Share spreadsheet with link to a sheet/cell]
+
: Here is another view:
 +
:: <code>=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")</code>
  
−
== Quick Edit Sheets ==
+
* [http://stackoverflow.com/questions/16818796/generate-sql-insert-script-from-excel-worksheet Generate sql insert script from excel worksheet] (StackExchange)
−
* [https://ethersheet.org/ Ethersheet]
+
: '''Q:''' I have a large excel worksheet that I want to add to my database. Can I generate an SQL insert script from this excel worksheet?
−
: 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].
+
: '''A:''' You can use Excel to create insert statements:
 +
:: <code>="INSERT INTO table_name VALUES('"&A1&"','"&B1&"','"&C1&"')"</code>
 +
* [http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html How to Use SQL Statements in MS Excel] (Chron)
  
−
* [http://www.editgrid.com/ EditGrid]
+
* [http://www.wikihow.com/Embed-a-SQL-Query-in-Microsoft-Excel How to Embed a SQL Query in Microsoft Excel] (wikiHow)
−
: 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) ===
+
== Quick Edit Sheets ==
−
* [http://etherpad.org/ Etherpad.org]
+
Moved this section to: [[Office Productivity Hacks#Quick_Edit_Sheets|Office Productivity Hacks#Quick Edit Sheets]].
−
* [http://piratepad.nl/ PiratePad.nl]
 
−
* [http://piratepad.net/front-page/ Piratepad.net]
 
−
* [https://www.piratepad.ca/ PiratePad.ca]
 
  
−
== Excel Add-Ins ==
+
== Open-Source Tools ==
−
* [http://www.dutchcode.com/ Timeslider]
+
* [http://jspreadsheets.com/ JSpreadsheets - Best Open-Source Spreadsheets and Data Grids written in JavaScript.]
−
: 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.
+
: JSpreadsheets compiles the best open-source spreadsheets and data grids written in JavaScript. It helps you choose the most suitable component for your project.
  
 +
== See Also ==
 
== References ==
 
== References ==
 
{{Reflist}}
 
{{Reflist}}
 +
 +
{{Additional Resource Wiki Pages}}

Latest revision as of 16:39, 17 November 2019

Dashboards

100+ Excel Dashboards
Winners of 2014 dashboard competition.

Embedding Spreadsheets & Parts of Spreadsheets

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

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

Google Spreadsheets

Also See: User:Dave/Web_Hack_Notes_(Non-Wiki)#Google_Sheets_as_CMS

Step-by-step guide to conditionally formatting a drop-down list in Google Sheets.
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)
Imports data from a table or list within an HTML page.
Example: =IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4)
A powerpoint-like presentation.

Link To Google Spreadsheets

2017-11-29: Editor's Note -- Try this link: Go to cell A3 of Sheet2
2017-11-29: Editor's Note -- Have not tested yet.
2017-11-29: Editor's Note -- Have not tested yet. Solution is at bottom of list.

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>

LibreOffice Calc

This tutorial shows how to download and install the mysql-connector-java-x.x.x-bin.jar file in order to connect LibreOffice to MySQL.

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

Ever wondered what those Mac keyboard symbols mean and what they translate to? Find help here.
Windows shortcuts in dark gray on the left, Mac shortcuts in white on the right.
See also: The 54 Excel shortcuts you really should know and How to use Mac function keys with Excel.
Download – List of 250 Ms-Excel Keyboard Shortcuts (PDF)
Download – List of 250 Ms-Excel-xlsx File (XLSX)
There are many Excel shortcuts, but few are as versatile as the Paste Special command.
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.

Learn Excel

Includes links to resources, like the following:
- ExcelRibbon.Tips.Net - Microsoft Excel Tips, Tricks, and Ideas!
- Duke's Fuqua School of Business - Ecklund Excel Review - Excel Review Materials
Filtering is a simple, however, amazing & powerful way to analyze data. Advance filter are quite easy to use. Here’s how you can use Excel’s advanced filtering capabilities.
Advance Filter is the most powerful feature of Excel. The advanced filtering feature in Excel allows you to quickly copy unique information from one data list to another. It allows the person to quickly remove duplicates, extract records that meet certain criteria. It works great when we use wildcards, within 2 date criteria.

Miscellaneous Tricks

For instance, to separate date from full path:
(e.g. Directory\Sub-Directory\12-27-2016 to Directory\Sub-Directory\   12-27-2016)
Extract Path: =LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))-1)
Extract Date: =SUBSTITUTE(A1,B1,"")
Wrap String in Double Quotes: =""""&A2&""""
Wrap String in Single Quotes: ="'"&A2&"'"
As we all known, the Concatenate function in Excel can help us to combine multiple cells into one cell, but, sometimes, you want to do the opposite of Concatenate function, it means not combine cells, but split cell value into multiple separated cells. Have you ever thought of reversing the Concatenate function in Excel?
Example: =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
I can get the pictures to "float" around my worksheet, but I need to put them into a cell. How can I do this?

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.
Moving worksheets containing formulas without linking back to source document.

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

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

Calculating Numbers in Cells Containing Text & Numbers

Sum Numbers in Cells Containing Text & Numbers - IFERROR Version - Should work in Excel 2007 and above (PC) & Excel 2011 and above (Mac).
A B C D E
1 String Result What It Does Formula Array?
2 5 red 12 yellow 10, green 5 9 77 Sums all numbers found in cells A2:A4. Ignores text. {=SUM(IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A$2:$A$4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1,0))} Yes
CTRL+Shift+Enter (PC)
CMND+Shift+Enter (Mac)
3 red 3, blue 9 12 Sums just the numbers in A3. {=SUM(IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A3," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1,0))} Yes
CTRL+Shift+Enter (PC)
CMND+Shift+Enter (Mac)
4 yellow 21, blue 3 24 Sums just the numbers in A4. {=SUM(IFERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1,0))} Yes
CTRL+Shift+Enter (PC)
CMND+Shift+Enter (Mac)
5 Explanation: The formulas shown above are array formulas. After typing an array formula into the formula bar, finish by pressing CTRL+Shift+Enter (PC) or CMND+Shift+Enter (Mac).

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces { }. Do not type these yourself. They disappear whenever you edit the formula, making it necessary to press CTRL+Shift+Enter (PC) or CMND+Shift+Enter (Mac) whenever you edit the formula. The curly braces { } in the examples shown above are displayed only to show what the formula looks like as it appears in the formula bar.
Source: text and,numbers in same cell, sum of numbers ?
Hat Tip: Tables Generator

Sum Numbers in Cells Containing Text & Numbers - IF(ISERROR Version - Should work in Excel 2003 and above (PC) & Excel 2008 and above (Mac).
A B C D E
1 String Result What It Does Formula Array?
2 5 red 12 yellow 10, green 5 9 77 Sums all numbers found in cells A2:A4. Ignores text. {=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A$2:$A$4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE($A$2:$A$4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))} Yes
CTRL+Shift+Enter (PC)
CMND+Shift+Enter (Mac)
3 red 3, blue 9 12 Sums just the numbers in A3. {=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))} Yes
CTRL+Shift+Enter (PC)
CMND+Shift+Enter (Mac)
4 yellow 21, blue 3 24 Sums just the numbers in A4. {=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))} Yes
CTRL+Shift+Enter (PC)
CMND+Shift+Enter (Mac)
5 Explanation: The formulas shown above are array formulas. After typing an array formula into the formula bar, finish by pressing CTRL+Shift+Enter (PC) or CMND+Shift+Enter (Mac).

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces { }. Do not type these yourself. They disappear whenever you edit the formula, making it necessary to press CTRL+Shift+Enter (PC) or CMND+Shift+Enter (Mac) whenever you edit the formula. The curly braces { } in the examples shown above are displayed only to show what the formula looks like as it appears in the formula bar.
Source: text and,numbers in same cell, sum of numbers ?
Hat Tip: Tables Generator

Pivot Tables

This course includes one self-paced lesson and one practice session for hands-on experience.

SQL & Excel

Very good tutorial! Learn how to retrieve data from MySQL Database using Microsoft Query, Join multiple tables & link the exported data to a drop down list to retrieve data dynamically based on the value of drop down list.
Additional Excel video lessons from this instructor: Excel & VBA Video Tutorials
Learn How To Import Data Using SQL Queries & Stored Procedures From SQL Server To Excel. Database connectivity can be a very powerful tool, with which you can get real time refresh-able data which you can use for data analysis, creating dashboards etc. This method does not use VBA Coding and hence can be learnt by any excel user who does not know VBA Programming.
Answer: Re: Connecting to MySQL from Excel 2007
To connect Excel to MySQL, you need ODBC driver. This StackExchange answer combines two of the submitted answers for the solution.

Using SQL Statements in Excel

Q: Do you have any tricks for generating SQL statements, mainly INSERTs, in Excel for various data import scenarios?
A: (Spaces added for visibility - remove before inserting.)
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")
Here is another view:
=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")
Q: I have a large excel worksheet that I want to add to my database. Can I generate an SQL insert script from this excel worksheet?
A: You can use Excel to create insert statements:
="INSERT INTO table_name VALUES('"&A1&"','"&B1&"','"&C1&"')"

Quick Edit Sheets

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

Open-Source Tools

JSpreadsheets compiles the best open-source spreadsheets and data grids written in JavaScript. It helps you choose the most suitable component for your project.

See Also

References

edit · view  

Additional Resource Wiki Pages

Home • Dave
Retrieved from "https://www.telecafe.org/smw/index.php?title=Spreadsheet_Tricks&oldid=999"