Increase accuracy of data entry using Data Validation.
Using data validation in Excel means you can put restrictions on what can be entered into the cell, increasing accuracy. There are a lot of different options and settings within the data validation area. Here we have a drop down list of choices that the user can select from. You could also require the data […]
Time saver: Enter data in multiple cells at once.
Select cells to enter the data into (Holding down the ctrl key if they are not in one continuous block). Type into one cell. Press Ctrl + Enter, the data is entered into all the selected cells.
Make your life easier: give a cell a name.
You can give a cell a name in excel. So, rather than being called A5 or E3 the cell could be GST or Total. This makes you formulas easier to understand. See below in the formula bar for an example, this means more than E10+E23+E34+E40. This also means you can use the name box to […]
Save time: Link to a cell
You can insert a link within excel. This link could be to a web site, or a place within the spreadsheet. That way you can navigate around the worksheet or even to a different sheet without lots of scrolling and clicking. If you have a large sheet or a workbook with a lot of sheets […]
Quick Information: Use the Status Bar
In the bottom right hand corner of your excel sheet is the status bar. When you highlight a group of cells the status bar tells you some information about them, such as the average, count(how many cells are highlighted) and the sum. You can add extra pieces of information to show here by right clicking […]
Avoiding manual data entry
Instead of doing lots of typing, did you know you can fill a series of cells based on just one manually entered cell? For example, if you write in a date, and take your mouse to the bottom right hand corner of the cell, (it should turn into a small plus sign) and RIGHT click, […]
Power Query
If you are having to collate information from different sources into one place, learn about Get & Transform. (It used to be called Power Query in #excel.) It will change your life. Here is an example: Seven spreadsheets, all with some columns in common, but inconsistent column headings, and the need for the client to […]
Excel Tables: Why you should be using them.
If you have data that is in a table structure, i.e. column headings and rows of information then formatting your Excel data as a table has a lot of advantages. To format your data as a table, you can use the Format as Table button on the Home Menu, (Shortcut keys Alt, H, T), or […]
Conditional Formatting
Conditional Formatting, such a great tool. On the home tab of your #excel sheet. I have used it to highlight all the cells in my column that are over 85. You can use it to automatically highlight the top 10 values, or the lowest 5%, or cells that contain a certain word, or many other […]
Top ten shortcut keys in Excel
Ctrl+; —Inserts today’s date.Ctrl+Shift+:—Inserts the current timeCtrl+0— Hides the current column.Ctrl+9—Hides the current row.Ctrl+`—That’s the accent mark, up by the 1 key. Toggles the view in the sheet to show all the formulas or cell values.Ctrl+PageUp or PageDown—Moves to the next worksheet in the currently open workbook.F2—Start editing the current selected cell.Shift+F10—Opens the right-click menu for the cell […]