My Collection of Excel Tips by David Hager

“#Excel, did you know?” that you can use a CUBE function in an #Excel conditional formatting formula.
“#Excel 2013 did you know” that there are download files in Help for new worksheet functions.
“#Excel, did you know?” that chart based on TABLE will auto-expand when new data is added.
“#Excel, did you know” that you drag/drop data from one #Excel window to another?
“#Excel did you know?” you can use a defined name range on an xl4 macro sheet in a formula?
“#Excel did you know?” that you can compare two columns by using GoTo Special – Row Differences?
“#Excel did you know?” that if a number of worksheets are selected, right-clicking one will make it the active sheet.
“#Excel, did you know?” magic UDF part 2: .AddComment .Comment.Text CStr(Now()) End With End Function
“#Excel, did you know?” magic UDF part 1: Function TS() Application.Volatile True On Error Resume Next TS = “TS” With Application.Caller
“#Excel, did you know?” you can make and store your own custom cell styles.
“#Excel, did you know?” if you fill a table across worksheets, new tables are made automatically in the destination worksheets.
“#Excel, did you know?” you can import XML from Internet which becomes a data table that you can then load into PowerPivot.
“#Excel, did you know?” that you can use Insert, Object to place a linked Excel file inside itself.
“#Excel, did you know?” there are >250 commands that are not accessible through the ribbon, but can be added to the Quick Access Toolbar.
“#Excel, did you know?” you can r-cl the cell below a text list & select “Pick from drop-down list” to pick an entry in the list.
“#Excel, did you know?” you can right-click the scroll buttons in lower left corner to access a list of all worksheets in workbook.
“#Excel, did you know?” you can create a defined name formula (DNF) from an UDF without arguments.The DNF can then be used in other formulas.
“#Excel, did you know?” you can write an external link formula in a cell that points to a cell in an #Excel file stored on the Internet.
“#Excel, did you know?” you can return the sum of top 3 values of a filtered column with =SUM(AGGREGATE(14,1,filtered_col,ROW(1:3))) CSE
“#Excel, did you know?” r-cl a shape, cl 1st option & enter VBA procname in Address box preceded by #. cl on shape to go to that proc in VBE.
“#Excel, did you know?” you can link a data range in an external workbook to a sparkline.
“#Excel, did you know?” you can link a cell in an external workbook to a shape.
“#Excel, did you know?” Convert formulas to values: Select range, right-click edge, pull away, drop in original area and select menu item.
“#Excel, did you know?” that sparklines can use defined name formulas such as =OFFSET($A$1,1,$G$1,COUNTA($A:$A)-1,1) as their input range.
“#Excel, did you know?” that sparklines autoadjust with auto-expansion of tables if associated with a vertical range.
“#Excel, did you know?” that sparklines respond to filters in tables if associated with a vertical range.

Advertisements

One thought on “My Collection of Excel Tips by David Hager

  1. Pingback: #Excel Super Links #125 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s