Posts in category: Uncategorized

Are multiple fields taking up just a single column in your Excel spreadsheet? This is a common issue when working with data from an external source. But manually retyping data into separate columns is tedious and error-prone. Fortunately, Excel offers a powerful feature designed exactly for this problem: Text to Columns. In this guide, I’ll walk you through how to use the Text to Columns wizard, and if you’re using the latest version of Excel, I’ll also show you other ways you can convert your text into multiple columns.

What is “Text to Columns” in Excel?

Text to Columns is a built-in Excel tool that splits a single cell of text into multiple cells based on a specific boundary. This boundary is usually a character (like a comma or space) or a fixed position in the text.

Common use cases include:

• Splitting full names (John Doe) into First Name (John) and Last Name (Doe).
• Separating product SKUs (Item123Red) into ID, Batch, and Color.
• Cleaning up data exported from CSV files or database software.

Method 1: Using the Text to Columns Wizard (Delimited)

This is the most common method. You use this when your data is separated by a specific character, known as a delimiter (e.g., commas, tabs, semicolons, or spaces). In the following example, I have a list of values in one column showing first and last name. I am going to break it out so that first name is in one column and last name is in another.

Step 1: Select Your Data

Starting by highlighting the range of cells containing the text you want to split.

• Pro Tip: Ensure the columns to the right of your data are empty. Excel will overwrite any existing data in those cells.

Step 2: Open the Wizard

Go to the Data tab on the Ribbon and click Text to Columns in the Data Tools group.

Step 3: Choose “Delimited”

A wizard window will pop up. Select Delimited and click Next.

• Pro Tip: Unless you always need to split a cell in the exact same place each time, you won’t need to use the Fixed Width option.

Step 4: Select Your Delimiter

Check the box that matches how your data is separated.

• If your data looks like Doe, John, check Comma.
• If your data looks like Doe John (as it does in the example above) check Space.
• You can see a preview of how your data will be split in the Data preview window at the bottom. If it looks okay, click Next.

Step 5: Format and Finish

In the final step, you can choose the data format (e.g., Text or Date) for each column.

• Destination: By default, Excel overwrites the original column. If you want to keep the original data, change the Destination cell to the next empty column (e.g., $B$1).
• Click Finish.

The end result is that the original column has now been split into two.

Method 2: Using the Text to Columns Wizard (Fixed Width)

Use this method when your data isn’t separated by a character, but rather organized by specific spacing (e.g., the Product ID is always the first 5 characters, followed by a space, then the Date). In the below example, the first two fields always contain the same length, and there is no delimiter that can break all of them apart.

1. Select your data and open Text to Columns.
2. Choose Fixed width and click Next.
3. Set Field Widths: Click in the ruler area within the Data preview section to create a break line. You can drag the line to adjust the width or double-click to delete it.
4. Click Next, verify your format, and click Finish.

Since the lengths of the first two fields are always the same, using Fixed Widths is an ideal solution in this example. This produces the following result:

Method 3: The Faster Alternative (Flash Fill)

If you are using Excel 2013 or newer, you might not need the wizard at all. Flash Fill uses pattern recognition to do the work for you.

How to use Flash Fill:

1. Suppose your data is in Column A. In Column B (the adjacent cell), manually type exactly what you want to extract from the first cell.
2. Move to the cell below it and start typing the second entry.
3. Excel usually detects the pattern and offers to fill the rest in ghosted gray text. Press Enter to accept.

The Shortcut:

Simply type the first example, click the cell below it, and press Ctrl + E. In the following example, I’ve entered my first values in column B and C, as an example of what my output should look like.

Next, I’ll go to cell B2 and press CTRL + E, and do the same in cell C2. Excel has now automatically filled in the pattern for me:

Method 4: Using Excel Formulas (TEXTSPLIT)

For users with Excel 365, you can use dynamic array functions to keep your data live. If the original text changes, the split columns update automatically.

The formula simply takes two inputs, the range, and the delimiter. In the case of a space being the delimiter, this is what the formula would look like:

=TEXTSPLIT(A2, " ")

This produces the same result as the other methods.

The benefit of this approach is that even if your data changes in column A, the formula will update; there’s no need to redo the flash fill or use the text to columns tool again.

If you liked this post on How to Convert Text to Columns in Excel: The Ultimate Step-by-Step Guide, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on X and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

You can import a lot of different data into your Excel spreadsheet. In this post, I’ll show you how you can get tables from Wikipedia both manually and with the help of Power Query. For this example, I’m going to use the page for the largest fast food restaurant chains. The data is sorted in a table, making it ideal to import into Excel.

How to import a Wikipedia table into Excel by copying and pasting

1. Copy and paste the values. Since the data is easily organized in Wikipedia, you can just use your mouse to drag, and copy and paste everything into Excel. The hardest part is just to make sure you’ve selected everything you want to copy.

Here is how the data looks after you’ve removed formatting:

Pro tip: if you have the latest version of Excel, you can use CTRL+SHIFT+V to copy the values without any additional formatting.

2. Remove the comments and values in brackets. In Wikipedia tables, you’ll often see references and notes after values. To remove this, you can use find and replace in Excel to get rid of the values. Set it up so that you replace [*] with nothing. The asterisk will remove everything which comes between the brackets, as well as the brackets themselves.

You can repeat these steps if there are any values in parentheses ( ) as well and any other special characters. After these steps, your data should look much cleaner. The downside is that it may take multiple adjustments to get the data cleaned up correctly and for all the issues to be accounted for.

Import Wikipedia tables using Power Query

Another way you can import tables is with the help of Power Query. Here’s how you can do that:

1. In the Data tab, select From Web and copy the link and press OK.

2. Select the table which resembles the data you want to import. Oftentimes there can be multiple tables, so it can be a good idea to cycle through them to see which one is the best match for your data. Then hit the button to Transform Data.

3. Remove any unneeded columns. To remove columns in Power Query, right-click on the headers and select Remove.

4. Clean up the data. You can use the Extract option in Power Query to grab values that come before a specific character. This is similar to the find and replace function but it can remove everything before a value:

Here you can specify the opening parenthesis as the character. You can repeat this step for brackets and other characters as well. This type of find and replace can also be done within Excel by just using *( to grab everything before the opening of a parentheses or *[ before an opening bracket.

Then, convert the values to Whole Number to ensure they are formatted correctly. If they aren’t, you’ll see some error values, in which case you’ll have to go back to the previous step to correct them. Otherwise, you are done and ready to move on to the last step.

5. Load the data back into your Excel spreadsheet. For this step, all you need to do is click on Close & Load on the Home tab.

Your formatted table is now loaded into Excel:

The benefit of using Power Query is that it saves your steps just like a macro would. If you want to refresh the data and download it again, to check for updated information, you can just right-click on the table and select Refresh.

If you like this post on How to Import Tables From Wikipedia Into Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

An Excel spreadsheet has lines and displays like a grid. And rather than buying graph or grid paper, you can print it out yourself and customize it. Below, I’ll show you how.

How to create your own graph and grid paper in Excel

1. Select all the cells in your spreadsheet. You can do this by using the CTRL+A shortcut.

2. Adjust your column width. Right-click any column header and re-size the column width to 2. You can make this smaller or larger if you prefer.

3. Select the PDF printer. Go to File -> Print and select Microsoft Print to PDF.

4. Modify the margins. Instead of Normal Margins, select Custom Margins. Set the margins all to 0 to maximize the space on your sheet. Also, on the margins page, select the options to center your page both horizontally and vertically.

5. Select the cells on the main tab. Go to the View tab and select Page Layout to see what an entire page will look like. Select all the cells for that initial page.

6. Adjust your borders. On the Home tab, select the drop-down option for borders and select More Borders.

Here you can adjust the color and look of your gridlines. Although Excel looks like it has gridlines on the cells, they won’t actually print out until you do this step and actually format them. I prefer to use a moderate grey color so that the outline isn’t too dark or too light.

6. Save the file as a PDF. By saving the gridlines how you want, you now have a graph paper template which you can re-use to print out whenever you need to. If you want 1 page or 100, you can just create as many copies as you need.

The benefit of setting up your own grid paper is that you can customize it so that it looks just how you want. For example, you could decide to highlight certain cells a specific color and apply different types of formatting to suit your needs.

If you like this post on How to Print Graph / Grid Paper in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Want an easy way to track your expenses? In this post, I’ll show you how you can use my free expense tracking template, which is based in Google Sheets. It makes copying and pasting expenses over simple, and you can quickly see reports that summarize your spending. No need for macros or even refreshing data.

How the expense tracker template works

There are just two tabs in the expense tracker template. One is Data tab where you enter all your expenses, and one is the Summary tab that has charts where no data entry is required.

Entering the data

On the Data tab, these are the following areas where you’ll enter in information:

• Columns A & B are for the Vendor and Amount. Expenses should be positive and refunds are negative. This can just by copy and pasted from your bank or credit card statement downloads.

• Column C is optional and only necessary if you don’t want an expense item to go to its default category. For example, you may have a one-time expense that throws off your budget for a spending category. You can check off the box for ‘Irregular’ and it will flow through to that category and bypass the default spending category.

• Columns G and H are where you will set up your Vendors and which spending category they relate to. This is important so that when you enter your transaction data in columns A & B, the Category field (Column E) will automatically populate as well. Otherwise, everything will go into a “Not Categorized” bucket.

• Column J (aside from the first row which is reserved for income) is where you will set up your spending categories. The spreadsheet accommodates 10 categories. Any more than that and the charts can become difficult to read.

• In Cell K1 you can enter an income amount (if applicable). The other values in column K are simply a tally of the spending by category.

Once you have all your transactions entered, you can go to the Summary tab where you’ll see a summary of your spending.

The reports

There are three charts on the Summary tab:

• A waterfall chart is the main chart that you’ll see on the page. It shows you your spending during the period. This starts with an income amount (this is entered on the Data tab) and every expense is negative afterwards, showing you how much of your income is left over. There are two totals, one before irregular expenses and one that includes everything. The purpose here is to show how your spending would have looked if not for one-time, irregular expenses, and how much of your income was left before and after those expenditures.

• There is an Amount vs Vendor chart which shows you spending by Vendor.

• % of Expenses shows a breakdown of spending by the different categories. In the middle it shows the total spending during the period.

There is no defined period for this template

There are no date fields in this template for the purpose of keeping it simple. Whether you want to look at one week, two weeks, a month, or several months, you can plug the data into here to see how much your spending was for that period. However, if you get past row 200, you will want to copy down the checkboxes and the formula in column E.

Download and use the template

The reports in the template don’t require a refresh and so as soon as you enter your data, you can begin viewing the Summary tab.

If you’d like to use the template, you can get a copy of it here.

If you like this Expense Tracking Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

There are numerous ways to display numbers in your reports. Using percentages and decimals are two common ways to do so. In this post, I’ll cover when it might makes sense to use percentages and when to decimals may be more appropriate. I’ll also provide you with some easy-to-use formulas that will allow you to convert percent to decimal, and vice versa. You can do these calculations whether you’re in Excel or just have a calculator handy.

Converting between percent and decimal

If you want to convert numbers between percent and decimal, the process is incredibly simply in Excel. Select the value(s) you want to change and then select the format you want.

However, if you’re not using Excel, you can still accomplish this manually. To convert a percent into a decimal, all you need to do is to pretend you’re moving the % sign two spots to the left, and then convert it into a period.

In the case of 50% it becomes .50, or 0.50, depending on whether you want to display the 0 in front. This also works with large percentages, such as 1,000%. That’s a significant percent, but the same logic applies, and following the same steps would convert the value to 10. That tells you that the new value is 10 times the size of the original value.

To convert back into percent, you multiply the value by 100 and drop the % sign.

Formula for converting decimal to percent:

Formula for converting percent to decimal:

How to calculate percent of something versus percent change

One important distinction you should consider is to determine whether you’re looking at a portion of something, or a change in value. If you’ve eating half of a pizza, that’s 0.5 of it, or 50% of it. But if you’re talking about the price of something going up by 10%, that’s a slightly different calculation.

Let’s take the stock price of Microsoft as an example. At the start of 2020, its stock price opened at $158.78. By the end of 2021, it finished the year at $339.32. If we take the ending price and divide by the beginning price (339.32/158.78), then that gives us 2.14, or 214%. It would be correct to say that $339.32 is 2.14 times $158.78. But it would not be correct to say the stock price increased by that amount. That’s because you’re not calculating the actual increase in value. To do that, you need to subtract 1, to arrive at 1.14, or 114%.

If the stock didn’t increase at all, you would be left with an equation of 158.78/158.78, which would be equal to 1, or 100%. The ending value was 100% of the beginning value, but it certainly wasn’t an increase of 100%. Thus, the need to deduct 1 will give us the correct answer in that case — a 0% change. The same goes for decreases. Suppose the stock fell by 30% to $111.15. Dividing $111.15 by $158.78 would tell you the price is now 70% of the value it was at the start of the year. That’s correct, but to get the percent change, you deduct 1 from that, which tells you it declined by 30%.

The formula for percent change:

The formula to calculate the portion or relative size of something:

It’s a subtle difference but it’s an important one to note, which can prevent you from making a mistake in your calculations.

What to do if your decimals or percentages are very small

If you’re dealing with numbers that go to four or five decimal places, it may not be helpful to display them as percentages or even as decimals. For example, if you were to say the odds of getting struck by lightning in your life were 0.00654% or 0.0000654, whether you use decimal or percent isn’t going to be helpful in conveying those adds.

One thing you can do is flip those numbers around by calculating the inverse. By taking 1/0.0000654, that returns a value of approximately 15,300. Stated another way, it tells us that the odds of getting struck by lightning are 1 in 15,300. It’s a far more effective way of communicating the odds as you’re no longer dealing with miniscule percentages that can be hard to visualize.

The formula for converting from a decimal value to a rate:

As you can see from these formulas, they are fairly simple and can be incorporated into your spreadsheet and even done just on a calculator. You could create LAMBDA functions with these formulas, but they involve so few steps that the time savings may not amount to much.

If you liked this post on How to Convert Percent to Decimal, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Nested IF statements aren’t always the most efficient way to structure your formulas. And they can make it difficult later on if you need to fix a formula or make a change to it. What’s worse, is if you inherit someone’s spreadsheet and try to dissect their nested IF functions. In this post, I’ll show you how you can get around using nested IF statements and the different alternatives you can use.

How nested IF statements works

To start, let’s look at how you might construct nested IF statements. Here’s a data set that has different cardholders and their related expenses:

I’m going to create a column with a series of IF statements to see how much Bob spent on home repair since the start of 2021. With a nested IF statement, I might first check if the cardholder is Bob. Then, if that’s true, check if the category is Home Repair. And then, check if the date is after Jan 1, 2021. Here’s how that would look inside of a formula:

=IF(B2="Bob",IF(D2="Home Repair",IF(A2>DATE(2021,1,1),E2,0),0),0)

You can see this starts to get pretty messy. And the IF statements could continue going on if you have even more criteria you want to fit into here. If I were to copy this formula down, I could get a total of all the values where Bob spent money on Home Repair. However, this wouldn’t be terribly efficient.

You could use a pivot table to quickly summarize the data by cardholder spending and category. But for this example, let’s assume that you need to do it within a formula and can’t rely on creating a pivot table when doing these types of calculations.

Using the AND function to group multiple criteria

An effective option in making your nested IF functions shorter is by using the AND function. It allows you to put all your conditions in one neat formula that you can embed within an IF function. Within the AND function, I can enter all these arguments:

AND(B2="Bob",D2="Home Repair",A2>DATE(2021,1,1))

You can keep on adding to conditions to the AND function for as many rules as you’d like to apply. This can make it cleaner to see all your criteria. All of the criteria within the AND function need to be met for the formula to return a TRUE value. Similarly, you can use the OR function if you want to check if any criteria are met.

The above formula can easily be embedded within the IF function as follows:

=IF(AND(B2="Bob",D2="Home Repair",A2>DATE(2021,1,1)),E2,0)

This does the same job as the nested IF formula except it’s a lot cleaner. However, the drawback here is that like with the nested IF statement, if you wanted to calculate all of the instances where Bob spent money on Home Repair, you would need an extra column and ad all the values up. That’s still not very efficient.

Using an Array function

Another option you can use for quickly tabulating these results is by using an array function. This can apply the logic to every cell and calculate the total for you. Rather than IF and AND statements, you can evaluate each argument, force a 1 or 0, and then multiply that by the amount to arrive at a total. Here’s how that formula might look:

=(B2:B100="Bob")*(D2:D100="Home Repair")*(A2:A100>DATE(2021,1,1))*(E2:E100)

This formula extends to the bottom of my data set. How it works is that each group of parentheses represents an argument. If it evaluates to TRUE (i.e. the criteria is met) then the value becomes a 1. If the criteria is not met, then it evaluates to a 0. So if all the criteria is met, the results will be 1*1*1 multiplied by the amount in column E. If any one of the conditions is not met, then the result will be a 0. This is the same method as the earlier examples.

The downside of an array is that it will automatically extend to the bottom of the data set:

This again runs into a similar limitation where your formula of using up more cells than you might want to occupy. But to get around this, you could add the SUM function before your array formula:

=SUM((B2:B110="Bob")*(D2:D110="Home Repair")*(A2:A110>DATE(2021,1,1))*(E2:E110))

Using SUMPRODUCT

Another function that can do the job is SUMPRODUCT. With this function, it can take care of all the criteria while also summing up the total in just one cell. The logic is similar to how the array formula was calculated above. The key difference here is to put that all within the SUMPRODUCT function. Here’s how it looks:

=SUMPRODUCT((B2:B110="Bob")*(D2:D110="Home Repair")*(A2:A110>DATE(2021,1,1))*(E2:E110))

This will obtain the same result as if you were using the array function. SUMPRODUCT is used for multiplying arrays but it can be made to work in this fashion as well. The key is making sure you encompass all the arguments withing parentheses (hence why I opened and closed SUMPRODUCT with not one but two parentheses.

As you can see, there are many different ways you can make your formulas more efficient in Excel without having to rely on nested IF functions.

If you like this post on How to Avoid Using Nested IF Statements in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.