Excel 2013 new feature: Timeline Slicer

What’s New in Excel 2013?  Timeline Slicers !

Timeline Slicers are a feature new to Excel 2013.
If your PivotTable contains dates, a Timeline Slicer affords you and your reviewers an easy method to filter and scale of the dates displayed..

(For detailed instructions on creating PivotTables review the 5 minute video tutorial  PivotTables and Slicers )

Inserting the Timeline Slicer

  1. Click here to open the file PivotTable Advanced Functions.xls file.
  2. Review the Sales sheet. This is the source of our PivotTable data.
  3. Click the Summary Sheet. A PivotTable has already been inserted, with the dates of Weekly Sales displayed in Rows, and a Slicer for the Category field applied.
  4. Select one of the cells containing data (e.g. A4).
  5. On the Ribbon, on the PIVOTTABLE TOOLS: ANALYZE tab, in the Filters group, click Insert Timeline. The Insert Timelines dialog appears.
  6. Check Weekly Sales and click OK. The Weekly Sales Timeline Slicer appears.
askq_timeslicers2
Weekly Sales Timeline Slicer

Working with Timeline Slicers

  • CLICK or CLICK+ DRAG on the timescale to filter timeline.
  • To change the scale, click the Level drop-down, at right, and select Years, Quarters, Months, or Days.
  • To quickly format, select a style from  the Timeline Styles on the TIMELINE TOOLS: OPTIONS  tab of the Ribbon.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!

Excel Advanced Filter

   

So you’ve mastered the FILTER tool (aka, ‘Filter-in-place’). Are you ready to step up to ADVANCED FILTERS?

Although the Filter-in-place tool is easy to use, it does not handle multiple column INCLUSIVE criteria very well.

Using the ADVANCED FILTER you can:

  • Copy filtered records to another part of the spreadsheet, leaving the original database undisturbed.
  • Generate a BOOLEAN OR (aka, inclusive) condition across multiple fields.

What does that that last part mean? Let’s take the following scenario.

The last time Russia played host to the Winter Games was in 1994.  In preparing for the 2014 Olympics , you are tasked to create of separate list that includes:

  • ALL RUSSIAN MEDAL WINNERS (any year), and
  • 1994 GOLD MEDALISTS, in the WINTER OLYMPICS, of any nationality.

An Olympic feat (pun intended)? Not for ADVANCED FILTER!

(cue the Olympic music

Performing the ADVANCED FILTER requires a little preparation. You will need to prepare a CRITERIA RANGE to ask the question, and an OUTPUT RANGE to display the result. These ranges must be be in a separate area of the same worksheet  preferably to the right of the original.

Click here to download the 2014 Olympic Data file, then execute the instructions below.

Preparing the Criteria and Output Ranges

  1. Select and copy the column headings in cells A1:G1.
  2. Paste the headings, starting in cell J1 and again in cell J6.
  3. In cell L2 type ‘RUS’
  4. On the following row, in the appropriate cells, type ‘1994‘ ( J3), ‘Gold‘ (O3) and ‘Winter‘ (P3).
Preparing the Criteria and Output ranges
(click to zoom)

Performing the Advanced Filter

  1. Place cursor on one of the cells from the original database (e.g. B4).
  2. On the the Data tab of the Ribbon, in the Sort and Filter group, click Advanced. The Advanced Filter dialog will appear.
  3. For Action select Copy to another location.
  4. Review the List Range. If necessary correct the reference to include the source data.
  5. In the Criteria Range field type or select cells J1:P3.
  6. In the Copy To field, type or select the Output Range header, cells J6:P6.
  7. Click OK.

Voilà! The records that satisfy these criteria are copied to the Output range, and you get a virtual laurel wreath!

Closing Ceremony Notes:

  • Our criteria range included the headings and two additional rows. Each OR (inclusive) condition is entered on a separate row. Although our criteria uses data from 4 columns, there are only two separate, inclusive criteria; (1) RUSSIAN OR (2) GOLD MEDALIST in YEAR 1994. Were additional inclusive criteria necessary (e.g., Gender)  the criteria range in Step 5 would need to be increased, accordingly.
  • The copied data is not linked to the original. Edits to the original source data do not affect the copied output. Should you edit the source data you need only re-run the Advance Filter to synchronize data.
  • Do not put anything below the Output Range headers that you intend to keep; Excel erases all the rows beneath prior creating the output.

PivotTables & Slicers

Want a quick introduction to PivotTables? Watch this 10 minute video. I demonstrate the basic features of PivotTables and feature Slicers, new to Excel 2010. And for more, view the earlier post: PivotTable Intro, Step by Step.

Excel Speaks!

Here’s another cool feature of Excel: Speak Cells on Enter.

Speak Cells on Enter

This can prove valuable as a means to verify accurate data entry. The only setup required is to add a button to your Quick Action Toolbar (QAT).

To setup:

  1. RIGHT + CLICK on the QAT and select Customize Quick Access Toolbar.
  2. Set the Choose commands from drop-down to All commands, then scroll down and select Speak Cells on Enter.
  3. Click Add, then OK.

The button now appears on your QAT. Click button to toggle the feature on or off.

When active, each time you enter in a cell, the cell contents will be read back to you. Unlike other reader programs this voice is clear and rather pleasant (take note Acrobat).

Now if only you could select the voice,  I’d take something along the lines of a HAL 9000, or K.I.T.T. model.

Additional voice features of Excel include:

  • Speak Cells
  • Speak Cells – Stop Speaking Cells
  • Speak Cells by Columns
  • Speak Cells by Rows
  • Stop Listening to Voices in My Head*

*available only to select consumers. What, I am not one of them? I am so! You keep out of this.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!

PivotTable Introduction: Step by Step

Concept

PivotTables are one of the most powerful tools in Excel’s data management arsenal. Surprisingly, it is also one of the most overlooked tools. A PivotTable enables the reviewer to statistically analyze data in various flexible formats. By pivoting bits of data into place you are able to create new views of worksheet data in seconds.

Figure 1 PivotTable report example

Preparation:

To create a PivotTable you must start with a database. For Excel’s purposes a database can be defined as:

A table, where the first row contains the column headings (field names), each row contains data, and is devoid of blank rows or columns.

First, review your data and, if necessary cleanup the data. This will include deleting unnecessary blank rows. It may also include inserting blank rows between titles and summaries.

Execution

Click here to open ‘PivotTable Intro.XLS ‘ and follow below instructions.

To insert a PivotTable report

  1. Place cursor on one of the data values of the table.
  2. On the Insert tab, click PivotTable. The Create PivotTable dialog appears.
  3. Confirm the settings and click OK. A new sheet is displayed, and the PivotTable Field List pane appears at right.
  4. Drag the fields in the pane into the appropriate area at bottom.
  • Row: Region, Product
  • Column: Market
  • Value: Quantity

To update the PivotTable

After editing the original database, you must manually update the PivotTable to reflect those editions.

  1. Click on one of the values in the PivotTable.
  2. On the Analyze tab, in the Data group, click Refresh.

Tip Double + Click on a PivotTable value to Quick query the data. Excel will copy the supporting records onto a new sheet for your review. This data is not dynamic. To prevent unintentional editing it is recommended that you delete this sheet, following your review.

Slicers

Slicers, new to version 2010,  enable you to filter select records to display. In earlier versions of Excel, this is accomplished using Filters. 

To Filter the PivotTable report using Slicers

  1. Click on one of the values in the PivotTable, to display the PivotTable Ribbon tools.
  2. On the Analyze tab, in the Filter group, click Insert Slicer. The Insert Slicers dialog appears.
  3. Check the field(s) you wish to create filters for and click OK. The Slicer pane(s) is added to the worksheet.
  4. Click on item(s) in the Slicer pane to display only those records.

Review

  • When generating a PivotTable, the source data must be in a ‘clean’ table.
  • Slicers can be used to enhance the report layout.
  • Double + Click a data point to quickly query the data.

Office 2010: Mark as Final

Here’s a quick Office tip that applies to Word, Excel and PowerPoint.

MarkAsFinal

The Mark as Final feature enables you to protect a document to discourage editing. This simple seal of protection can easily be removed by the reader, should it be determined editing is necessary.

Note, this option is not designed to prevent edits, only to ward against unintentional editing. To render the document un-editable use other alternatives (for example, saving the file password protected or distributing a PDF version of the file).

To Apply Mark as Final

  1. On the File tab, scroll down to Info, click Protect and select Mark as Final. A dialog will appear indicating “the file will be marked as final and saved.”
  2. Click OK to confirm.

When backstage view is active, a notice appears in the status bar, indicating, “An author has marked this … as final to discourage editing.”  The Application title bar also indicates that the file is Read-only.  Reading, printing, and viewing options continue to function, but all editing features are disabled.

To remove the Mark as Final setting and restore edit functions repeat step 1, above. Alternatively, you can click the Edit Anyway button displayed on the info bar in the backstage view .

Cheers!
hɔuᴉnb

Comments and questions are always welcome!

Excel: Using Conditional Formatting to Highlight an Entire Row

Conditional Formatting a Row

Conditional Formatting makes it easy to visually highlight cells, based upon conditions (criteria) that you set. The conditional formats are dynamic, so as the data is edited, the criteria is tested, and the formats reapplied.

In a prior post, I mentioned how you can format an entire row or record based upon criteria found in one of that row’s fields.  Here’s the step by step example, using the Charity Guest List data, used in the prior post.

Let’s demonstrate the COUNTIF using the first scenario. Count the number of guests who have donated more than $100

CountIF..click to zoom in
Rows conditionally formatted gold where donation is greater than or equal to $100

The intent is to format gold, those rows where the value in column D is $100 or greater.

A copy of this spreadsheet can be found hereon Google Drive. The file will open in a browser window/tab, in view mode. Click CTRL + S (PC) or select FILE, SAVE AS to download the file.  

  1. Open the file in Excel.
  2. Select the donation data, cells A3:D15.
  3. On the Home tab, in the Styles group, click the Conditional Formatting drop-down and select New Rule. The New Formatting Rule dialog will appear.
  4. Select Use a formula to determine which cells to format.
  5. In the Edit the Rule Description section, click in the Format values where this formula is true, field, then type or select the first criteria value, the donation in cell D3, followed by the criteria, ‘>=100’ (no quotes).
    Note if using your mouse to select the cell, Excel will add ‘$’ to indicate absolute references. It is important that you remove the absolute reference indicator before the row number.
  6. Click the Format button. The Format Cells dialog appears.
  7. Click Fill tab, and select a Background Color.
  8. Click OK. The dialog should look like this:
    Conditional Format Dialog
  9. Click OK.

A few more notes on Conditional formats.

  • To edit or delete the conditional formats: On the Home tab, in the Styles group, click Conditional Formatting and select Manage Rules.
  • You can create multiple rules and order how they should be applied.

Experiment: change some contributions in the spreadsheet and see how the conditional formatting reformats the row.

Cheers!
hɔuᴉnb

Comments and questions are always welcome!