Microsoft excel

Microsoft EXCEL Tech Tips

Microsoft Excel is a popular spread-sheeting program used my many people and some on a daily basis.  Below are some really helpful tips to help you get the most out of using Microsoft Excel

Enter today’s date.

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


Formula

DescriptionResult
=TODAY()Returns the current date.27/02/2018
=TODAY()+5Returns the current date plus 5 days. 4/03/2018

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.

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 SpecialText.

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

You’ve got a bunch of rows. You want them to be columns. Or vice versa. 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.

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.”

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.

Change multiple cells by the same increment/ decrement

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.

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.

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.

Leave a Reply