Splitting cell contents

This lesson is exclusive to members

Questions

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

Course info

24 lessons / 2 hours

Overview

Hi there, Welcome to this Microsoft Excel BootCamp. Together we’re going to learn how helpful Excel is in nearly every part of our professional lives.

This course is for beginners. You do not need any previous knowledge of Excel. We will stick closely to the powerful built in features of Excel and will not get bogged down in confusing code & complicated formulae.

This training course is project based. We start with a simple company branded invoice and explain how to calculate totals & tax. Using a complex and messy spreadsheet we will clean it up using Excels automatic features. With our new tidy data you’ll learn how easy pivot tables can turn long and hard to understand information into simple tables & beautiful graphs. Before you’re finished you’ll be making helpful drop down menus to help you fill out & sort your financial data. . You will learn how to turn uninspiring profit & loss statements into a good looking, easy to use documents. 


Class projects:

  • Create a quote & invoicing form.

  • Cleaning & formatting messy imported data.

  • Inventory spreadsheet.

  • Pivot tables

  • Regional Sales Report

  • Profit & loss spreadsheet.

  • GST & Tax calculations

  • Graphs for use in Word, PowerPoint, InDesign & Illustrator

  • Creating spreadsheets that work within Word documents.

Who should attend?

  • This course is designed for people who have little or no previous experience in Microsoft Excel. You will start right at the beginning and cover all the basics.

  • Only basic computing skills are necessary - if you can send emails and surf the internet then you’ve found the right training.

  • By the end of the course, you will be producing real world results with Excel.

What do you need?

  • No previous Microsoft Excel experience necessary.

  • You'll need Excel 2016 installed on your laptop. The standard installation of Excel 2016 or the Microsoft Office 365 version is fine.

Course duration 2 hours

Daniel Scott

Daniel Scott

Founder of Bring Your Own Laptop & Chief Instructor

instructor

I discovered the world of design as an art student when I stumbled upon a lab full of green & blue iMac G3’s. My initial curiosity around using the computer to create ‘art’ developed into a full-blown passion, eventually leading me to become a digital designer and founder of Bring Your Own Laptop.

Sharing and teaching are a huge part of who I am. As a certified Adobe instructor, I've had the honor of winning multiple Adobe teaching awards at their annual MAX conference. I see Bring Your Own Laptop as the supportive community I wished for when I was first starting out and intimidated by design. Through teaching, I hope to bring others along for the ride and empower my students to bring their stories, labors of love, and art into the world.
True to my Kiwi roots, I've lived in many places, and currently, I reside in Ireland with my wife and kids.

Downloads & Exercise files

Download Exercise Files Download Completed Files

Transcript

Hey there, my name is Dan. In this video we're going to split our names here, they're kind of bunched together in one cell. And presto, turn them into our two column thing. Super easy, let's go and do that now.

There's two ways of doing it, there's the regular way, it's called 'Text to Columns', pretty easy. And there's another one called 'Flash Fill'. I'm going to show you 'Flash Fill' afterwards because I'll introduce this 'Flash Fill' to you. I like to say 'Flash Fill' loads. And in fact, 'Flash Fill' has so many other kind of cool uses. So let's do the first one.

What I want to do is, I want to split the first name and last name. I'm going to select this column here. Actually, first of all, I want to separate them into two columns. I haven't got a second column here, so I'm going to right click 'B'. And go to 'Insert', so I've got a nice blank one here. I'm going to select 'A', and I'm going to make sure I'm on my 'Data' tab, on top here. And there's one that says 'Text to Columns'. 'Delimited', click 'Next'.

What spaces, what delineates these two words? In my case it's a space between them. You might have maybe a comma between them. Mine's a space, you can kind of tell down here, that little preview. Pick whatever separates them out. Mine's space, I'm going to click 'Next'. Then I'm going to click 'Finish', and magic. You can see, it's separated my 'First Name' and 'Last Name'.

That could be cool if you've got, maybe cities, states, and post codes as you can separate them all out.They've got spaces between them. So that's one thing, it works good. What I want to do though is I'm going to 'undo' that. I'm going to insert another 'column' because I want to show you something called 'Flash Fill'. I love it, I love saying it, it does awesome things.

Think of it as, you teach it once what to do, and then it kind of goes "Oh, you probably mean to do this over and over again, and I have learnt." It's pretty cool.

So what you do is-- I've got 'Cynthia Wolfe' here. Let's say I want to separate them out. I can do a couple of things at the same time, though I can say, actually, I want it to be 'Cynthia', I'm typing this in. You can see, I'm using title case so I'm putting 'Cynthia' in there, but I'm using uppers and lowers. And then her name 'Wolfe' here. Nothing really happens, except when I click here, still nothing happens, make sure you're on 'Data'. And click on 'Flash Fill'. And it just kind of guesses what you were doing.

You can see, it's pulled the first name, and it's changed the case at the same time. Same here with 'Wolfe'.And you say 'Flash Fill'. And it goes through and reaches into these ones here. I love 'Flash Fill'.

So there's lots of uses for this when you're re-ordering data. Let's say you want it to be-- still you want it to change the case, but you want it to be like this where they're still together, like they are, but they're re-ordered so their last name is first because it's easier to sort like that. I've switched them around, put a comma in there as well. And underneath it, I go 'Flash Fill'. Cool, huh?

So we reached in, grabbed them, sorted them where I want. And you can do that with any data. Just give it a shot, it works most of the time. Make a column next to it, re-order it the way you want. Next row underneath, and just click 'Flash Fill', and see how it goes. That's it for 'Flash Fill'.

I'm just going to tidy up my columns down, you can skip along to the end. What I want to do is, I need them separated. So I'm going to 'undo', get rid of this one. And I'm going to say I want the first name first, I wanted 'Cynthia'. 'Tab' across, 'Wolfe'. You can't do two of these at once. So you might just select both of these and go to 'Flash Fill'. But you can't click 'Flash Fill' when there's two of them. So you got to do this one, that one.  And what I'll do is, this would be 'First Name', 'Last Name'. And this column here, I'm going to 'Delete'. 'Bold' them up to match the rest of them.

And that will get us on to the next video. Let's hit 'Save'. I will see you in the next video, I promise not to sing the word 'Flash Fill' in any more videos. I've got it out of my system. All right, see you in the next one.

  • Powered by Marvin
  • Terms of use
  • Privacy policy
  • © Bring your Own Laptop Ltd 2024