Tips For Excel On How To Select All

To ensure that data outside of this age range isnt entered, go to Data-Data Validation-Setting, input the conditions and shift to Input Message to give prompts like, Please input your age with whole number, which should range from 18 to 60.

In order to deal with tons of big data, you cant ignore the important role Excel plays in daily work.

However, both for beginners and advanced users, there are still many useful tips and tricks that are inevitably overlooked.

Here are 20 useful Excel spreadsheet secrets you may not know.

Please note that all these functions are based on Microsoft Excel 2010.

Tips For Excel On How To Select All

One Click to Select All You might know how to select all by using the Ctrl A shortcut, but few know that with only one click of the corner button, as shown in the screenshot below, all data will be selected in seconds.

Open Excel Files in Bulk Rather than open files one by one when you have multiple files you need to handle, there is a handy way to open them all with one click.

Select the files you would like to open then press the Enter key on the keyboard, all files will open simultaneously.

Shift Between Different Excel Files When you have different spreadsheets open, its really annoying shifting between different files because sometimes working on the wrong sheet can ruin the whole project.

This function is also applicable to other files like different Windows tabs in Firefox when opened using Windows 7.

Create a New Shortcut Menu Generally there are three shortcuts in the top menu, which are Save, Undo Typing and Repeat Typing.

However, if you want to use more shortcuts, like Copy and Cut, you can set them up as follows: File-Options-Quick Access Toolbar, add Cut and Copy from the left column to the right, save it.

How to make it Everyone knows that Home-Font- Borders can change different borders for a cell, and even add different colors.

However, if you click More Borders, you will get more surprises, like a diagonal line.

Click it and saveyou can now make it immediately.

The best way is to drag and select X rows or columns (X is two or more) if you want to add X rows or columns above or left.

New rows will be inserted above the row or to the left of the column you first selected.

Speedily Move and Copy Data in Cells If you want to move one column of data in a spreadsheet, the fast way is to choose it and move the pointer to the border, after it turns to a crossed arrow icon, drag to move the column freely.

What if you want to copy the data You can press the Ctrl button before you drag to move; the new column will copy all the selected data.

Speedily Delete Blank Cells Some default data will be blank, for various reasons.

If you need to delete these to maintain accuracy, especially when calculating the average value, the speedy way is to filter out all blank cells and delete them with one click.

Choose the column you want to filter, go to Data-Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks.

Go back to Home and click Delete directly, all of them will be removed.

Question Mark stands for one character and Asterisk represents one or more characters.

What if you need to search Question Mark and Asterisk as a target result Dont forget add a Wave Line in front.

Generate a Unique Value in a Column You are aware of the key function of Filter, but few people use the Advanced Filter, which will be repeatedly applied when you need to filter a unique value from data in a column.

As the screenshot shows, click Copy to another location, which should be in accord with the second red rectangular area.

Then specify the target location by typing the value or clicking the area-choosing button.

In this example, the unique age can be generated from Column C and show in Column E.

The unique value showing in column E can be the contrast of the original data in C, thats the reason why it is recommended to copy to another location.

Input Restriction with Data Validation Function In order to retain the validity of data, sometimes you need to restrict the input value and offer some tips for further steps.

For example, age in this sheet should be whole numbers and all people participating in this survey should be between 18 and 60 years old.

Tips For Excel On Mac