Search Contextures Sites

 

 

Contextures
Newsletter Index

 

 

 

 

30 Excel Functions in 30 Days

 

 

 

Contextures News

Excel Weekly News from Contextures Sept 18, 2012

 
Create adjustable shaded rows on a worksheet, lock headings in place, show missing pivot table items, and other tips, in this week's Excel news from Contextures.

Create Alternating Shaded Rows on Excel Sheet

You can quickly add shaded rows to a worksheet by inserting a table, in Excel 2010 and 2007.

If you don't want to use a table, and its table style, you can create adjustable alternating shaded rows with conditional formatting.



Click here to see the instructions, and watch the short video: Create Alternating Shaded Rows on Excel Sheet

Lock Excel Heading Rows in Place

Do you ever have trouble with the Excel Freeze Panes command? Have you tried the new Freeze Top Row and Freeze First Column commands

 

I learned a few surprising things about freezing Excel headings on a worksheet, and shared them in this article. Click here to read the tips and see the video: Lock Excel Heading Rows in Place

Show Missing Items in Excel Pivot Table

When you filter a pivot table, some of that data might disappear.

For example, if a customer didn't order all the colours for each product, only the colours that they did order will appear in the pivot table, if you filter for that customer name.



If you want to keep the layout consistant, and always see all the colours listed, you can change a pivot table setting.

To see the steps, please read my article on the Pivot Table Blog: Show Missing Items in Excel Pivot Table

Introducing Spreadsheet Controls in Office 2013

Among the new features that will be available in Office 2013 are spreadsheet controls. There are tools designed "to help organizations establish proper safeguards and controls over mission critical EUCs."

On Microsoft's Excel Blog, Steve Kraynak, Program Manager on the Excel Services team, describes some of these features, and how you can use them to find mission critial workbooks, and analyze them.



Read more about the new controls here: Introducing Spreadsheet Controls in Office 2013

Conditional Formatting of Lines in Excel Chart

On his Peltier Tech Blog, Jon Peltier shares his method for adding conditional formatting to the lines in an Excel chart. The techniques uses VBA, and in this example the increase are coloured blue, decreases are orange, and grey indicates no change.



Read the details here:  Conditional Formatting of Lines in an Excel Line Chart Using VBA

Interactive Pivot Table Calendar & Chart in Excel

Chandoo created an interactive pivot table, based on a list of dates and some date formulas. Click on a Slicer to select a year and month, and see the calendar for that month.



See Chandoo's instructions here: Interactive Pivot Table Calendar & Chart in Excel

Recommended Excel Tools

They aren't free, but these Excel tools are a worthwhile investment, so please click on the links to take a look at their features, and decide if they're right for you.

Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.

 

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.