Microsoft Excel is one of the most popular pieces of software for all kinds of projects. After getting the hang of the program, there are plenty of other things to learn and we can show you Excel tips and tricks that will make this software even more useful. Here are ten to get you started.
1)Highlight Alternate Rows To Make Them Easier To Read
This is great if you are looking at a lot of information. By highlighting alternating rows you will be able to tell the difference between rows and read everything much easier.
To do this, just highlight the information to want to format, first. Click Format and then Conditional Formatting. A box will appear that will let you choose several options. Choose Formula Is and then enter the information that you want highlighted. Something like =MOD (Row () ,12=0. Then just click Format and select the color that you want to use.
2) Use Custom Lists
Custom lists will help you fill in repeated information without having to manually type something over and over again. If you are doing a repetitive job this can be a lifesaver.
Simply click on the Microsoft Office button and inside of Excel options you can click Custom List. All you will then have to do is input each item, separated by commas, and then click Add. Now all you have to do is select AutoFill whenever you need the list filled in.
Shortcuts can help you navigate and make changes to your spreadsheet quickly. If you have a lot of work and are short on time, this can be the difference between success and failure. There are endless shortcuts inside of Excel, but a couple are the most commonly used.
There are a few you should know:
– By pressing Control, Shift, and @ you can apply time formats
– Press Control and ; to enter the current date
– Press Control, Shift, and : to enter the current time
– Press Control and F6 to go to the next workbook’s window
– Press Control and ‘ to switch between cell values and your formulas
4) Sorting Data
Sorting data is one of the most important things you can do to keep track of information. Luckily, Microsoft Excel features a Sort and Filter option that is both easy to find and easy to use.
The Sort & Filter option is found at the top of the window and can easily help you make sense of all of your data. Sorting things properly is the key to a proper spreadsheet in many cases.
5) Link Data
Having a summary page is ideal when you have a spreadsheet with a lot of information on it. In order to help create this you can use the Paste Special option.
All you have to do is copy the formula you want to use and choose the cell you want to connect it to. By right clicking in the cell, you can select the Paste Special option and then click Paste Link in order to link the formula between two spaces.
6) Hide Information That You Don’t Want Printed
Sometimes when you make a spreadsheet you don’t want to print all of it. This tip will let you keep information handy on the spreadsheet as you are putting in data, or trying to make sense of it, but you won’t have to delete anything before you print it in case you need to edit it later.
Select a row or column that you don’t want to print and then right click the information and select Hide. When the document prints, anything that you have selected will not be printed. This is a very simple tip that helps a lot.
When you have a spreadsheet with hundreds of numbers and pieces of information you can filter it to find things easier. There are many reasons that you might need this, but many people still don’t know how to do it properly.
Simply select the header of the column that you want to filter and then select Filter and choose the option that you want. You can also remove the filter in the same way.
8)Creating Drop Down Lists
A drop down list will allow you to easily put in information from a few options if you always need information of a certain type. This is similar to a quiz and can also be used to gather information from people without having to ask them and input the data individually.
When you want to create a drop down menu, select Data Ribbon and Data Validation. You will then create a list of options. When you go back to the spreadsheet there will be an arrow next to each cell, once you click that you will be able to choose the appropriate answer.
9) Creating A Table From Data That You Have Already Entered
Tables can make it much easier to analyze the data once you have input all of it. You probably have clients that would much rather look at a table than a spreadsheet as well. Creating one is easy if you know what to do.
There are several styles available, but you can also create a custom style. To do this, select Range of Cells, Home, Styles Group, and then select the option that says Format as a Table. Once you add all of the elements to it and select the options that fit the style you need you are ready to go.
10) Finding Specific Data Cells
If you need a certain piece of specific information, the Look Up function is going to be just what you need. This will help you avoid looking through thousands of pieces of data manually.
All you have to do is type VLookUp followed by the thing you are searching for inside of parenthesis, along with the range of cells that you want to search. Take this for example: if you want the name James, you would type in Function=VLookUp(James,A7:E12,FALSE).
The FALSE part is important because it lets Excel know that you are searching for an exact match, not something similar.