MyGPS has a wealth of reports that can be exported to Excel. Below are several quick tips to increase the readability and relevance of the data.

Are you a Google Docs user? Click here for Formatting Google Sheets Reports For Readability

To see these tricks in action, watch the video at the bottom of this post.

  1. Select All button
  2. Resize columns and rows or autofit
  3. Wrap text
  4. Delete rows or columns
  5. Freeze rows or columns
  6. Find & Replace
  7. Sort & Filter
  8. Conditional formatting

1. Select All

screen_shot_2016-10-05_at_2_38_52_pm

Figure 1

To quickly change the font, font size, font color, etc., select the entire table by clicking the corner Select All button (or Command + A). This allows you to make whole-sheet formatting changes.

2. Resize Columns and Rows or Autofit

  1. Hover your cursor between the column letters (or row numbers) to get a double arrow cursor. Click and drag to resize the column (or row) or double-click to auto-fit the column (or row) to the longest entry.
    imgres-1

    Figure 2

  2. To resize all columns at once, select the columns (or rows) and from the Home tab, click Format > Autofit Column Width (or Row Height). For wide columns with long text, right click on the column letter and choose resize. Set the width to 2 (you can always drag it wider)
    screen_shot_2016-10-05_at_3_35_51_pm

    Figure 3

3. Wrap Text

  • Wrapping text will allow long sentences or paragraphs of text in a cell to be read by increasing the row height while leaving the column width the same.
  • Select the column and resize it to a reasonable width (directions in #2 above).
  • From the Home tab click Wrap Text. The text will wrap within the cell by increasing the row width.
    screen-shot-2016-10-05-at-3-33-56-pm

    Figure 4

4. Delete or Insert rows or columns

  • Select a row (or column), right click, and choose Delete. Alternatively, from the Home tab click on Delete > Delete Sheet Rows (or columns)
  • To insert a row or column, select the column to the right or row below and click Insert > Insert Sheet Rows (or columns)

5. Freezing Rows & Columns

When working with long spreadsheets, it is helpful to keep the top header row or first column visible at all times, even when scrolling. From the View tab, click on Freeze Top Row and/or Freeze First Column.

6. Find & Replace

You can search for and replace text, including special characters (such as question marks, tildes, and asterisks) or numbers. To remove rather than replace, press the space bar in the replace box. You can search by rows and columns, search within comments or values, and search within worksheets or entire workbooks.

  1. Select the range of cells that you want to search. If you want to search the whole sheet, click any cell.
  2. In the search field Search box , type the text or number that you want to find.
  3. Press Return; the cell with the search term will be selected. Every time you press return the next cell with the search term will be selected.
  4. Click the arrow next to the magnifying glass to search in the Sheet or Workbook.
  5. Choose Advanced Search or Replace to get the advanced search window.
    screen-shot-2016-10-11-at-4-18-01-pm

    Figure 5

  6. In the Replace with box, type the replacement characters. To replace the characters in the Find what box with nothing, leave the Replace with box blank.
  7. Click Find Next.
  8. To replace the highlighted occurrence, click Replace. To replace all occurrences of the characters in the sheet without reviewing them first, click Replace All.
Source Microsoft: https://goo.gl/LLcwkh for more details.

7. Sort & Filter

Sort a column

  1. Click a cell in one of the columns that you want to sort.Data in adjacent columns will be sorted based on the data in the column that you selected.
  2. On the Data tab, select Ascending Ascending sort icon or Descending Descending sort icon .

Sort a list by two or three columns

You can sort by several columns by adding levels of sorting criteria. For example, you could sort a sales report by region, then by date, and then by salesperson. Each sort level is represented by a single row in the Sort dialog box.

  1. Click a cell in one of the columns that you want to sort.
  2. On the Data tab, select Sort.On the Excel Data tab, select Sort
  3. If your data has a header row, select the My list has headers check box. But if the data does not have a header row, clear the My list has headers check box.
  4. In the row next to Sort by, under Column, click the blank space, and then click the column that you want to sort by.
  5. Under Sort On in the same row, click Values, and then on the shortcut menu, click the criteria that you want.You can also choose to sort based on cell or font color, or on the icon in a cell.
  6. Under Order in the same row, click A to Z, and then on the shortcut menu, click the criteria that you want.
  7. If you selected Cell Color, Font Color, or Cell Icon in step 5, then under Color/Icon, click the row, and then on the shortcut menu, click the criteria that you want.
  8. For each additional column you want to sort by, click Add Level.Add level iconThen fill in the Column, Sort On, Order, and Color/Icon columns for the new row.
    Figure 1

    Figure 6

Source Microsoft: https://goo.gl/FyLoVt for more details.

Filter Columns

When you filter a list, you temporarily hide some of your content. Filters provide a quick way to find and work with a subset of data in a range or table.

  1. On the Data tab, click Filter. Filter adds a drop down arrow to the top of every column.On the Data tab, select Filter
  2. Click the arrow Arrow showing that column is filtered in the column that contains the content that you want to filter.
  3. Under Filter, click Choose One, and then enter your filter criteria.
    In the Filter box, select Choose One

    Figure 7

Notes

  • You can apply filters to only one range of cells on a sheet at a time.
  • When you apply a filter to a column, the only filters available for other columns are the values visible in the currently filtered range.
Source: Microsoft https://goo.gl/Nxp7s5 for more details.

8. Conditional Formatting

Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition. Or you can format a whole cell range and vary the exact format as the value of each cell varies.

Below are 2 examples of conditional formatting.

  1. Apply conditional formatting to text
    • Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
    • On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Text that Contains.
      screen_shot_2016-10-12_at_8_06_11_am

      Figure 8

    • In the pop-up menu, in the box next to Containing, type the text that you want to highlight. Choose the type of formatting by clicking Format with: and then click OK.
  2. Format only unique or duplicate cells
    • Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.
    • On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values.
    • In the pop-up menu, click unique or duplicate. Choose the type of formatting by clicking Format with: and then click OK.
Source: Microsoft https://goo.gl/LLcwkh for more details.


Print this article