Microsoft Excel Tips

Microsoft Excel is a popular spread-sheeting program used by many people and some on a daily basis.  Below are some really helpful Excel tips around how to use excel to its full potential and become more efficient and save time.

Enter today’s date.

To enter today’s date (Or just Today) into Excel, press Ctrl+; (the semicolon key). Or type the formula =Today()

AutoSum shortcut.

The AutoSum tool that is found under the Home ribbon is quick, but an even faster technique is to select a cell at the bottom of a column and pressing Alt+=, which inserts the =Sum() function just like AutoSum does.

Quickly convert a column of formulas to values.

Here is a great Excel tip to quickly convert a column containing formula to a column that does not have any formulas. Highlight a column containing formulas, right-click and drag the edge of the column to the right, then drag it back to its original position, and release the mouse button, then select Copy Here as Values Only from the popup menu.

Keep leading zeros.

To paste mobile numbers into Excel you will find that the leading Zero will be automatically removed. To preserve the leading zeros, first format the cells as Text, then paste the data using Paste Special, Text.

Dropdown lists tip.

To make a dropdown list on one worksheet, refer to a list of data on a different worksheet, highlight the list, and name it from the Formulas tab, by selecting Define Name, and then enter a name (such as employees) and click OK. Then, on a different worksheet, create a dropdown list from the Data tab by selecting Data Validation from the Data Tools group, select List from the Allow dropdown box, and in the Source box, type = and the name you defined (or in this example, enter =employees), then click OK.

Custom Ribbons.

Excel 2010 and above allows you to create your own custom Ribbon tab, as follows. You can use this to create a ribbon of frequently used buttons and commands.

Right-click on any Ribbon tab and select Customize the Ribbon, then click the New Tab button, add commands from the left pane using the Add>> button, as desired, then click OK.

Produce a quick chart.

To quickly create a chart, select the data cells you want to chart and press the F11 key.

Paste Special to Transpose

In this list of excel tips we aim to save you time. If you have accidentally created a a bunch of rows and you want them to be columns, you do not want to go and retype them all. You would go nuts moving things cell-by-cell. It’s a pretty good chance you already know that you can copy that data, select Paste Special, check the Transpose box, and paste with a different orientation. If you didn’t, you do now.

Screenshot Insertion

Excel makes it ultra-easy to take a screenshot of any other open program on your desktop and insert it into a worksheet. Just go to Insert tab, select Screenshot, and you’ll get a dropdown showing a thumbnail of all the open programs.

Delete blank rows. 

You may have a spreadsheet with blank rows that are spread throughout the worksheet. Instead of deleting each blank row individually, you can delete all blank rows at once. For this to work, your header rows must be on the first row of the spreadsheet. Select the entire columns that contain your data by clicking on the letters at the very top of the columns.

• On the Data tab, Sort & Filter group
• select Filter. Click the drop-down arrow on the right side of the first column of your data
• uncheck (Select All), and check (Blanks).
• If any numbers are still visible, go to the second column of your data and repeat the step above. Continue to repeat the steps for each column until no data appear.
• Select the filtered rows and go to the Home tab, Cells group, and select Delete. On the Data tab, Sort & Filter group, select Clear.

Use $ to Prevent Shift

When you write a formula, you reference cells by their position, such as A1. If you copy a formula and paste it in the next cell down, Excel will shift that referenced cell, so it would say A2 instead. To prevent shifting, use the $. So in that case, saying $A1 would prevent a shift in the column (A); A$1 prevents the shift in the row (1), and $A$1 prevents the shift change in any direction when copying a formula.

Add More Than One New Row or Column

To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select “Insert.”

Quickly resize a column to fit contents.

You may have data in a cell that is much shorter than, or too long to fit in, the default width of a column. Instead of trying to manually adjust the width of the column to get the right size, double-click the boundary between two column headers (eg, the line between the A and the B for the first and second columns), and the column size for the column to the left will be perfectly sized to accommodate the cell with the longest text.

Track down cell dependencies

In complex worksheets, where the value in one cell impacts several other cells and formulas, it’s helpful to understand the relationship between those cells – especially if it’s a spreadsheet you didn’t personally create. This tip will help you troubleshoot and understand what’s going on.

Select a cell in your worksheet and then press Ctrl-Shift-] (that’s Control-Shift-Right Bracket) and all of the cells that are dependent on the selected cell will be highlighted.

Bonus tip: With a cell selected, click on Trace Dependencies from the Formula ribbon and you’ll see lines from the cell to the dependent cells. Click Trace Dependencies a second time and you’ll see the dependencies of those cells.

Extract characters from the left of a text string. 

You may need to extract a portion of data to the left of a text string. For example, you may need to extract the area code of a phone number. Use the function LEFT(Text, Num_chars). For Text, reference the cell that contains the text string. For Num_chars, enter the number of characters to the far left of the text string to extract. Click OK.

Extract characters from the right of a text string. 

You may need to extract a portion of data to the right of a text string. For example, you may need to extract the last four digits of a National Insurance number. Use the function RIGHT(Text, Num_chars). For Text, reference the cell that contains the text string. For Num_chars, enter the number of characters from the far right of the text string to extract. Click OK.

Extract characters in the middle of a text string. 

You may need to extract a portion of data in the middle of a text string. For example, you may need to extract digits in the middle of a product number. Choose the function MID(Text, [Start_num], [Num_chars]). For Text, reference the cell that contains the text string. For Start_num, enter the position of the first character to extract (eg, if you wanted to start extracting at the fourth character in a text string, the Start_num would be 4). For Num_chars, enter the number of characters from the Start_num of the text string to extract. Click OK.

Merge multiple cells into one text string. 

You may need to combine data from various cells into one text string in one cell. You can also include spaces, symbols, etc, in the new text string you are creating. Choose the function CONCATENATE (Text1, Text 2, etc). For Text1, enter the cell reference, text, or any other characters for the beginning of the text string. For Text2, enter the cell reference, text, or other characters for the next part of the text string, and so on. For example, if you had a first name in cell A1 and a last name in cell B1, and you wanted to combine the first and last name into one cell with a space separating the two, you would reference cell A1 for Text1, enter a space (in quotes) for Text2, and reference cell B1 for Text3: CONCATENATE (A1,” “,B1).

Change multiple cells by the same increment/ decrement

The next Tip in this list of great Excel Tips will be usre to be a big time saver. For example, to reduce a number by a percentage, take your percentage and subtract it from 100, then add a decimal in front of it. In my example, 100-2 is 98, so I’m going to be using .98 on my spreadsheet.

If you want to increase by a percentage, do 1.xx, where the xx is your percentage. For example, if you wanted to do a 2% increase, you would use 1.02.

Hers how to do it

• In an empty cell, put your number in it (.98 in my example).

• Copy that cell (Control + C).

• Highlight all of the numbers you want to change by this percentage.

• Go under your Paste menu and choose Paste Special. Choose Value and choose Multiply. After this, all of your values should be changed by your percentage.

Produce random numbers. 

To produce a list of random numbers, click in the cell that will contain your first random number. Use the function RANDBETWEEN. For the Bottom, enter the lowest number in the range of random numbers (eg, if you wanted numbers between 1 and 100, 1 would be the Bottom). For Top, enter the highest number in the range of numbers (100 for the previous example). Drag that formula down (or across) as many times as needed to get the number of random numbers needed.

Remove duplicates. 

A dataset can have duplicate entries, such as a customer accidentally listed twice in a CRM database. To eliminate duplicate information, select the data you want checked for duplicates and go to the Data tab, Data Tools group, and select Remove Duplicates. Select all the columns you want Excel to check for duplicates (be careful only selecting something like last name when you could have multiple customers with the same last name). Click OK.

Increase numbers by a percentage.

Excel can be used to increase numbers by a certain percentage. First, insert the amount of the percentage as a decimal added to 1. For example, a percentage increase of 20% would be entered as 1.2 in a blank cell. Copy that number. Next, select the range of cells that should be increased by the percentage. On the Home tab, Clipboard group, select Paste, then select Paste Special. Select Multiply, then click OK. Your numbers will be changed to reflect the percentage increase. See the screenshots below for an illustration. In this example, there is a list of products with their current prices. In order to change the prices to reflect a 20% increase, 1.2 was entered in a blank cell (D1). That cell was copied, and the current prices were selected (B2:B10). Under Paste Special, Multiply was selected, and the prices for each product changed to reflect a 20% increase.

Add line breaks within a cell.

If you have a lot of notes and you don’t want them all to appear in one continuous sentence; Instead each note to appear on a separate line within the cell. The problem is, if I just click Enter, my cursor will go to the cell below it.

Put your cursor in the cell where you want to put a line break.

Press Alt + Enter on your keyboard.

That’s it! Your cursor will now move down to a new line, within the cell, and you can enter your text there.

Change the case of text to capitalize the first letter of each word and all other letters to lowercase. 

To change the text to capitalize the first letter of each word and make all other letters lowercase, click the cell where you would like the converted text to appear. Choose the function PROPER. For Text, select the cell that contains the text to be converted. Then copy the formula down for all text.

Disable AutoCorrect one time for one situation. 

AutoCorrect can be very useful for fixing unintentional grammatical errors. However, sometimes Excel will continue “correcting” something that is not an error and should not be changed. You can instruct Excel to stop “correcting” this temporarily by typing the word, hitting the spacebar once, then clicking Undo or pressing Ctrl+Z.

Recover an unsaved workbook. 

Excel will autosave your Excel workbooks (by default, every ten minutes). If you have not previously saved your workbook and then close the workbook by mistake or if your computer closes without the workbook being saved, you can recover the document. Go to File, Open, Recent Workbooks, Recover Unsaved Workbooks, and choose the file that was closed before it was saved.

Excel Keyboard Shortcuts

What would be a list of Excel Tips without showing you some Excel keyboard shortcuts. Excel, like any great software, has many excellent keyboard shortcuts. Here are some of the best.

Ctrl+; – Inserts today’s date.

Ctrl+Shift+: – Inserts the current time (the colon is what is in a clock reading, like 12:00).

Ctrl+Shift+# – Changes the format of a date.

Ctrl+5 – Applies a strikethrough to the text in a cell.

Ctrl+0 – Hides the current column.

Ctrl+9 – Hides the current row.

Ctrl+F6 – Switches between open workbooks (that is, open Excel files in different windows).

Ctrl+`– That’s the accent mark, up by the 1 key. This combo toggles the view in the sheet to show all the formulas.

Ctrl+PageUp or PageDown –Quick shift between the sheets in the currently open workbook.

I hope you have enjoyed this list of great Excel Tips and get use out of them next time you are using Microsoft Excel.