From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Microsoft 365 Apps
From the course: Cert Prep: Excel Expert - Microsoft Office Specialist for Microsoft 365 Apps
Start my 1-month free trial
Fill cells using Flash Fill
“
- [Instructor] Have you ever had to combine information from two or more cells into a brand new column, such as combining a first name, a middle initial, and a last name? Well, you may have used concatenate to do that or you may have just retyped all the information, but I want to show you how to use Flash Fill to make your life so much easier and let Excel do all the work. Let's go ahead and go into our document, 03_01 FlashFill, and I'll show you first how we can combine those first and last names; Contact First, Contact Last. I'm going to type in Quail Austin, hit Enter, and now Kamal Duncan. Notice how the ghosted text underneath Kamal's name is showing me how it's going to auto-complete that. I'm going to go ahead and hit Enter. Perfect, that was quick, wasn't it? Now I can put in their first and last initials: QA, KD. I see the ghosted text, but it disappeared, didn't it? I'll type a couple more in, and then what we're going to do is select what I typed in all the way down to the last row, and we're going to go up to Fill and Flash Fill. Now, sometimes you need to troubleshoot Flash Fill, and this is how you can kind of give it a kick to complete the filling of all of the cells for you. We can do the same with work phones. We can put hyphens in if needed. We can do a company and type. So the first company is Zino Letti's with a forward slash, and it's a restaurant. I'll use my Enter key and we'll type in Flavorville, and notice how if I just hit Enter, it completes that pattern for me. Now let's look at how we can break information apart. On our customer addresses worksheet we have the full address. Well, let's just pull out the street address first. 222 Dakota, and all I have to do is hit Enter. Now if we want the cities, Des Moines, and Enter. So that's how you can quickly break the information apart using Flash Fill. Really a very, very awesome tool that will be a huge time saver for you. And you may come across a task or two on the exam asking you to use Flash Fill to either pull information together or break it apart into separate columns, and now you know how to do that.
Contents
-
- The different kinds of Microsoft Office Specialist certifications 3m 10s
- Prepare for the test 6m 20s
-
- Manage macros 6m 43s
- Reference data in other workbooks 7m 15s
- Manage workbook versions 5m 12s
- Protect workbooks and worksheets 7m 20s
- Configure formula calculation options 3m 22s
- Challenge: Manage workbook options and settings 1m 7s
- Solution: Manage workbook options and settings 7m 37s
-
- Fill cells using Flash Fill 2m 45s
- Fill cells using advanced Fill Series options 5m 47s
- Create custom number formats 6m 41s
- Configure data validation 7m 1s
- Group and ungroup, use subtotals and totals 4m 51s
- Remove duplicate records 3m 14s
- Create and manage custom conditional formatting rules 8m 20s
- Create conditional formatting rules using formulas 7m 29s
- Generate numeric data by using RANDARRAY() 6m 26s
- Challenge: Manage and format data 33s
- Solution: Manage and format data 11m 24s
-
- Use AND(), OR(), and NOT() in nested functions 4m 56s
- Use IF() and IFS() in nested functions 5m 43s
- Use SWITCH() in nested functions 4m 23s
- Use SUMIF(), AVERAGEIF(), SUMIFS(), and AVERAGEIFS() functions 4m 43s
- Use COUNTIF(), COUNTIFS(), MAXIFS, and MINIFS 5m 51s
- Look up data using XLOOKUP(), VLOOKUP(), HLOOKUP(), INDEX(), and MATCH() 7m 19s
- Use NOW() and TODAY() functions and calculate dates using WEEKDAY() and WORKDAY() functions 8m 31s
- Summarize data from multiple ranges by using the Consolidate feature 4m 46s
- Use What-If Analysis, Goal Seek, and Scenario Manager 5m 56s
- Calculate financial data with NPER() and PMT() functions 4m 19s
- Use the SORTBY() and FILTER() functions 4m 30s
- Troubleshoot formulas 8m 16s
- Challenge: Create advanced formulas 30s
- Solution: Create advanced formulas 14m 31s
-
- Create and modify dual-axis and combo charts 4m 16s
- Create and modify box and whisker and funnel charts 2m 48s
- Create and modify Sunburst, Histogram, and Waterfall charts 6m 45s
- Create PivotTables and modify field selections and options 6m 47s
- Create and modify PivotCharts 5m 15s
- Challenge: Manage advanced charts and tables 28s
- Solution: Manage advanced charts and tables 5m 23s
-
- Challenge: Full-length MOS Excel Expert sample exam 45s
- Solution: Full-length MOS Excel Expert sample exam 20m 43s
