Date & currency formatting

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

Hello, wonderful people. In this video, we're going to clean up our dates. At the moment, we've got kind of a shortest version with 4 digits for the year, there's ones with 2 digits for the year. There's some long words, with the full word 'October' there, we need to clean all that up.

Another thing is, we'll add our dollar signs to this column here because it's our money. And it will look like this dollar signs, all consistent dates. So let's go and do that in this video.

Dates can be a bit of a problem, right? We've imported this data from who knows where. And some of the entries are in this format where it's got the written word 'May'. Some of them have got the days, months, year in 4 digits. Some of them have got them in just 2 digits. We need to get some sort of consistency. Doesn't really matter if the month is first, or the day is first. It's the same process.

So what we want to do is-- you might be lucky, and they might import, and all you have to do is select on it. And go up to here, and say, I want to go from long date to short date, and it might work. You might have lots of problems. Other problems where you've got other values, where I say, "Yes, let's just be the long date," and it just doesn't change. So we need to do something slightly more manual.

So you might have to do the easy way, and just go and change it, or this option here. So I'm going to select this one. Hold 'Shift' down on my keyboard, and select the last option. And then in here, I'm going to go to 'Data'. I'm going to go to the one that says 'Text to Columns'. It's reasonably easy. Leave it as 'Delimited', and hit 'Next'. Doesn't matter what this is, click 'Next'. And you're looking for this one here, say, I want dates to be day, month, year, 'DMY'. If you are in America, you might go month, day, year, ‘MDY', doesn't really matter. It's just going to force all these guys to be at least text that we can adjust. Hit 'Finish'.

And something kind of changes, you can see, nothing really happens, except now, when I go to 'Home'

and I go to 'Format', ‘Date', 'Short Date', they are all changing. And when I go to 'Long Date', they all become long dates. I'll scale this up, so you can see them all. So you might have to do it the quick easy way by just picking up here, 'Short' or 'Long' dates, or you might have to do my little trick here, go to 'Date'., and then, this 'Text to Column' option. Cool. Nobody likes long dates. And we go back to short dates.

And do our currency, so, back to here. Back to 'General'. 'Short Date'. Great. I'll make it a bit smaller again. Next thing we're going to do is this one. This one here is missing all the currency. So that's '4100'.

I'm just going to select this whole column by clicking 'D'. Up here, I'm going to pick 'Currency'. There we are. It's picked pounds '£' by default. You can go into here, and pick 'More Number Formats'. 'Currency', and I want not the £, I want English, doesn't really matter, all those symbols, we'll go to New Zealand.We love New Zealand. We set the dollar signs to it there. Lovely.

One thing you might also do is, can you see, it's added these '.00' to the end? That might be useful foryou if you've got change. In my case, just adds more data that I don't need. Makes it a little bit more confusing.

So I'm going to select the whole column again. And over here, under 'Home', just underneath, where wegot 'Currency' here, you can decide how many decimal points you want to show. You can show more, in our case we want to show less. Just down to the nearest dollar. Lovely, let's hit 'Save'.

And that my friends is how to format dates and currency formats, and other stuff. Let's look at cleaning up more of our table here in the next video.

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