Thursday, February 9, 2012

EXCEL HOW-TOs



When you have data in the adjacent column (left or right), you can also simply double click on the fill handle icon (instead of clicking and dragging). Double-clicking would do the same thing and fill down the column until the last filled cell in the adjacent column.

I had same issue and in poking around, I found a quick workaround that actually gets the job done without any macros, etc.
In the first cell of the column that has the URL's in it (i.e. the first URL, not the title) and double click on the URL in Cell to 'activate' it. Wait till it turns into a link (you may have to actually open the item so the sheet recognizes it.
Once it is underlined, you simply click the next cell down and select to the end of the column data. NOW the Cell Styles will work. Click Cell Styles from the Home Tab and select Hyperlink and viola - all the cells change to clickable hyperlinks.

Create UDF that accessible from all files at all times (INCOMPLETE)
Udf in the personal.xlsb workbook
Try saving the file with macros as .xlam (excel add-in)
How to Make an Inactive Add in Active in Excel
(Note to self) Use the udf by typing this way in the cell :
=(file name where the udf resides)!(udf name and parameters)

---

CONCATENATE Excel Ranges (Using VBA)

Find non consecutive (out of sequence) number
How to Find Missing Numbers in a Sequence

Print long list in multiple column
How to Print Long Columns in Excel on 1 Page - Snake Columns

Insert, move, or delete page breaks in a worksheet

How to Select Only the Visible Cells in Excel 2016 and Prior
Select the range of cells in your worksheet.
Press Alt+; (hold down the Alt key and then press the semicolon key)

Workbook with many sheets: How to select all sheets except the first one
1. Go to last sheet
2. On left arrow of sheet navigator, ctrl-left click it
3. Shift-left click on second sheet
4. right click and choose Delete

Spreadsheet Compare
- only available with Office Professional Plus 2013 or Office 365 ProPlus
Alternative(s):
cloud based

(Subtotal) Select and copy summary lines only:
Overcome a Nuance of Excel's Subtotal Feature

Transpose multiple rows to multiple columns

Determine MDX query Excel send to server:
How To view MDX query In Excel Sheet
Related:
Administration and Optimization: SQL Server Profiler for Analysis Services Queries

Nested (multi level) data validation
Create Dependent Drop Down Lists
(all tips regarding data validation)

Creating global macro
In EXCEL is there a global space like the Normal.dotm where EXCEL macros can be stored and referenced just like WORD's .dotm?
Select 'Personal Macro Workbook' in the 'Store macro in' dropdown

(Pivot NON-OLAP) How to view underlying data (Excel Pivot Table DrillDown Show Details)

To see the underlying records for a number in the pivot table:

  1. In the Pivot Table, right-click the number for which you want the customer details.
  2. In the pop-up menu, click Show Details

TIP: Instead of using the Show Details command, you can double-click on a Values cell.

(Pivot) Sort Subtotal values, not details
Right-click on the subtotal of any of the Salespersons in the Grand Total column.
Select Sort from the dropdown list.

(Pivot) Display dimension levels side-by-side :
1. Click any cell in your pivot table, and the PivotTable Tools tab will be displayed.
2. Under the PivotTable Tools tab, click Design > Report Layout > Show in Tabular Form

(Pivot) Create Date/Time grouping dimension from Date/Time column/data:
1. Select a single field item in the PivotTable.
2. From the Data menu, select Group and Outline, and then Group.
3. Excel displays a dialog box in which it automatically enters a start and end date.
....It also lists a number of intervals to group by. (Use Ctrl to select multiple intervals)
4. Select an appropriate interval and click OK.

Change thousand and decimal separators in cell values:
Replacing and Converting in a Macro

Printing in newspaper style (two columns)
Copy list to Word then use Page Layout \ Columns function
How to Print Long Columns in Excel on 1 Page - Snake Column

XL2013: Invisible table border:
Home \ Font \ [Draw Borders] \ [Line Color]

Add percentage column when connecting to SQL Analysis Services (OLAP)
Adding a Calculated Field to an Excel Pivot Table With Analysis Services
- works only with Excel 2010 or later
OLAP PivotTable Extensions
In the case of Calculated Measures and Calculated Members on Excel 2013, we can use its native feature as described in the series of articles start with [Office 2013] New OLAP Tools Part 1: Calculated Measure
Example :
Another option (harder but will be visible to all users) is to add calculated measure ON THE SERVER :
Yet another option that MIGHT work is to use Power Pivot, but it's available on certain editions of Excel only: Where is Power Pivot?

Remove empty cells scattered between filled cells
Quickly Delete Blank Rows From A Long List

How do I locate merged cells in my Excel worksheet?

Padding out a cell with leading zeros
=REPT(0,8-LEN(A1))&A1

Excel displaying long-number text with scientific notation :
Delete Scientific Notation by Using Text to Column Feature

Preserve leading zero(es) when copy-pasting:
Setup target column to use custom format of something like 00000
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)