Managing List Based Data in Excel
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: Mike Thomas
- Topic: Software, Business Administration, Business Skills, Finance, Taxation and Accounting
- Credit: ATAAA 1.5, ATAOP 1.5, ATATX 1.5, CPE 2.0
-
If you think that Excel is all about working with numbers, then think again. More and more people are turning to Excel to help them to manage list-based data.
Whether it's lists of employees, lists of equipment, lists of training courses or anything else, Excel contains a whole array of features designed to help you to manage your list-based data. It’s also more than likely that those lists will originate outside Excel (in a database for example)
Why you should attend:
If you manage list-based data in Excel and want to learn some best practice techniques for working with this type of data, this session is for you.
Learning Objectives:
- Importing list-based data into Excel from a variety of external sources
- Sorting a list of items into alphabetical, numerical or chronological order
- Using List Filters to display only those items matching a given criteria
- Saving a Filter for re-use
- Use Flash Fill to quickly create a list
- Convert a single-column list into multiple columns
- Remove duplicate values from a list
- Converting a list into a Table – the why and how
Who should attend
There's nothing particularly advanced about the features covered in this session so even if all you've ever done is opened an existing Excel workbook and looked at the data, if you want to know how to manage lists in Excel, then this webinar is for you.
Although the training will be delivered using the latest version of Excel, this training is relevant for users of Excel 2010 and above.
- Introduction
- Sorting a List 00:03:49
- Sorting By Row 00:04:26
- Filtering 00:04:30
- Tables 00:04:59
- Pivot Tables 00:05:39
- Sorting a List 00:06:33
- Sorting Mulitple Columns 00:09:45
- Sort By Month 00:13:49
- Sort By Row 00:15:34
- Filtering - AutoFilter 00:21:19
- Filtering - Filter By Color 00:30:14
- Tables - Create A Table 00:38:57
- Tables - Chart Based On Fixed Range 00:48:06
- Tables - Chart Based On Table 00:49:40
- Tables - Formulas 00:52:09
- Tabular Data 01:00:15
- Order Details Text File 01:06:04
- Moving Data From A Text File To An Excel File 01:08:10
- Pivot Tables - Sales Data 01:20:06
- Pivot Tables - By Flavor 01:29:26:14
- Pivot Tables - By State 01:30:04
- Pivot Tables - By Month 01:30:27
- Pivot Tables - Running Total 01:30:55
- Pivot Tables - Difference 01:31:12
- Pivot Tables - Sales Data - Building a Pivot Table 01:31:28
- How To Create a Chart From Pivot Table Data 01:36:50
- How To Use a Slicer 01:38:39
- Presentation Closing 01:41:02
- Cell 00:52:35, 01:37:43
- Column 00:10:41, 00:31:17, 00:39:27
- Column Headings 00:08:17, 01:00:41
- Custom Sort 00:20:06
- Custom Views 00:34:52
- Filter 00:04:30, 00:22:29, 0:31:11
- Formula 00:52:12
- Pivot Tables 00:05:39, 00:26:51, 01:20:06, 01:25:49, 01:31:28
- Row 00:15:39, 00:39:27
- Running Total 01:30:57
- Slicer 01:38:39
- Tabular Form 00:05:26
Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.
Custom Sort: Doing a custom sort allows you to specify the order in which your list will be sorted in - like if you want to sort in a non alphabetical order. You may want to do a custom sort to sort items in an order that is not alphabetical or numerical.
Custom Views: This feature stores a snapshot of the hidden/visible status of columns, rows, and worksheets, along with print settings and filter settings.
Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.
Formula: A formula is an expression which calculates the value of a cell.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
Running Total : A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total. Another term for it is partial sum. The purposes of a running total are twofold.
Slicer: You can insert slicers in Excel to quickly and easily filter pivot tables. Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click
Tabular Form: In Tabular Form, each Row field is in a separate column, as you can see in the pivot table below. There are two Row fields -- Customer and Date. The Row labels are not in a separate row.
