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 your formulas easier to understand. See below in the formula bar for an example: AccomodationTotal + ActivitiesTotal + FoodTotal + TravelTotal. This means a lot more to someone reading […]
Save time: Link to a cell
You can insert a link within a cell in 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 […]
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 have information in them) and the sum. You can add or remove which pieces of information to show here […]
Avoid manual data entry with the Fill Handle.
Instead of doing lots of typing, did you know you can fill a series of cells based on just one manually entered cell? Using the Fill Handle, you can. For example, if you write in a date, and take your mouse to the bottom right-hand corner of the cell, the pointer will turn into a […]
Super Powerful: 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 and is still often referred to as this.) It will change your life. The Basics You can find Get & Transform on the Data menu. Basically, Get & Transform is […]
Interpreting Formulas in Tables.
You are probably aware of how formula use cell references in Excel. A1 refers to a single cell, A1:A5 refers to a column of cells and A1:G1 refers to a row of cells. If you have a formula in a table, or a formula that refers to a table then the way that the formula […]
More Conditional Formatting Options.
On the home tab of your #Excel sheet or shortcut key Alt, H, L. You can use conditional formatting to simply highlight values of interest. See this post. Other options from within Conditional Formatting are, Data Bars, Colour Scales and Icon Sets. Data Bars Within Data Bars, you get two Standard options: Gradient Fill, and […]
Make your data colour coded with Conditional Formatting
Conditional Formatting, such a great tool. On the home tab of your #Excel sheet or shortcut key Alt, H, L. Start by selecting the cells that you want to apply the formatting too. I have used it to highlight all the cells in my column that are over 85. You can use it to automatically […]