Office Blogs: Helping business analysts take full advantage of Excel 2016 and the new Power BI

Surely you have already installed the Office 2016 Preview - check out this post about Excel 2016 and Power BI features on Office Blogs.

Excel Help Forums

Just thought of sharing links to those Excel forums I have found useful:

• stackoverflow
• Microsoft Answers
• MrExcel Message Board

I know there tons of others too; please share your favorite Excel forum in the comments and I'll add all quality sites to a new post!

Excel: Sum Value of Same Cell Across Multiple Sheets

Heres a tip how to sum value of the same cell across multiple worksheets:

=SUM(Sheet1:Sheet3!B3)

This sums cell B3 from Sheet1, Sheet 2 and Sheet 3 - voilá!

European Spreadsheet Risks Interest Group (Excel Horror Stories)

Scary Excel Horror Stories here.

Excel VBA: Create Masked Password Dialog

Want to make a masked password dialog for your workbook?

Check this tip from Microsoft.

How to Round Numbers in Excel

Chandoo posted a thorough article on tips to rounding numbers using Excel.

Improving Excel 2010 Performance

Since Excel 2007 we've had the "Big Grid" available - meaning over 1000 times the amount of cells than in older Excel versions.

This brings challenges in keeping the performance good while amount of data and formulas increase.

Read Microsoft's article Excel 2010 Performance: Improving Calculation Performance to get a better understanding of the calculation logic and how to improve you workbooks performance.

Excel Shortcut: Open VBA Editor's Immediate Window

Ah the number of time's you've hoped that the VBA Editor's Immediate window could be attached to Excel's own window. That's not going to happen soon I think, so here's a tip how to quickly open and activate the Immediate window with some shortcut keys:

Alt + F11 Ctrl + G

Alt + F11 opens VBA Editor and Ctrl + G activates the Immediate window. Convenient way to help your spreadsheet editing especially with short single-line macros.

Excel Shortcut: Add Date or Time to Cell

To quickly add current date to active cell press CTRL + SEMICOLON (i.e. CTRL + ; )

To add current time press CTRL + COLON (i.e. CTRL + : )

VBA: Remove All Shapes From Sheet

To remove all shapes from a sheet, use the below snippet:

Sub RemoveShapes()


    Dim s As Shape


    For Each s In ActiveSheet.Shapes
        s.Delete
    Next s


End Sub

Excel: Count Unique (Number) Values

To count the unique number values in a range, say for example column A, you can use a combination of SUM, IF and FREQUENCY formulas.

Example:
=SUM(IF(FREQUENCY(A:A;A:A)>0;1))


Check the help for more details on FREQUENCY formula.

Excel: How to Repair a Corrupted Workbook (Excel 2002, Excel 2003)

Read Microsoft's tips how to repair a corrupted workbook. For Excel 2002 and Excel 2003.

Excel: How to Repair a Corrupted Workbook (Excel 2007, Excel 2010)

Read Microsoft's tips how to repair a corrupted workbook. For Excel 2007 and Excel 2010.

Excel Shortcut: Open Save As.. Dialog

To open the Save As... dialog press F12

VBA: Deleting Rows Programmatically

When you need to delete rows in your spreadsheet programmatically, start from the last row and head towards the first one. This can be accomplished by using Step -1 in the For loop, see example below:


For lRow = lLastRow To lFirstRow Step -1
    ActiveSheet.Rows(lRow).Delete
Next lRow


If we would loop starting from the first row, our lRow counter would be pointing incorrectly at the moment when a first row gets deleted.

Excel Shortcut: Select Entire Worksheet / Select All Data

If worksheet contains data, Ctrl + A selects the current region. To select entire worksheet press Ctrl + A a second time.

Excel Shortcut: Select Entire Column

To select the entire column in Excel, select cell in that column and press Ctrl + Space

Excel Shortcut: Open VBA Editor

To quickly open VBA Editor window press Alt + F11

Excel Shortcut: Insert Comment or Edit Existing

To insert a new comment or to edit an existing one, select the cell and press Shift + F2

Quickly Copy Outlook Appointment's Tracking Status to Any Other Program

Scheduled an appointment in Outlook and want to quickly get the attendees' tracking status to say - Meeting Minutes or notes?

In Outlook 2010 this is easy:

• Open the appointment in Outlook
• In the Meeting ribbon click the small arrow under Tracking button
• Click "Copy Status to Clipboard"
• Open Word/Excel/other and paste - you're done!

How to copy Outlook appointment tracking status

The format is pure text with tab separated values so you can paste it to almost any program.