Tips and Tricks for Using Excel To Its Full Potential
Microsoft’s Excel is a powerful tool that can calculate, graph, organize and filter data. There are Excel formulas for doing almost anything, but sometimes the problem is knowing the formula. Check out our Excel productivity tips below, they will help you work more efficiently and do less manual work.
Select All with One Click
Most know that Ctrl + A will select all, but if you click the corner between the A column and 1 row, that button will select all the data in the Excel Sheet.
Shift More Easily Between Different Excel Files
It can be annoying to switch between different spreadsheet files. Use Ctrl + Tab to shift between different Excel spreadsheets freely. Note that it won’t shift between Excel files that are minimized.
Add More Than One New Row or Column
Adding one new row or column is easy, but if you need to insert many new rows or columns, it is not very efficient. The best way is to drag and select the number of rows or columns you want to add. Right click on the highlighted rows or columns then choose ‘Insert’ from the drop down menu. Voila!
Note that new rows will be inserted above the row you first selected and new columns will be inserted to the left of the column you first selected.
Filter Sets of Data
When looking at sets of data, especially large ones, sometimes you may only want to see data that fits certain criteria. Filter allows you to do that.
Filters can be added to each column of your data by clicking the Data tab and selecting ‘Filter.’ Clicking the arrow next to the column headers allows you to choose specific rows you want to show and organize the data in ascending or descending order.
Transposing data can transform pre-existing rows into columns and vice versa. Copy the area you want to transpose, move the pointer to where you want the first row or column to begin, right click, select ‘Paste Special’ then choose the transpose option.
Input Values Starting with 0
When an input value starts with zero, Excel will delete the zero by default. Rather than reset the Format Cells, this problem can be easily solved by adding a single quote mark ahead of the first zero like ‘0001.
Combine Cells Using ‘&’
Spreadsheets can often split out data to make it as exact as possible, but there are some times when you want to combine that data. The ‘&’ sign in your function can do that. This can be handy for combining first and last names or city, state, and zip code.
To do this, find the two cells to be combined, for example, A2 and B2. The formula would be =A2&” “&B2. If you just to type =A2&B2, there would not be a space between the information in both cells, the quotation marks tell Excel to put, in this case, a space between the two cells’ data.
Double Click to Rename a Sheet
Just click twice on a sheet to rename it. This is more efficient than right clicking and selecting ‘Rename.’
Combining Two Sets of Data
If you’ve ever had two sets of data on two different spreadsheets that you wanted to combine, VLOOKUP and INDEX MATCH can both be powerful tools. Here are the differences:
- VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, then the INDEX MATCH function will significantly decrease load time in Excel.
- INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, you would have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.
Combining data with VLOOKUP:
In order to do this, be sure that you have at least one column that has identical information in either data sets like email addresses or SKU numbers. The order does not have to be identical.
Formula: =VLOOKUP(lookup value, table array, column number, [range lookup])
Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet.
Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1.
Column Number: This tells Excel which column the new data you want to copy to Sheet 1 is located in. Count the number of columns from the left.
Range Lookup: Use FALSE to ensure you pull in only exact value matches.
Combining data with INDEX MATCH:
Note that the INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula.
Formula: =INDEX(table array, MATCH (lookup_value, lookup_array))
Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1.
Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets.
Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets.
For more information about Microsoft Excel, get in touch with CyberStreams at (425) 2 or firstname.lastname@example.org.