How to Enter Data into a Microsoft Excel 2016 Spreadsheet
During this Microsoft Excel 2016 training tutorial video, we will show you the basics of entering and editing data in a workbook. We will also demonstrate how to use autofit column width to make data fit within a cell, as well as how to format numbers into currency.
Video Transcript:
In this section we’re going to start to look in more detail at how to enter and edit data in Excel 2016 and then I’m going to set you your first actual exercise on the course.
Now before I start I am once again going to be basically working with mouse and keyboard. I will refer to touch once or twice. If you are using a touch device you’ll either need an onscreen keyboard, so you’ll get the equivalent of this, or if you have an external keyboard for your touch device you need to have that setup because we’re going to be entering quite a bit of text and numbers during this and subsequent sections.
Now the other thing to bear in mind is that in this and the next few sections I’m really going to concentrating on getting the numbers in and formatting them in the sense of making sure that dates look like dates and so on. The actual cosmetics, how pretty what we’re doing looks, is something we’re going to come back to later on in the course.
Now the first thing that we’re going to do is to start work on a new workbook and to make a new workbook, you’ve already seen how to do that. You can also use a Keyboard Shortcut. The keyboard shortcut is Control-N. So there is my new workbook and what we’re going to put in this workbook is we’re going to do a very simple monthly electricity account.
So the first thing I’m going to do is to save the book with the name Monthly Electricity. There we are. And now what I’m going to do is to enter the months of the year. Now you’ve already seen that we can actually use a Series Fill feature here. I’ll come to that again in just a moment. The first thing I’m going to do is just select a cell, it doesn’t really matter which one at this stage, and I’m going to type in the first month of the year which of course is January. Now when I’ve finished typing January if I press the Enter key two things happen.
First of all I leave that cell and that cell has its value January in it. But also notice that the selection, the cell selection goes to the next cell below and that lines me up well for typing in February. Now if after you’ve typed you press the Enter key by default the cell that’s selected next is the cell below the one you’ve just typed in. But if instead of hitting the Enter key I’d used the Enter, the tick mark on the Formula Bar, and if I used that now after typing February watch what happens. February is entered but I’ve now still got the same cell selected. So although I’ve got the same content my next selection is different depending on how I terminate entry of data into a cell. If I now want to go to K10 I can of course click in K10, enter March and of course now I’ve got the same options but also I can terminate entry in a cell by just clicking anywhere else on the sheet.
So it’s important to recognize that when you’re entering data what happens after you’ve entered that data depends on essentially what you do at the end of the data entry.
Now the next thing I want to do is to use the feature that we saw before to get all of the months of the year in quickly without having to type them all. So I’ve selected January. I’m going to use the little symbol at the bottom right hand corner of the border there, drag down the requisite number of months, release, and I have all of the months of the year.
Now notice that when we get down to September and November and December they’re actually slightly too wide to fit into the available space and they spill over apparently into the next column. Now in fact they’re not in the next column at all and September, for example, is wholly within the K16 cell. But because we haven’t told it otherwise what happens when something is too wide for the cell is that Excel lets it just display over the next one.
Now one way of getting round this is to make column K wider and there are a couple of ways of doing this. One way of doing it is if you hover over the top of the column over the K itself you see that down pointing arrow. If I move to the right until it becomes a vertical bar with two arrows, one pointing at each side, and click with the mouse I can actually drag to make that column wider. And I can drag it to pretty much any width that I like.
As an alternative to that, let me just put it back. In fact I’ll overdo it and make it really far too narrow. With that column selected if I go up to the Home Tab and the Cells Group and the Format button at the bottom of that there is a dropdown and one of the options on there is AutoFit Column Width. And if I select AutoFit Column Width then it will automatically choose a column width whereby everything in the selected column, column K in this case, fits comfortably within the column.
Want More? Get Started With a Free Excel 2016 Course! Click Here
Now in fact you can apply that command to more than one column at a time. So if I’d actually put data in columns K, L, M, and N, for example, I could select all four columns and use that AutoFit feature and all four columns would have their width suggested to accommodate the data contained within them.
What I’ve done now is to make a mistake in the spelling of September. And having entered the data and got the column width sorted out I look at it and I think, “Oh dear I’ve spelt September wrongly.” To correct an error if you just click on September in this case and started typing you would overwrite everything that’s in that cell. Now if I don’t want to overwrite it, I just want to correct the bit that’s wrong, I’ve got a couple of options. One option is just to press the X button, the Cancel button next to the Formula Bar and it goes back to what it said before so I can recover what was there before. The other thing I can always do, remember, is an Undo. So there’s always the Undo option. Of course if I press the Undo button too many times, such as here, and undo that correction I made or the change that I made to the column width don’t forget I’ve always got the Redo button and that will redo the column width for me.
But let’s go back to having the September error. So I’m going to Redo the September error now. If you want to just edit within a cell there are a couple of options. I find the simplest way to do it is to select the cell and then click in the Formula Bar and do the correction actually in the Formula Bar. So I’m actually looking at the Formula Bar as I’m typing here. Some people prefer to just click and then click the cursor within the cell, so in this case the K16 cell, and basically look at the cell as they’re typing. I find it easier to use the Formula Bar.
By the way if you don’t like the Formula Bar you can actually disable it. You might want to have a little exercise now, give you a little extra job to do. See if you can find in Excel Options where the option is to disable the Formula Bar. If you have trouble finding it it’s on the Advanced Tab in the Display Options. But if you want to get rid of it, perhaps to give yourself a bit more space, you can. I tend to use it so I tend to keep it onscreen pretty much all the time.
So I’ve got my list of months. I’m just going to do a little Save here to save my work. Of course I’ve got AutoSave on anyway but just to be extra safe.
Now what I’m going to do against January is to put in the cost of electricity in January. So I’m going to type in 124, hit the Enter key and you can see 124 as the cost. Now, one of the most noticeable things is that when you enter text such as January by default it is left aligned. When you enter numbers, by default, they are right aligned in the cells. So as I enter these costs, hitting the Enter key each time, the numbers are right aligned. However they don’t look much like amounts of money. They just look like any regular numbers. And when you are entering numbers there are many, many options for what the numbers actually mean. And one of the things we’re going to do here is to actually indicate that these numbers are amounts of money.
So let me go back and click on 124 and what I’m going to do on this occasion is to right click and I’m going to choose Format Cells from the Contextual Menu. The first page, the first tab within dialogue is selected. It’s the Number Tab. And you can see that by default is taken 124 as a general format number. Note the little tip there. General format cells have no specific number format. Now supposing I want to make it a currency amount. Let me click on Currency.
Now in this case because I’ve got myself setup to be using U.S. currency, I’ll tell you more about that later on, it gives me a sample of what the number will look like if I choose this setting. Note the sample there. Dollar symbol, 124.00. It gives me an option to change the number of decimal places. It also gives me an option to change the symbol that’s being used from a U.S. dollar symbol to just about any other symbol there is. And it gives me a choice of showing negative amounts using either a minus symbol or red or a number in brackets, the sort of accountancy type formats. I’m going to stick with the defaults here, click on OK, and 124 is now displayed as $124.00.
Now if I now want to apply that same format to the other two numbers there I can select both of them. I could perform exactly the same operation. It’s worth noting that I can access that Format Cells Dialogue from a variety of places. Another one is up on the Ribbon on the Format button, the bottom part of it in the Cells Group on the Home Tab there is a Format Cells button link there, opens the same dialogue. I could do exactly the same thing again, change the currency to the same settings. I can in fact also apply it to the cells that I haven’t yet put amounts into or indeed to the whole of column L. If I selected column L, click on again Format, Format Cells. There’s a difference this time because when I’ve selected column L I’ve selected a load of cells. Three of them are in currency format and the rest will still be in the same format they were in before which was that general format. When you make a selected the covers different formats then when you bring up the Format Cells Dialogue Excel doesn’t know which of those to show so it basically indicates that you have a multiple selection by not showing any specific format. And what I would need to do now is to make all of the relevant selections. So if I said Currency and I say the symbol I want to use is the dollar symbol everything seems to be okay. Two decimal places, click on OK. Now that format has been applied to all of those cells. And if I click say in the amount for April and just type say 97.5, press the Enter key, then that is automatically formatted with that currency format that I’ve now applied to all of those cells.
Want More? Get Started With a Free Excel 2016 Course! Click Here
So finally then I filled in the rest of the month electricity costs there. I’m going to Save that and that’s the end of this section. I’ll see you in the next one.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
