Data manipulation is one of the most powerful abilities of Excel. Flash Fill is a tool in Excel that enables you to manipulate data in a cell according to your needs.
In this tutorial, we will learn how to switch first and last names in Excel with the help of Flash Fill.
Switch First and Last Name
Now we will walk through the steps of switching the first and last names in Excel, and we will use Flash Fill for this purpose.
Flash Fill works as follows: you define a new pattern, and Flash Fill imitates this pattern for all other cells. In this case, the pattern that we will create is going to be “LastName, FirstName”.
Step 1: Go to a cell that contains a full name. In the cell right next to it, write the pattern you want in a reverse fashion: “LastName, FirstName”.
Be careful that the first and last names are written correctly, just like the original cell.
Step 2: Press Enter on your keyboard to go to the cell below, or click on the cell below the new pattern.
Step 3: In the Home tab, click on the Fill icon in the Editing part in the Excel ribbon, and then click on Flash Fill.
This will automatically fill out all cells next to a full name, and your names will successfully be switched:
Using this method, you can use any pattern that includes a first name and a last name. For example, you can also abbreviate a first name:
As long as you provide a new name format, Flash Fill will automatically learn the pattern and apply it to other cells. It’s like magic, right?
This extremely easy method can enable you to perform all kinds of text manipulation. Learning to use Flash Fill is an excellent skill. Nice job!
Copying filtered data onto a new sheet is a great way to segregate related data rows.
It allows us to have the extracted rows available anytime without re-applying the same filters.
We have to be careful, though — the hidden rows within the dataset might get included in the cells copied if we miss the crucial step of selecting only the visible cells.
In this tutorial, I’ll show you ways to select only the visible cells within a filtered dataset so that you can easily copy and paste them onto a different sheet.
Table of Contents
1. Using a keyboard shortcut
1. Select all the cells within your dataset.
You can do this by first selecting one of the cells within the filtered records and pressing CTRL + A to select all.
Your entire filtered dataset should be highlighted (as shown in the image above).
2. Next, press the keyboard shortcut that selects only the visible cells.
Note that the keyboard shortcut varies for Mac and Windows. Please use the table below for reference.
Computer System
Keyboard Shortcut
Notes
Windows
ALT + ;
ALT key and semicolon (;).
Mac
CMD + SHIFT + Z
Command and shift keys and letter Z.
After pressing the appropriate keyboard shortcut, notice that the hidden rows are excluded from the selection. You will see a white demarcation line between rows that have filtered-out rows that follow them.
4. You can now copy the selected cells and paste them somewhere else.
Press CTRL + C to copy.
Notice that the broken lines appear in between rows that have hidden cells after them. These lines let you know that only the visible cells are selected and copied.
5. Open the worksheet (or workbook) where you would like to paste the filtered cells.
Select a cell and press CTRL + V to paste.
And that’s it! You have your filtered cells copied onto a new worksheet or workbook.
2. Using the Go To Special menu
If you are not much of a fan of keyboard shortcuts and prefer doing tasks with your mouse, you can also select visible cells using the Go To Special menu.
1. Highlight all the cells within your filtered dataset. (Select one cell within the dataset and press CTRL + A to select all).
2. From the Home tab, go to Find & Select and click on Go To Special.
3. The Go To Special menu should appear.
From the list of options, select Visible cells only, then click OK.
4. That’s it! You now have only the filtered cells selected.
5. You can now proceed with copying it to a new worksheet or workbook.
3. Using the QAT command
If you regularly copy filtred cells, there is an even quicker method for you to select visible cells. All you need to do is add a Quick Access Toolbar (QAT) command that you can readily click whenever you need to select the visible cells.
To do this:
1. Click on the Customize Quick Access Toolbar button right above the Home tab.
This button has an inverted triangle with a thin line on top (as shown in the image below).
2. From the list of options that appear, select More Commands.
3. The Excel Options menu will appear with the Quick Access Toolbar tab opened.
Select All Commands from the “Choose commands from” dropdown menu.
4. Wait a bit until the list of commands under the dropdown menu gets updated and looks something like this:
5. Scroll down until you find the Select Visible Cells from the list of commands.
Once you find it, select it, and click on the Add >> button.
6. You should now see the Select Visible Cells command added to the list box on the right.
This list box contains all the commands added to your QAT.
After adding the command, click OK.
7. That’s it! You now have the Select Visible Cells in your top left toolbar.
Click on this button whenever you want to only select the visible cells in your worksheet.
To use it in your filtered dataset:
Select all the cells in your dataset.
Click on this button. Notice that Excel immediately selects the visible cells.
You can now press CTRL + C to copy the visible cells and paste them onto a new sheet.
Conclusion
Excel’s data filter is a great way to sift through your records and only capture the set of data that meets the criteria that you have set. But please remember that when you copy a filtered data set, you must ensure that only the visible cells are selected. You can choose from any of the methods above to do this.
When working with exported data, there will be times when we will have only the month and year included in the dataset (the day is nonexistent).
It was okay until you realized that, for very particular reasons, you must turn these two bits of information into an actual date.
In this tutorial, I will teach you how you could convert your month and year to an actual date. Also, in the latter part of this article, I will teach you how to do it in reverse – from date to month and year.
Table of Contents
Steps to convert month and year to date in Excel
Since I have no idea how your month and year are currently formatted, we have to do some data prep to ensure that we are working on the same data format.
Our goal is to have our month and year look something like this:
* The month is in numerical format (1 to 12).
* The month and year are on separate cells.
If your data already looks something like the image above, you can skip the following data prep sections.
IMPORTANT:
Before doing the data prep, please copy your Year and Month column to a new sheet and perform the data prep steps there. We want to ensure that your dataset is safe from accidental alterations.
Data Prep: Split Month and Year and place them on separate cells using the “Text to Columns” option
We have to split our Month and Year and place them in separate cells.
If they are joined together by a space, a dash (-), a slash (/), or any other symbol (similar to the image below), the fastest way to split them is by using the “Text to Columns” option.
To do this:
1. Highlight all the cells containing the year and month. Do not include the header.
2. From the Data menu, click on Text to Columns.
3. The Convert Text to Columns Wizard will appear. From the list of options, select Delimited. Then, click Next >.
4. Now, select the appropriate delimiter for your data.
In my example, a space separates the month and year (e.g., Jan 2012).
Since this is the case, I have unchecked all other delimiters and only ticked the Space checkbox.
You know you have selected the correct delimiter once you see your month and year on separate columns in the Data preview.
If your delimiter doesn’t exist in the list of available options, tick the Other checkbox and type the symbol that separates your month and year.
Once you’re happy with the result, click Next >.
5. You should now reach the final step in the Wizard.
We are not going to change anything on this step. Just click the Finish button.
That’s it! You should now see your month and year in separate columns.
Data Prep: Split Month and Year and place them on separate cells using the LEFT() and RIGHT() Excel formulas
If your month and year are on a single cell but don’t have a space or any symbol in between them, we are to split them using Excel formulas.
Below are some of the sample formulas that you can use. Note that the following formulas assume that your month and year are on cell A2.
SAMPLE YEAR AND MONTH
FORMULA TO EXTRACT MONTH
FORMULA TO EXTRACT YEAR
NOTES
201201
=RIGHT(A2,2)
=LEFT(A2,4)
The year and the month have a fixed number of digits (4 and 2, respectively).
20121
=RIGHT(A2, LEN(A2)-4)
=LEFT(A2,4)
The year has a fixed number of digits (4), while the month has either 1 or 2.
If your month comes before your year, you only need to swap the LEFT() and RIGHT() functions and adjust the character length accordingly.
Once you’ve added the appropriate formulas, copy them and paste them as values.
To do this, highlight all cells containing formulas and press CTRL + C. Once the cells are enclosed with broken lines, press CTRL + ALT + V. From the Paste Special menu, select Values, and click OK.
Data Prep: Convert the Month in Text Format (e.g., February or Feb) to Numerical Format (e.g., 2)
Now that we have the month and year in separate columns, we move on to the last step for Data Prep.
You may skip this step if your month is already in numerical format (1 to 12).
But if it is in text format (e.g., February, Feb), please follow these steps:
1. Insert a new sheet.
2. Copy your Month column and paste it into column A of the new sheet.
This formula will get the numerical value of the month.
4. Copy cell B2 and paste it onto the remaining rows.
5. You should now see the corresponding numerical values of your months.
6. Once you’re happy with the result, highlight all the cells with formulas in column B and copy them (press CTRL + C).
7. Go back to your original sheet. Click on the first cell in your Month column and press CTRL + ALT + V. The Paste Special menu will appear. Select Values and click OK.
8. That’s it! Your month should now be in numerical format.
Using the DATE() Formula
Once our Month and Year columns resemble the image below, we have completed the data prep.
We will now proceed to the main course – the actual conversion of month and year to date.
1. Add another column beside YEAR. We can name it DAY.
In this column, enter the number you intend to set as the day for the dates. It could be any number from 1 to 31.
In my example above, I’ve added the first and last day of the month as my days.
2. Next, add another column after DAY. We can name it DATE.
This is where we’ll add the DATE() formulas to generate the dates based on the month, day, and year specified.
3. In cell D2, add this formula:
=DATE(B2, A2, C2)
Don’t worry if you have sorted your fields in a different order. You can change the formula accordingly.
You only have to remember that the DATE() function gets the following parameters (in this order): year, month, and day.
4. Copy cell D2 to the remaining cells in the DATE column.
5. That’s it! You should now have your actual dates.
Remember to choose the number for your DAY wisely if you want to have the dates correctly reflect the Month and Year.
It may not be ideal to choose 31 as the day of the dates, considering that not all months have 31 days.
In my example above, notice that in cell D8, the result was 7/1/2010 even if the month, day, and year are 6, 31, and 2010 respectively. It should have resulted in 6/31/2010, but since this is not a valid date, Excel automatically gets the next closest day — 7/1/2010.
6. Once you’re happy with the outcome, copy the DATE column and paste it as values in your original dataset.
Steps to convert date to month and year in Excel
To reverse the process and convert the date to month and year, you can do either of the following options.
If you want to have month and year in separate columns, use the following formulas:
(Note that the sample formulas below assume that your date is in cell A2).
MONTH
YEAR
=MONTH(A2) Results in the numerical value of the month.
=YEAR(A2) Results in the 4-digit value of the year.
=TEXT(A2, “mmm”) Results in the first three letters of the month (e.g., Jan, Feb).
=TEXT(A2, “yyyy”) Results in the 4-digit value of the year.
=TEXT(A2, “mmmm”) Results in the complete name of the month (e.g., January, February).
=TEXT(A2, “yy”) Results in the last two digits of the year (e.g., 22 for 2022)
If you want to have the month and year merge and share the same column:
MONTH AND YEAR
RESULT
=YEAR(A2) & “-“ & MONTH(A2) Combine the Year and Month with a dash (-).
2022-01
=MONTH(A2) & “/” & YEAR(A2) Combine the Month and Year with a slash(/).
01/2022
=TEXT(A2, “yyyymm”) Combine the Year and Month without a space or symbol in between.
202201
Conclusion
Converting dates can be tricky if you’re not adept with the options available in Excel. But I hope the suggestions above will help you easily convert dates without much hassle.
Data sorting (or the process of arranging data based on a field or a set of fields) allows us to analyze data more effectively.
If you want to temporarily sort your data in a particular order but want to be able to revert to the original data arrangement anytime, you’re in the right place.
In this tutorial, I’ll show you ways to unsort or revert to the original data order in Excel.
Table of Contents
Revert to the Original Data by Undoing the Data Sort
If you have just applied data sorting to your data by dates (and have not closed the file yet), you can go back to the original data arrangement by simply undoing the steps.
To do so, just press CTRL + Z (or Command + Z for Mac).
You may need to do it a number of times until you see your data sorted back to its original state.
IMPORTANT:
If you have performed other tasks in the file after data sorting, these actions will have to be undone too.
Please note that by default, Excel only allows up to 16 actions that can be undone. If you have done more than 16 actions post-data sorting, then this method may not work.
You may need to find a way to acquire a copy of the data in its original arrangement or manually re-arrange the data. Once you do so, you can refer to the succeeding methods to revert to the original order anytime.
PRO TIP:
If you want to view again the data sort that you have previously applied, you can press CTRL + Y to redo the steps.
Revert to the Original Data using a “Sort Order” Column
This method will work if you have not sorted your data yet – meaning it’s still in its original state.
1. Before applying any data sort, insert a new column next to your data.
We’ll name this helper column ‘Sort Order’.
If your data is on a table, the new column will automatically be added as part of the table (as shown below).
If not, you must reselect the entire data (including the new column) before sorting.
2. Next, we’ll add sequential numbers to this column – starting with 1.
By now, you may notice that we are adding this column to record the current order of the data rows.
Type ‘1’ in the first row and drag the fill handler down (up until the last row).
Notice that the column is now filled with 1’s.
Click on the Auto Fill Option that appears and select Fill Series.
The sequential numbers should now appear in the column.
Now, you’re all set!
3. You can now continue sorting the data however you like – you can sort them alphabetically, sort them by last name, or in ascending or descending order based on the numbers.
4. Once done, you can revert to the original data order by simply sorting the data using the Sort Order column we have added. That’s it!
You can hide this column (or delete it) when you no longer need it.
Revert to the Original Data using a Data Backup
If you don’t like adding a new column to your dataset, you can do the most basic technique to easily revert to the original data – create a copy of the worksheet or the file prior to data sorting.
To create a copy of the worksheet:
1. Right-click on the sheet and select Move or Copy.
2. The Move or Copy menu will appear.
Select where you intend to insert the copied sheet (see Before sheet).
Tick the Create a copy textbox and click OK.
3. And that’s it! You have successfully created a copy of your dataset.
You can now proceed with applying your desired data sort.
IMPORTANT: Remember to apply the data sort on the duplicate copy (not the original one).
To create a copy of the file:
1. Close the workbook.
2. Right-click on the file and select Copy.
3. Paste it on the same folder (or on a different folder, if you like).
Right-click on a blank space inside the folder and select Paste.
And that’s it! You can now proceed with applying your desired data sort.
Just remember to do it on the duplicate file, not on the original one.
Conclusion
Sorting data has many uses – it helps us understand, for example, which group performed better based on the numbers. We can also use it to quickly find records after sorting them alphabetically using custom autofill.
Whatever the purpose, there will be instances when we also need to go back to the original arrangement of the data. I hope the suggested methods above help you do so with ease.
Excel is a great way to store, compute, and sort information; however, there may be times when you want to save the tables in Excel as images for use on the web, brand print material, or for training.
Table of Contents
Use the Copy Feature in Excel to Save an Excel Table as an Image
Highlight the table that you want to save as an image.
Click the down arrow next to the Copy button in the Clipboard group and click the “Copy as Picture…” selection.
Open up your favorite image editor. You can use Paint™, Paint 3D™, Photoshop™, GIMP™, or Photos™ on Mac™. For this tutorial, we will use Paint™.
Click the Paste option in Paint, and the image appears on the canvas.
Resize the canvas by moving the small white rectangles on the canvas with the mouse so that they match the size of the picture.
Edit the image as needed and save the file.
Use the Print Screen Button to Save an Excel Table as an Image
Open the worksheet you wish to save as an image and press the Print Screen (PrtScr) button on your keyboard.
Open a new canvas in your image editing software, and press CTRL + V or CMD + Vif you use a Mac.
The screenshot will appear on the canvas and will need editing to isolate the Excel table from the complete image.
Note: if your keyboard does not have a Print Screen button, Windows users can use the Fn + Windows Logo Key + Space Bar combination to take a screenshot. Mac users can take a screenshot with CMD + Shift + 3 together.
Use Shortcut Keys to Save an Excel Table as an Image
Once your worksheet is open in Excel, press CTRL + C or CMD + V for a Mac to store the picture in the computer’s memory.
Open your image software and press CTRL + V or CMD + V on a Mac to paste the picture on the blank canvas.
Edit the picture to fit your needs.
Conclusion
Now you have discovered the different methods for saving an Excel table as an image. Explore the many other topics on our blog to continue increasing your knowledge of Excel.
