Learn Excel Now

Conquer the Fear of Excel

Excel Basics: How To Separate Text Into Columns

by

Do you have a list of data that you need to separate into columns in Excel? Maybe you have an address list with the city, state, and zip code all in one column. Or maybe you have a list of product SKUs and want to separate them into individual columns.

Well, before you can start applying all your Excel formulas, you’ll need to get your data all cleaned up. Data is typically cleaned by data engineers but if you’re working with financial data that mostly live within Excel databases, you’ll have to clean it yourself.

In this blog post, I will show you how to use the text to columns wizard in Excel to easily separate text into columns.

I will explain how to use the wizard by both delimiter and fixed width. So whether your data is separated by commas, tabs, or spaces, I will show you how to get it into neat and tidy columns!

Let’s get started!

How Do You Separate Text Into Columns in Excel?

Have you ever tried to filter your data in Excel only to find that all of your data ended up in one column? Before you try out these database tips in Excel, you might want to separate your data into columns first!

It can be frustrating, but luckily there is a tool that can help. The text to columns wizard in Excel allows you to easily separate text into multiple columns.

There are two main ways to use the text to columns wizard: by a delimiter and by fixed width.

If your data is separated by a specific character, such as a comma or tab, then you would use the delimiter option. For example, if your data looks like this:

You would use a comma as your delimiter.

If your data is not separated by a specific character, but you want to split it into columns of equal width, then you would use the fixed width option.

For example, if your data looks like this:

You would use fixed width to split the text into columns.

How To Use The Delimiter Option to Separate Text in Excel

Let’s say you have a list of addresses in one column and you want to separate them into individual columns for the city, state, and zip code. To do this, we would use the delimiter option.

First, highlight the column of data that you want to split. Then, go to the Data tab and click Text to Columns.

In the Convert Text to Columns Wizard, select Delimited and click Next.

On the next page, check the box next to Comma and clear any other boxes that are checked. Then, click Next.

On the next page, you will see a preview of how your data will look when it is split into columns. If everything looks good, click Finish.

And that’s it! Your text should now be split into columns.

End Result

Now that your data is arranged neatly into separate columns, your data format is more suited for further analysis using pivot tables!

How To Use The Fixed Width Option to Separate Text in Excel

If you want to split your text by fixed width, the process is similar. Highlight the column of data that you want to split and go to the Data tab. Click Text to Columns.

In the Convert Text to Columns Wizard, select Fixed Width and click Next.

On the next page, you will see a preview of how your data will look when it is split into columns. Place a break in between each column where you want to split the text. Then, click Next.

On the next page, you will see a preview of how your data will look when it is split into columns. If everything looks good, click Finish.

And that’s it! Your text should now be split into columns.

End Result

I hope this blog post was helpful in showing you how to use the text to columns wizard in Excel. Do consider using this simple but powerful tool in your next data mining or Excel project.

Key Takeaways:

– The text to columns wizard in Excel allows you to easily separate text into multiple columns.

– There are two main ways to use the text to columns wizard: by delimiter and by fixed width.

– If your data is separated by a specific character, such as a comma or tab, then you would use the delimiter option.

– If your data is not separated by a specific character, but you want to split it into columns of equal width, then you would use the fixed width option.

– To use the text to columns wizard, highlight the column of data that you want to split and go to the Data tab. Click Text to Columns. Then, follow the instructions in the wizard.

– Your text should now be split into columns.

Thanks for reading!

Enjoyed this basic tutorial on separating data? Having basic training in Microsoft Excel is important for success in many jobs. If you want to learn more about how to use Excel, check out the other blog posts or sign up for one of our Excel training workshops!

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Author Bio

Austin Chia is the Founder of Any Instructor. A data analytics and Excel enthusiast, he seeks to help others learn more about Excel and anything related to analytics and tech. He has experience as a data analyst and data scientist in healthcare and research.

Excel Pivot Table Tips: Refreshing the Table After Source Data Changes

by

The pivot table in Excel is one of the most vital and versatile tools available. It allows you to look at your data from a wide range of customizable views. In the following guide, we explore how to update the Pivot Table after the source data changes.

There are a variety of reasons you might need to update the pivot table. Maybe you get a weekly report that needs to be added each week. Instead of recreating the pivot table, you can simply refresh it. Maybe there were errors in the source data that needed to be corrected. Again, it’s simpler to refresh than to recreate.

Let’s say you had the following spreadsheet:

Refreshing Pivot Table Image 1

And you created this pivot table for it:

Refreshing Pivot Table Image 2

Then your manager informs you of a price correction on the last 4 items. They went up $50 each. For such a minor correction, it would be a waste of time to create a whole new pivot table. Instead, you will:

  • Make the source data correction
  • Go to the tab with the pivot table
  • Go to the Data tab on the Excel ribbon
  • Select Refresh

Refreshing Pivot Table for New Data

You can also use the keyboard shortcut Alt+F5 to perform this task.

As you can see in the animation above, once you apply the refresh option, the data in the table automatically updates with the source data corrections.

We hope you now feel comfortable making corrections to your pivot table source data and applying the refresh. This is one of many tools available to help you perfect your pivot tables.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Date Calculations Part 1: Finding the Difference Between Two Dates

by

Excel has several built-in date functions you can use to quickly find important information. These are known as Excel date calculations. Today, we will focus on how to find the difference, in number of days, between two different dates. We will also cover how to find a date based on number of days from a given date.

For today’s example, we will use the following spreadsheet outlining due dates for a library:

excel-date-calculations-image-1

Here, we have the due date and the current date. To enter the current date into your spreadsheet, you will use the formula =TODAY(). It will autofill with today’s date.

What we don’t have is the number of days overdue, and the overdue fee. To find that, we will:

Subtract the due date from the current date

Multiply the number of days by the fee per day

excel-date-calculations-gif-1

As you can see, Excel makes it easy to find the exact number of days and then calculate the amount owed. This process involves first finding the number of days and then separately finding the amount due. But you can also build the date calculation right into the fee calculation.

You will use the parenthesis to indicate which function you want performed first (always remember: Excel operates on the Order of Operations).

So, the first part of the calculation will be: =(B3-A3)

Then, tell the formula to also multiply by the amount owed column: =(B3-A3)*D3

excel-date-calculations-gif-2

Finding a Date Based on Number of Days

Let’s say you had the same spreadsheet, except that the original due date was missing, but you still have the number of days. Your job is to find the original due date. This is basically a reverse of finding the number of days overdue. You will enter:

=B3-C3

excel-date-calculations-gif-3

This is how you use Excel to find the number of days between two dates or vice versa. Next week, we will cover additional date functions you will want to know. We hope you found today’s lesson on Excel date calculations useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

 

Excel Data Validation: An Introduction

by

Excel data validation allows you to set specific criteria for the type of data that can be entered into a cell or group of cells. In addition to creating that criteria, you can write a brief message on what can be entered into the cell and error message when the wrong information is entered.

In today’s example, we will use the following customer survey as our example:

data-validation-image-1

Here, we need to set data validation so only whole numbers between 1 and 10 can be entered. To do this, we will:

  • Go to the Data tab
  • Go to Data Validation
  • Open the Data Validation Dialogue box
  • On the Settings tab, set the range (whole numbers between 1 and 10)
  • On the Input message, enter our unique message
  • On the Error Alert tab, enter your custom error message

Once you hit okay, the rules will apply. Here is a demonstration in Excel:

data-validation

As you can see, the only numbers that can be entered are whole numbers between 1 and 10. There are a variety of reasons to use Excel data validation and it depends on the situation and problem you’re trying to solve.

We here at Learn Excel Now hope you found today’s lesson on Excel data validation useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Essentials: Running Spell Check in Excel

by

Excel offers many built-in tools to help you perfect your spreadsheets. Some of these tools, however, need to be activated rather than running automatically. Today, we explore one such tool: running spell check in Excel.

When most people think Excel, the first thing that comes to mind is numbers. But text is often required in Excel to put those numbers into context. This is especially true on any type of report where you might need to add background information, explain your thought process, or just explain where the numbers on the page came from. Even something as simple as column headings, or a client name will require written text.

When submitting reports, you want the spreadsheet to be as accurate as possible and look good. Misspellings and typos simply won’t do. But unlike many Microsoft programs (Word, Outlook, PowerPoint, etc.), Excel doesn’t automatically spell check, giving you those convenient squiggly, red lines when something is wrong. This can make it extremely difficult to catch little errors.

Let’s say you had the following spreadsheet:

excel-spell-check-image-1

Maybe you can recognize all of the typos right away, maybe you can only see a few. The only way to be sure you caught all of the misspelled words is to run Spell Check:

excel-spell-check-gif

As you can see in the above spreadsheet, all of the misspelled words were fixed:

  1. “projcet” was changed to “project”
  2. “prdoucts” was changed to “products”
  3. “incrase” was changed to “increase”
  4. “esitmated” was changed to “estimated”
  5. “retruns” was changed to “returns”

Excel spell check is the perfect tool for verifying accuracy and correcting mistakes when it comes to text in Excel.

We at Learn Excel Now hope you feel comfortable checking the spelling in your text.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

 

Excel Find & Replace: Building Fundamental Skills

by

Find and Replace Featured Image (2)Excel contains tons of built-in tools designed to help you do what you need to. This week we are talking about one of those tools, the robust Find and Replace tool. In the following guide, we will explore how to use this tool and situations where it could be needed.

Simple Find Function

To find something in Excel, you use the simple Find function you use in any other program, Ctrl F. This opens the Find dialogue box where you can enter the text of what you’re looking for. However, Excel contains other built-in options in the dialogue box that allow you to go beyond the simple find function. We will explore those options later in this guide.

The important thing to remember is the keyboard combination Ctrl F at the same time.

Find Dialogue box

Replace What You Found

As you can see in the above image, the dialogue box contains a tab that states “Replace.” Once you found the items you are looking for, you can easily replace them using this tool.

Let’s say you had the following spreadsheet listing what clients each salesperson represents:

Replace Image 1

Now, your boss informs you that Cheryl Myers will be leaving the company. A new employee, Shania Reynolds, will be taking over her sales. Manually replacing Cheryl’s name with Shania’s in each instance would be tedious and time consuming (especially in larger worksheets!). Plus you could always run the risk of missing a location. Luckily, you can use the Replace tool to quickly find and replace all instances in a few clicks:

  • Use the Ctrl F keyboard combination to open the dialogue box
  • On the Find tab, enter the data you want (Cheryl Myers in this instance)
  • Click on Find All to ensure all instances are located
  • Then click on the Replace Tab
  • On the “Replace With” line, enter the new data (Shania Reynolds in this instance)
  • Click on Replace All

As you can see in the following GIF demonstrating these steps, all instances of Cheryl Myers are replaced with Shania Reynolds

Find and Replace GIF 1

Special Formatting for Replacing

The options for Excel’s Find and Replace tool don’t stop there. You can also use the tool to format specific cells. Let’s say your boss wanted you to highlight the new employee’s areas for quick reference. Again, you can do this manually, but it would be a waste of time. Instead, you could:

  • Use Ctrl F to open the dialogue box
  • Enter the data you want to find (Shania Reynolds)
  • Click Find All
  • On the Replace Tab, open the Options dialogue
  • Select Format on the “Replace With” line
  • Go to the tab that says “Fill”
  • Select the color you wish to use for highlighting (yellow)
  • Click “Replace All”

As you can see in the following GIF, the name is still in the cells, but it has been highlighted yellow.

Find and replace GIF 2

We at Learn Excel Now hope you now feel comfortable using the Find and Replace tool. This tool contains other formatting options to change the cells containing data you found. Using the tool is a matter of personal need, but there are many situations where it comes in handy.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter.

Kevin – Learn Excel Now

Sorting and Filtering Data With Excel

by

There are many built-in Excel tools to help with data management and the sorting and filtering features are among the best. The filter tool gives you the ability to filter a column of data within a table to isolate the key components you need. The sorting tool allows you to sort by date, number, alphabetic order and more. In the following example, we will explore the usage of sorting and filtering and show some advanced sorting techniques.

For today’s example, we will use the following spreadsheet:

Sorting & Filtering Image 1

As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques.

Sorting Data

Let’s say you had the spreadsheet above and wanted to sort by price. This process is fairly simple. You can either highlight the whole column or even click on the first cell in the column to get started. Then you will:

  • Right click to open the menu
  • Go down to the Sort option – when hovering over Sort the sub-menu will appear
  • Click on Largest to Smallest
  • Select Expand the selection
  • Click OK

Sorting & Filtering GIf 1

The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data.

Filtering Data

The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company.

To do this using the filter you would:

  • Go to the Data tab on Excel ribbon
  • Select the Filter tool
  • Select Eastern Company from the dropdown menu
  • Select Dylan Rogers from the Salesperson dropdown menu

Sorting & Filtering GIF 2

Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company.

The Sort & Filter Tool

In addition to the right-click menu sorting option and the Filter tool on the Data ribbon, Excel has a Sort & Filter tool that allows for custom sorting.

In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges.

Sorting & Filtering GIF 3

But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data:

Sorting & Filtering GIF 4

As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted!

Use Learn Excel Now to help with all your Excel questions and training needs.  We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more.

Download our free templates and guides

Receive live webinars from industry experts

Train at your own pace with our on-demand training

 

The Excel Fill Handle: How to Quickly Add Data

by

The Excel fill handle is one of the most convenient tools available to enter data quickly and easily. Using the fill handle, you can enter repeating or sequential content across multiple cells at a time.

This quick guide will provide you with how to use the fill handle to fill in data on your spreadsheet.

First, you need to know where and how to start with the Excel fill handle. On the bottom right corner of any Excel cell, you will see a large square:

Excel Fill Handle Image 1

When you hover your cursor right over that square, the cursor changes into a bold + mark:

Excel Fill Handle Image 2

Once your cursor shows that icon, you can click and drag the cell horizontally or vertically to fill in the data. The content of the first cell will determine what will be filled in across the other cells.

Filling In Dates

You can start with a single date or month and use the Excel fill handle to fill in the remaining months:

Excel Fill Handle GIF 1

Filling in Sequences

If you use several cells in a row with related data, you can highlight all the cells and use the Fill Handle on the remaining cell to fill in the sequence:

Excel Fill Handle GIF 2

Copy and Paste:

If you have a single piece of content and need to copy across cells, then you would use the fill handle on the first cell and it will copy it across all cells:

Excel Fill Handle GIF 3

As you can see, the Excel fill handle is an ideal tool to quickly fill in data.

We here at Learn Excel Now hope you enjoyed this week’s tips on the Excel Fill Handle. Hopefully this will save you some time as you build your spreadsheets.

Like Learn Excel Now? Subscribe above to receive our weekly tips and special offers.

Getting Social with Excel – Follow us on social media and share our content with your networks!

Kevin – Learn Excel Now

Excel Merge & Center – What It is & How to Use It

by

The following article shows you how to use the Excel Merge & Center tool.

The spreadsheet software of Microsoft Excel is best known for crunching numbers. This remains the primary reason people use Excel. But often times, you need text to accompany those numbers to make sense of what the numbers mean. Whether it’s column headings, naming the worksheet itself, itemization or important notes, adding text to Excel is a vital component of making your Excel spreadsheets be professionally viable.

Sometimes adding text can create formatting issues. Fortunately, Excel provides formatting options to make your spreadsheet accommodate text without compromising the number formatting. One such tool is the Merge & Center tool. This is perfect for text that runs over multiple cells. Without changing the Excel width or height of the individual cells in a column or row, you can merge all the cells together. The text will also automatically expand.

Let’s take the following spreadsheet as an example. The name of the spreadsheet has been added to the spreadsheet:

Excel Merge & Center Image 1

Notice how the text for the name of the spreadsheet runs from A1 to C1. The table, however, is already formatted so that the columns are the right width for the Column Headings. Adjusting column width for the name of the spreadsheet throws everything off:

Excel Merge & Center Image 2

This is where the Excel Merge & Center tool comes into play. The following Gif will show you how to use the tool:

Excel Merge & Center Gif

Now, as you can see above, the text for the spreadsheet heading runs the width of the table. But none of the columns or rows containing table data had to adjust. This is now a fixed heading.

From there, you can format the color, text size, etc. to get the heading exactly how you want it.

We here at Learn Excel Now hope you now feel comfortable using the Excel Merge & Center option.

Like Learn Excel Now? Follow us on social media and sign up for the newsletter!

Don’t forget to share our tips with your friends and colleagues!

Kevin, Learn Excel Now.

Excel Essentials: How to Remove Duplicates in Excel – Video

by

How to Remove Duplicates in Excel

Clean, readable data makes Excel much easier to use. A common struggle is how to remove duplicates in Excel. Having too many duplicates causes inaccuracies in reporting and analyzing data. But scrolling through your data to hunt and peck duplicates is tedious and time-consuming – especially when you have hundreds of rows of data.

Even using the Find function (keyboard shortcut: Ctrl+F) can take forever. But Excel provides built-in tools to help you with this.

Please enjoy the following video on how to remove duplicates in Excel:

We here at Learn Excel Now hope you know feel confident in your ability to remove duplicates in Excel

To discover essential tips like this and so much more, please join us for:

Excel Essentials: Most Needed Skills in Today’s Workplace
Wednesday, April 13, 2016

In this live, online event, our Excel expert instructor will cover the key Excel skills you need for workplace success.

Like Learn Excel Now? Sign up for our weekly tips, announcements and offers!

Getting social with Excel – don’t forget to follow us on social media and share our content with your networks!

Kevin – Learn Excel Now

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

Connect with Us

Contact

Learn Excel Now
questions@learnexcelnow.com
1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2021 ­Learn Excel Now

Terms of Service

Privacy Policy