Layers of Protection
As well as protect a workbook, or a worksheet, (as I have written about that here) you can also create layers of protection by using the Allow Users to Edit Ranges feature in Excel. This allows you to protect a range with a password. On the Review menu, next to the Protect Workbook button, is […]
Even better data validation
Data validation is great, I have written about it here and here. And now it can be even better. It has always required a bit of mucking around to get the data validation list to grow automatically if you add more items to the list. I now have a new favourite way of doing it. […]
Automatic Data Conversion, Helpful?
When you paste data into excel, or open a csv file in Excel it “helpfully” does an automatic data conversion on data that it deems needs it. This can be great, it that is what you want. But it is very annoying if it isn’t! It even changes what you type into a cell, for […]
Check out a Checkbox in Excel
Checkboxes have been added recently to Excel. If you don’t have them, you may not have the latest version, so check for updates or your subscription. To add checkboxes to your sheet, go to Insert menu, and find the Checkbox. (Shortcut keys Alt, N, CB.) The value of a checkbox is True if ticked and […]
Displaying Numbers just the way you want.
Displaying numbers in a cell is an important part of making your data easily readable. The way a number is displayed in a cell can be different to the data that is stored in the background. You can change the way the data looks by formatting the cell. Use the Number section on the Home […]
Workbook, Worksheets, and Cells: Excel Terminology.
There is some terminology that is important to understand when using an Excel spreadsheet. The workbook is the whole file. You can change the name of the workbook by saving the file with a new name, or editing the name in windows explorer. A workbook can have one or more worksheets. Each worksheet is listed […]
Much more efficient manual entry
As much as I like to automate tasks, and avoid manual entry, sometimes it is unavoidable. But when it is required, it too can be done in an efficient or inefficient way. I see people type into one cell, then use their mouse to click in the next cell to enter the next piece of […]
Text to Columns to the rescue!
I have had two times in the past couple of days when I have needed to use Text to Columns. (Short cuts keys Alt, A, E) It is a niche tool, but so good when it is needed, saving a lot of time compared to doing things manually. So, what did I use it for? […]
Flash Fill: Fill in a flash!
The Flash Fill tool in Excel is a massive time saver and is very easy to use. Use to complete tasks like splitting out first names from a list with first and last names. Yes, you can achieve this with a formula, but for most people that is too complex, and it is quicker and […]
Stop people messing with your cells.
If you share a workbook with others and want to stop them changing the sheet cells you can protect the sheet. By default all cells are protected. If you want to allow them to enter information in some cells, but not others, then you need to unlock the cells to allow them to be changed. […]