Oz du Soleil shows how to query data from a table or range in Get & Transform in Excel 2016.
- [Instructor] Get & Transform uses queries as its source. Therefore, to use Get & Transform, we need to create a query. Here we have orders about pies, and in column D, there are holes in the columns, so we need to get those filled in, and it's best to do that in Get & Transform. And this data is in a range right now. It is not in table format. It's just data typed into cells. So let's click inside that data range, go to Data, From Table.
See, Excel is guessing where our dataset is, and it's guessed correctly. Notice the marching ants are covering the whole range, and we can say OK. There is our data. Now let's close and load this to the workbook. Now let's look at another way that this data can be set up. It might look like this. Notice that there is an empty column at F.
Let's click inside the dataset, Data, From Table. Notice that it is not guessing the dataset correctly. So let's cancel that out. Now we can manually put this into a table. Let's do that, highlight, Home, Format as Table, Table does have headers, OK. Now it's made that up, but we can get rid of that manually here in Excel, or we can get rid of it in Get & Transform, but the point is we can start a summary this way.
We can go to Data, From Table, and there's our data, we can remove that column, and then do our fill down to close that gap. As you move forward and get to know Get & Transform more and more, the typical way that you'll start a query is from a table or a range.
AuthorOz du Soleil
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins
Skill Level Intermediate
1. What Is Get & Transform?
2. Working with Queries
3. Working with Columns
4. Working with Formulas
5. Pivoting and Unpivoting Data
Group data2m 31s
7. Appending a Query
8. Merging Data with Joins
9. Putting It All Together and Applying Your Learning
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.