Cells Archives

Enter Data Into Excel In A Specific Order

March 17, 2023 by Barbara

Entering data into Microsoft Excel can be time-consuming for users. Especially if the cells that require data are spread all over the excel worksheet. The last thing you want is a user to the tabbing or clicking around the worksheet frantically looking for the correct cells to enter data. In particular if you need the user entering data in a specific order.

Excel CAN force the sequence of cells that you want users to select. So how does that work?. This solution relies on defining a named range which essentially is a sequence of cells that you specify. for more information on named ranges, I suggest reading my dedicated articles here

Formula Friday – 3 Reasons To Use Named Ranges In Your Excel Formulas

Write A Macro Which Highlights Named Ranges. Macro Monday.

See below as an example of the sequence of cells that I want users to type into Excel. Pretty erratic isn’t it?.

So let’s give this a go.

Creating A Named Range

Click in the cell that is second in your required sequence
Hold down the CTRL key and click the cell sequence 3, carry on and select the cells in the correct order
When you have reached your last cell in the sequence, click the first cell in the sequence
Click the cursor into the Name Box (the area to the left of the bar that shows the cells addresses)
Name this area of cells, i.e a named range in this example I am using MyDataSequence – then hit Enter and the name box will return to showing a normal cell reference
Save your file

When you need to get a user to enter data into Excel in that order just select your named range MyDataSequence. Begin typing and press enter. Excel jumps to the next cell sequence ready for data entry

To view the details of the named range you created follow the steps below.

Formula Tab
Defined Names
Name Manager

So, as you can see the named range is just a sequence of cells, that we selected earlier in the exercise. Give it a go.

Understand The Power Of Concatenation Of Cell Formulas

August 12, 2022 by Barbara

When you are working with data in Excel, you may often find yourself needing to combine the results of two or more separate cell formulas into a single cell. This process is called concatenation. In this tutorial, we will show you how to concatenate cell formulas in Excel and explain the power that concatenation can give you when working with data. We will also provide some tips on how to avoid common errors when using concatenation. Let’s get started!

Back To Basics Concatenation.

The syntax of this formula is

=CONCATENATE(TEXT1,TEXT2…)

Up to 255 text entries can be added to the function and each one of them should be separated by a comma. One point to note, CONCATENATE does NOT add in extra spaces between text, in order to do this, you need to accommodate these extra spaces within the formula.

Also note, you can also use CONCAT, TEXTJOIN or & in the same way. Use whichever you feel comfortable with OR what is available in your version of Excel.

So, don’t be put off by this CONCATENATE terminology. The concatenation of cells is one handy little Excel feature that is fabulous. Basically, it means combining the values of two cells. The great thing about Excel is that it has functions that work with arithmetic functions but it also ones that work well with text and Concatenation is one of them.

Concatenation Excel Example.

Heres an example of when you might find concatenate in Excel useful.

Suppose you have a first name in one cell and last name in another column, and you need to combine then in one cell for a mail merge or some other administration exercise. In this example I want to generate a username with the first and surname. The sample data set I am using is below.

There are actually a few ways to concatenate the information

Use the built-in formula in the new cell where you want the new information

=Concatenate and follow instructions on screen entering the first text, then the second. Hit return and voila your results are displayed.

So, to generate the username in this example, the formula is below.

=CONCAT(B3,” “,C3)

Notice the extra space added. Any extra punctuation including commas and spaces etc need to be manually added.

Alternatively there is another way way which some people find a lot easier. This is using the & symbol. Let’s suppose you have the same scenario where you want to combine first and last names in to one column. Typing the following will achive this Same result. Possiby a bit quicker.? What do you think?.

Excel is a powerful tool for organizing and analyzing data. With the concatenation feature in Excel, you can quickly and easily merge cells into one long string. This can be useful for creating labels or headers in your spreadsheet, or for combining data from multiple sources into one cell. The example used was to join first name and lastname in our data source with little effort. To concatenate cells in Excel, select the cells you want to merge and use the CONCATENATE function. We’ve shown you how to do this using both the standard formula and the quicker way using the & symbol. Feel free to browse our other Excel tips, videos, Ebooks and sign up to the Excel At excel Newsletter for three free tips every month.

More Excel Tips

Why Is Excel Changing The Last Digit Of My Number To Zero?

September 22, 2021 by Barbara

Hello Excellers, welcome to another blog Excel post in my #ExcelTips 2021 series. Today let’s look at why Excel Is changing the last digit of a number to zero. That’s correct Excel changes the last digit to zero sometimes. If you have ever come across this, it can be confusing and damn frustrating. You type a long number into Excel, and weirdly anything past digit 15 appears as zeros. So if you are entering a credit card number, which is usually 16 digits, then the last is displayed as a zero.

Because Excel only stores 15 significant digits in a number, Excel changes any remaining digits to zeros. As the formatting of the cell is numbers, Excel interprets the numbers as being intended for calculation. Okay, so can we reformat them as Text or something else? Well, no, once entered, you cannot just reformat. Instead, you will need to format new cells as Text then enter the numbers again.

The Solution To Solve Why Excel Is Changing The Last Digit To Zero.

Hmmm, so is there anything else we can do to avoid having to re enter all of those numbers?…there are a few things.

Solution 1. Leading Apostrophe.

The first solution is to insert a leading ‘in front of you number. So, for example ‘2560089996999987 would display correctly. You can see in the screen grab below without the apostrphoe Excel changes the digit to zero.

Solution 2. Using The Text Import Wizard.

Now, onto the second solution. If you are importing your data from a .rtf or.csv file, when the text import wizard appears, scroll to your field containing the offending ‘longer than 15 digits’ number. Click on it, then in the upper right hand section of the window, change the field of column type from ‘General’ to ‘Text’.

text import wizard to prevent excel changing last digit to zero

Note. The Text Import Wizard is a legacy function. If you are using esrlier version of Execl then you will be ok. If you have a newer version of Excel you will see the Get & Transform Tools. So since In Excel 365 and 2016 the “Text Import Wizard" has been removed. But, if you want to use the legacy version of Text Import Wizard you can get it back.

Restore The Text Import Wizard.

Follow the steps below to restore the original text Imprt Wizard.

FIle | Options | Data
Select Show Legacy Data Import Wizards
Hit Ok

You can now go ahead and use the Legacy Data Import feature to comportably import your data the way you want to.

why is Excel changing last digit to zero

Next select your file to import. After selection hit Import.

I always make sure to check out any data I am importing to see if this issue may occur. It is easier to catch the issue at import stage than having to either get a leading apostrophe or re type. Who wants that right?.

More Excel Tips.

Sorting Excel Data By Colour

October 8, 2012 by Barbara

Excel allows you to sort your data in many ways, but with Excel 2007 onwards we can now sort easily by the font colour or by the cell fill colour. It is easy and effective and is available as a custom sort function.

Select the data you want to sort
Data Tab
Sort And Filter Group- Sort
Use drop down menu in the Sort On Column and select to sort by either the font or cell colour
Using the drop-down list in the Order column, select the color by which you want to sort. (Excel allows you to sort by any color within the range to be sorted.)
Using the second (right-most) drop-down list in the Order column, specify whether the color you picked above should be on top or on bottom.
If you want to additionally sort by other colors, click the Add Level button and repeat steps 4 through 6 for the additional level.
Click Ok.

In my example below I have sorted my Sales by cell colour. My Sales column was originally conditionally formatted using the criteria to highlight all values over 5000. Then take this a step further by using the Sort By Colour function in Excel. Nice.

Want Even More Excel Tips?

1. Quickly Import Data Into Excel From The Internet

2.Quickie Excel Tip:- Shrink Your Excel Data

3. Create A Timestamp In Excel With Formulas

4. Reset You Last Used Cell In Excel

Quickly And Easily Import Data To Excel From The Internet.

September 5, 2012 by Barbara

If you have ever used data from Yahoo or Google in a data table have you retyped it in, or tried to copy and paste it? It either takes too long or the formatting appears all over the place. Google is an excellent source of information you can analyze using Excel, I just do not want to retype it or copy it!

You don’t need to to, you can import the data into Excel as long as the data is formatted on the web page as a table. Here’s how to do it:-

Open an Excel workbook
Data Tab
Get External Data
From Web
Type in the URL of the web page that has your table of information
Data tables will have a yellow arrow next to them
Click on the yellow arrow of the data table you want to import

Next Hit Import
Choose the position of the data table in a new or existing workbook
Hit Ok

There you go your data table is imported, no fuss, no mess and no re typing!

This is really handy for business statistics for sales trends or any published figures that are readily available on the Internet.

Give it a go!

More Excel Tips

1. Create A Timestamp In Excel With Formulas

2. Hide Your Formulas For Prying Eyes

3. Remove Personal Information From Excel 2007 Workbook