When you protect a sheet with a password, you must make a note of the password, otherwise you create problems for yourself. Did you know you don't have to supply a password? To apply sheet protection, right click the sheet tab and choose Protect Sheet. When the Protect Sheet dialog displays just press Enter or click OK. This protects the sheet with no password. This stops accidental changes and you can easily unprotect the sheet to make changes. You can…
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
(more…)Excel 2013 added the Data Model to Excel. The Relationships option (Data ribbon) is part of that model. It allows you to create relationships between tables so that you can use a PivotTable to report on multiple tables. You have to have used the formatted table option to use Relationships. See my December 2016 INTHEBLACK article for an example. The keyboard shortcut to create or edit, a Relationship is easy to remember - it is Alt a a Pressed in…