DARTS : Excel Tips

Excel files from Discoverer

When outputting reports from Discoverer into Excel, be aware that apparently blank fields do, in fact, contain SOMETHING.  It is not relevant data and it is not visible, but it is able to be selected. To remove this unwanted data (which Excel WILL include in counts):

1. Select a "blank" cell and copy (Ctrl+c)
2. Do a find (Ctrl+f) and choose the replace tab
3. Paste the contents (Ctrl+v) into the "Find what" field and leave the "replace" field blank
4. Click "Replace All"

Exporting Discoverer Query results and retaining most of the formatting.

File > Export  (NOT Export to Excel)

• Choose Table Format of htm
• Choose desired Destination
• Choose desired File Name

Once exported then open Excel and browse for the file. It will not open if you click on it. Save as an Excel workbook.

Do not worry if you get a warning message when you save the file. Simply hit Continue to save the file after the warning message appears.

Make sure all number formats do not contain <1,000,000> to represent minus numbers.

Highlight duplicate values

Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Opening CSV files in Excel from any tool

• When opening in Excel directly, leading zeroes are deleted from fields such as EMPLID.  Adding them back causes them to be read-only.
• To ensure the leading zeroes are functional, follow these instructions:
• Open Excel
• Select a new blank workbook
• From the "Data" tab at the top, select Select External Data From Text.
• Browse for your desired .csv and click "Import."  The following screen appears.  Make sure delimited is selected and click to include headers.  Click" Next."

• Deselect "Tab" and select "Comma."  You will notice the Data preview will change to columns. Click "Next."

• In the next step, the first column field will be highlighted.  Click on the field header that should contain the leading zeroes and click on "Text." 

• In the final box, click "OK."

Easily convert xls to xlsx

When downloading Excel files from PS, you note that they are all xls format.  You can convert the file without creating a second file of the same name.

Go to the File tab.  This brings you to a new page.  The very top icon says "Convert." Click it and it will change your xls to an xlsx file.

Creating Subtotals in Excel

Click this link to review the subtotal function in Excel.

Creating Pivot Tables

Excel 2013 - Pivot Tables Training

• A. Getting Started

• • What you'll learn in this training

  • Understanding the benefits of a Pivot Table

  • Understanding Pivot Table terminology

  • Selecting appropriate data for a Pivot Table

• B. Creating & Deleting

  • Working without a Pivot Table Wizard

  • Using the new Quick Analysis tool

  • Laying out a Pivot Table manually

  • Using the new Recommended Pivot Table feature

  • Using the classic Pivot Table layout in the grid

  • Selecting different source data

  • Deleting a Pivot Table

• C. Custom Appearance & Layout

  • Adding and removing fields from a Pivot Table

  • Adding and using the page field

  • Changing the data area number format

  • Changing how empty cells are displayed

  • Formatting a Pivot Table

  • Sorting a Pivot Table

  • Viewing data detail for an item

  • Viewing detail for a data cell

  • Excluding row or column items from the data summary

  • Grouping Pivot Tables by dates

• D. Printing Options & Copying a Pivot Table

  • Printing a Pivot Table with column & row field

  • Inserting page breaks after each row item

  • Copying a Pivot Table

• E. Summarizing with Totals & Formulas

  • Displaying grand totals

  • Changing the Data Summary function

  • Creating a calculated field

  • Inserting a calculated item

  • Listing the formulas used in a Pivot Table

• F. Refreshing Data

  • Refreshing Pivot Table data

  • Refreshing data at specific time intervals

• G. Pivot Charts

  • Creating a Pivot Chart from an existing table

  • Creating a Pivot Chart from scratch

  • Changing the layout of a Pivot Chart

  • Converting a Pivot Chart to a static chart

• H. Sparklines and Slicers

  • Using Sparklines and Slicers

  • Creating Sparklines

  • Changing the Sparkline type

  • Formatting Sparklines

  • Viewing the parts of a Slicer

  • Creating a Slicer

  • Formatting a Slicer

Change the source data for a PivotTable

After you create a PivotTable, you can change the range of its source data. For example, you can expand the source data to include more rows of data. However, if the source data has been changed substantially—such as having more or fewer columns, consider creating a new PivotTable.

You can change the data source of a PivotTable to a different Excel table or a cell range, or change to a different external data source.