Recognizes patterns in data and fills out the rest of the fields for you.
Look for the Flash Fill button is under the Data Tab in Excel!
Fill out the first cell so that Excel will know the pattern it’s looking for (field C1 in the image above or D1 in the second image above). Then put your cursor in C2 and click the Flash Fill button and Excel will do the rest!
If you want to move the text in the rows to columns or vice versa
Example: Copy down the column, January through December then put your cursor in the first field you want January to go into across the top (in this case B1). Click the Past drop-down and choose “Paste Special”. The dialog box will pop up. Click the “Transpose” button and click OK.
Recording a Macro
If you have something you do over and over you can set up instructions for this and set up a button or shortcut.
- First you need to see the “Developer” tab. If you don’t, click File, Options, Customize Ribbon, then on the right column, check the box next to “Developer”.
- Under the Developer tab, click “Record Macro”. Now, format your spreadsheet how it will consistently be formatted. Make sure to capture any blank rows that may have data in them at a later time. Once you are done, click “Stop Recording”.
- Add the button to your ribbon. Click File, Options, Customize Ribbon. In the left column, click the drop-down to Macros. In the right column, find the location you want to put your button (under the Home tab?). Create a New Group under the tab. Find the macro on your left and drag it under the new group you just created. Rename your macro and add a picture if you choose.
- When you close the workbook, it will ask you to save your workbook. That’s up to you but it will also ask you if you want to save your PERSONAL.xls workbook, choose YES.
That’s it! Three tips that hopefully helped you to learn something new today!
By Rana Kory