Search Contextures Sites
Excel Weekly News from Contextures Sept 18, 2012Create 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 SheetYou 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 TableWhen 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 2013Among 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 ChartOn 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 ExcelChandoo 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 ToolsThey 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.
- Contextures 30 Excel Functions in 30 Days
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
- Excel Pivot Table Intro Course
Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.