![]() And for that, we need the VLOOKUP function. But, instead of randomly picking a letter, we will look up the letter associated to the customer id. Now that the orders have the id of the customer, we want to know the name of that customer.įor the sake of practice, the names generated will be composed by the word “Company”, followed by a letter/name from the helper data. The customer name will be the first time where we make use of the helper data in the second worksheet. We’ll make them static at the end of the demo. So, write the formula in the first cell of the Customer ID column and then use one of the two methods we’ve seen previously to copy the formula to the remaining 999 cells.Īgain, with each action, you’ll see the numbers being generated again. If you’re using different helper data, then adjust these two limits as needed. In this case I chose to pick a number between 1 and 10 because the helper data contains ten customer names (the data in the second worksheet). As such, our formula is =RANDBETWEEN(1, 10)įriendly tip: if you don’t like using the mouse while writing a formula, press the Tab key to autocomplete the name of functions. The generation of data for this next column is straightforwad, as we are just picking a random customer id by generating a random integer. Whichever one you prefer, please keep in mind these will be the crux of our data generation as they are what allow us to have an arbitrary number of rows of data. Random date generation formula successfully copiedĪnd with these last two sections, we have finished generating the dates for the fake orders and have learned how to copy a formula to multiple cells with two different methods. This is achieved by either pressing Ctrl+D or by selecting the Fill Down option in the menu. ![]() Now you have all the cells of the Date column selected, up to the one that already has a date created, and we can fill the empty ones. To select the remaining cells of that column, press the Ctrl+Shift+Up Arrow key combination. This means now you have the last cell of the Date column selected. Move the cursor to the last cell of the Order ID column (Ctrl+Down arrow while having a cell of that column selected) and then press the right arrow. Since we have an adjacent column of the size we want to select, we can do it with simple keyboard shortcuts. Imagine, selecting a thousand rows, by hand. But we are not going to use the mouse for this. ( Copy formula to multiple cells with Fill )įor the first method, using the Fill feature, we need to select all the cells which we want to copy the formula into. The first uses the Fill feature we’ve seen previously, the second is a literal copy and paste after selecting the desired range (by typing the range, not by selecting with the mouse!). What about the remaining 999 orders? Let me show you two ways of achieving this. Okay, all is well, but we have only generated the date for a single order. Don’t worry though, at the end of the demo we’ll make these randomly generated values fixed. Pressing F9 or any interaction with the workbook recalculates the formulas, i.e., generates another random date. However, the cell displays the generated value as a date because we formatted this column in the beginning to be of the date data type. Create a second worksheet by pressing the plus button in the footerīy the way, keep in mind that RANDBETWEEN generates an integer, even if we have given it dates to choose from. By default it has one worksheet, but that first one will be used for our dataset. Open Microsoft Excel and create a new blank workbook. We’ll have that data written in a second worksheet, used exclusively for look up when generating data for our table. ![]() Since we’ll need to randomly choose values for our table, such as the quantity purchased, or look up values in a given range, like the price of a product, it would be good to have those values available somewhere to refer to. Now that you’ve been introduced to the demo, let’s talk about the helper data used for look up. Our purpose with this exercise is to become more familiar with manipulating data, both creating new data and making use of already existing data (via look up). A real sales table could never have this structure. However, before going further, please understand the data included in this table is included just for the sake of practicing Excel. For instance, given the customer id, use the name of that customer. The other thing that will be quite important is to have a worksheet from where we can look up values. For the effect, we will mostly make use of random choice of values and number generation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |