Category Archives: General
At some point in your working life you will be involved in a project of some sort. The type and size of project will vary wildly from a list of “to-dos” through to multi-site, multi-year projects.
Managing these different projects requires different skills and also different ways of keeping track of things. There are lots of tools available out there. The problem is deciding which one to use.
If you are involved in the likes of managing the building of the Olympic stadia, or a new rail line/network, the likes of Primavera might be best suited, allowing you to build multi-site, multi-project programs. Working on single, long term projects with a requirement to track resources and costs, then something like Microsoft Project or similar might be your best option.
For many of us though, both of these would be a serious case of over-kill. The cost of licences etc. is also prohibitive which in turn means that only a very limited number of people are likely to have access to the information.
So, what is available to you for quick simple projects?
This is where software like Trello, Microsoft Planner or Asana might be the tools for you. They are all very similar in their functionality and for two of them come in either free or paying version. I’m going to look at Trello and Planner.
This is an online tool. There is a free version and a chargeable version with added functionality beyond the standard free one. You’ll need to create an account and then log in to your account to view the planning boards.
Click on Welcome Board and this opens a useful hints and tips page to give you an overview of everything that can be done within the application.
The left hand column has a number of cards with hints and actions to perform to help you discover the basics of Trello.
To create a new project, click on CREATE NEW BOARD. This will start what Trello calls a board. Think of this as a notice board where you can pin job cards. Create new cards by clicking on ADD A NEW CARD. Give your task or activity a name and click the ADD button.
Each card that you create can then be edited to add more detail around the job or activity that needs to be completed. To view the details card, click on the job card.
Here, you can add a lot of detail about the task;
- Members – who will complete this task?
- Labels – highlight the task with colour coded labels
- Checklist – add a task specific to do list
- Due date – when does this need to be completed by?
- Attachment – add any relevant files/images etc.
Using the buttons on the right hand side add as much detail as required.
Members: you can invite people to be part of your project team, and once they are on board, you can assign tasks to them. Each task can have multiple people assigned to it.
Labels: These are coloured tags that can be assigned to the card. Each tag can be customised with text. There is an option to use colours suitable for colour blind people.
Checklist: add lists of actions that might be required to complete the task. These could just be a list to remind you, or a breakdown of everything that needs to be done.
If something on the checklist ends up as quite a big task in its own right you can use CONVERT TO CARD, making it a new, separate task in the project.
Due Date: when does this need to be completed by?
Attachment: add any attachments that might be needed or are relevant to the task in hand.
There is an area for comments. People can add comments, attachments, mention people or add emojis. This gives the task more of a social element where team members can contribute or comment about the task.
If you want to kept up to date with changes etc. in the activity then you can SUBSCRIBE and get notifications.
Depending on how many things you want to display on the card you will see a number of icons etc. showing you a simple visual summary of the activity.
Back on the main board, you have a number of icons on the right hand side:
People: displays project team members
Activity: shows any activities that have taken place or comments added.
Power-Ups: You can add extra functionality to Trello. If you have the free version, you can only enable one power-up per board. To add more than one then you need to upgrade your account to TRELLO BUSINESS CLASS. There is a long list of power-ups available. Here is just a short sample…
Filter: Use the filter to quickly find things within your project – keywords, tag colour, people or due dates.
Stickers: Click and drag stickers onto your cards.
Settings: A number of options such as changing the background appearance of your board, controlling member permissions etc. Again, there are a number of options that are only available to business class members.
So, what do you get if you choose to use Microsoft Planner?
If you have used Trello, then using Planner is almost the same. It has the distinct advantage of being fully integrated into Office 365 and all its applications making collaborative working simpler and creates a smooth workflow across multiple applications within a business. Unfortunately, I cannot do a direct comparison to the Business Class version of Trello as I don’t have it. For the purpose of this blog I am more interested in the general functionality of both applications.
To access Planner you need to be using the enterprise version of Office 365. Log in through the portal and click on the Planner icon.
When you open Planner it will take you to the PLANNER HUB or landing view for the application.
Here you will see any plans that you have either created yourself or are part of. If you are involved in a plan click on the plan name to see the details. If any of your plans are ones that you go to regularly etc. you can make them a favourite by clicking on the three dots next to the name and selecting ADD TO FAVOURITES. This will add a shortcut in the left hand panel as well as display a mini dashboard. Although limited in functionality and detail, it is a useful feature which is not present as a standard feature in Trello (free version).
To remove a plan from your favourites click on the three dots in the mini dashboard and select REMOVE FROM FAVOURITES.
You can create a new PLAN by clicking on NEW PLAN. Complete the details on the new window to name the plan, add an optional description and decide whether you want members to receive mail notifications generated by assigning tasks etc.
Whereas in Trello you create a new board, in Planner you create a plan.
Give your plan a name, and decide whether the plan is open to all to view or limited to team members only. When you look at plans on the PLANNER HUB, any plan icons with a padlock are limited to members only.
Once complete click on CREATE PLAN.
When a new plan is created, a group is set up with its own calendar, mailbox, SharePoint area etc.
When you start a new plan the initial layout and way to get started are the same as Trello. Planner uses BUCKETS. By default, you start with a TO DO bucket where you can add tasks. Think of the buckets as top level summary tasks. You then add detail to each bucket by adding tasks. To add new buckets click on ADD NEW BUCKET.
When you create a new plan the default bucket that is created is TO DO. You can rename this by clicking on TO DO and overwriting it to whatever your task summary name you want to give it. To add a task, click on the plus symbol.
You will ned to build a team so tasks can be assigned to the various team members. Click on the MEMBERS arrow and type in the name of someone you want to invite to the plan. As you are linked to the enterprise, the names of every employee should be listed, so just start typing a name and when the name appears click on it to add it to the list.
Now, when you create a task you can name it, select a name or several names to assign the task to, and set a due date. Then click on ADD TASK. This is the bare minimum required. Just like Trello, click on the task card to reveal a details window.
You can set the PROGRESS status for the task (Not started, In progress, Completed). Set a START DATE if necessary.
You can add a more detailed description if that is of help.
Add a checklist if you want to break down the task a bit.
Tick the SHOW ON CARD option if you want people to see that there is a checklist.
If an item on the checklist is later considered too big a task to be a checklist item you can convert it into a separate card. Click on the item to select it then click on the PROMOTE ITEM arrow over on the right hand side.
This will convert the checklist item into a new task that can be edited and assigned like any other task.
Add any useful or relevant attachments. If you tick the SHOW ON CARD option you will get a preview of the file appearing on the card.
Comments can be added to the task. These will form a conversation within the task.
Depending on the number of options you selected to be displayed on the card you will see something like this for each task. Note that you can only display one of the SHOW ON CARD options at a time at present.
You can switch views from the BOARD with all your buckets and tasks, to the CHARTS view, which is the mini dashboard giving you a quick overview of the plan.
Click on CHARTS to get this;
Obviously, the more tasks and assignments you create the more information will be displayed here, but it’s a nice feature to get a quick summary of the plan in a visual format.
Use the drop down GROUP BY option to change how you see your plan’s status.
This is a useful feature so you can quickly review your plan’s tasks in a variety of ways. To quickly highlight any tasks assigned to a specific person, go to the MEMBERS area.
Click on the image or the initials of the person whose tasks you want to see and in the window below, any tasks assigned to that person will turn grey.
This will work in any layout view.
So, whether you choose to use Trello, Planner or Asana, it’s down to the individual people who were assigned tasks to do them and update them so everyone involved in the project is aware of what is going on and up to whoever the project manager or equivalent is to oversee everything. At the end of the day, no software, however good, manages a project…people manage projects. The tools are there to hopefully make managing them a bit easier.
If you want to look at Asana for comparison go to Asana.com. This comes in three versions: free, Premium and Enterprise. Below is a screenshot taken from their website. A you can see it has a similar feel and layout to Trello and Planner. As with Trello, some of the neater features such as creating dependencies are only available in the paying versions.
Planner is probably the one with the fewest features at the moment, but is relatively new and there are regular updates so no doubt over time, Planner will add a whole raft of features to bring it more in line with the likes of Trello and Asana. The main advantage that Planner has over the other two is that is fully integrated in the Office 365 suite of applications so working from one application to another is pretty much seamless…you just have to be on an enterprise version of Office 365, whereas the other two are standalone packages.
The choice is yours.
If you’re already using One Note then you will know how useful it can be and also how easy it is use. If you are amongst the majority who have never used it or as is often the case, not even noticed it is installed on your laptop or PC, then read on…
As the name suggests it’s a notebook, but it offers a lot more than just somewhere to jot down the odd comment or two.
One Note allows you to organise data, notes, web links, images etc. into sections, with each section containing pages. Each page is quite literally a blank canvas where you can add notes etc. anywhere you like. So unlike Word which is fixed, in other words, you start writing at the top of the page unless you press enter 10 times to start further down the page, you can start typing anywhere on the page, just like a paper notebook, with the advantage of being able to move or copy notes anywhere within the page, notebook or even to another notebook.
Let’s look at the structure of a notebook;
- First of all you can have multiple notebooks.
- Each notebook can be divided into multiple sections.
- Each section is made up of pages and sub-pages.
There is a great diagram showing how a notebook is structured within One Note itself which you can see below.
To create a new notebook go to FILE, NEW and set where the notebook will be created (web, network or My Computer), give it a name and click on CREATE NOTEBOOK.
To add new sections just click on the tab with a star on it or right click on an existing tab and select NEW SECTION. When the tab is created the name of the section is active. Type a name for your new section and press enter. You can rename a tab at any point by right clicking on the tab and selecting rename.
By default, a section has one page. Rather than click or anything else on the tab on the right hand side of the page, you type the page name or heading in the dotted box in the top left hand corner of the page. To add new pages, click on NEW PAGE above the page name tabs and use the same steps as above to name your new page.
Once you have your basic structure, it’s down to adding content.
You can add all sorts to your notebook;
- Screen clippings (using the Snippet tool)
- Files (as attachments)
- Files (readable – whole printout)
- Scanned documents
- Voice memos
- Video memos
Note there isn’t an insert text option – just start typing wherever your cursor is.
To help you flag content, you can add tags. You’ll find these on the HOME tab. You can easily create to do notes, highlight questions etc. You can add new ones and you can move tags that you use regularly to the top of the list so you can use a keyboard shortcut to add the tag. Once tags are in place you can search by tag name to find them quickly again.
There is also a DRAW tab, which gives you a number of drawing tools. Pick from various pens, colours and thicknesses to create free hand drawings, as well as draw shapes and graphs. Obviously, this will work much better if you have either a touch enabled screen or a graphics tablet rather than trying to write or draw with a mouse which is pretty hopeless to say the least.
So that’s pretty much everything you can add to a page from within One Note.
Also note that One Note automatically saves anything you do, so don’t go looking for the save button. You can do SAVE AS to save pages and sections etc. as new pages, new sections or even new notebooks.
Now…where One Note sits high above all the other applications is its level of accessibility across applications and devices.
Just look for the One Note buttons in most applications.
You receive a mail that relates to a project you are tracking in one of your notebooks and would like to have a copy of it along with your notes – just click on the One Note button and the open mail item will be copied. You will be prompted to select the notebook and page to send the mail to.
The e-mail in its entirety will now be in your notebook and can be annotated etc. like any other note.
Alternatively you can e-mail pages directly from One Note. If you have minutes from a meeting, action points etc. in One Note just click on E-MAIL PAGE – no need to copy/paste the contents or re-write them.
In IE (bit not Firefox) you can create notes that are directly linked to a web page (One Note linked note), or even send the web page itself to One Note. As with e-mail you’ll be prompted to select a specific page in a notebook. Just right click on the web page and select whichever option you prefer.
From within PowerPoint you can add LINKED NOTES. As always, select a page to add the linked note to, start typing something and One Note will add a PowerPoint icon next to the note. Then, from within One Note you can click on the icon and it will open the presentation at the slide you created the linked note from.
Like PowerPoint, you can create notes that have links to documents created in Word. Look for the Word icon next to a note and click on it to open the document at the page relating to the linked note.
You can download One Note to your mobile phone – for both Android and iOS. OK…it doesn’t have all the features of the desktop version but it’s great for quick notes, sending images etc. while you’re out an about. You can view all your notebooks and make edits through your phone.
The final great feature of One Note is that you can access your notebooks from any laptop, PC where you are a user. You can even install One Note on both Android and Apple phones and link it back to your notebooks. The convenience of this is that you can be anywhere – see something interesting, or suddenly come up with an idea, you can make a note, record video or a voice memo and save it straight to One Note which is then automatically synchronised to all your other devices. Even if you don’t have access to any of your devices but can get on the internet, sign in to your Microsoft Live account and access your notebooks from any PC, laptop or tablet without the need to even install One Note. If you have shared notebooks then all of One Note’s features are available to all team members. Brilliant, and so convenient it’s untrue.
For those of you in a work environment you may not be so lucky. Depending on network and access restrictions, you may find yourself limited to using One Note on your PC or laptop only, and unable to create shared notebooks, or link to mobile devices as it all depends on having a Live account which most businesses will not allow. If you can create networked notebooks, then this can be very useful if a team of you are working on a project. Each team member can update files, add comments etc. to the notebook, and any added comments have tags showing who added the comment, as well as the date and time it was added. This makes One Note a great collaborative tool, even if you are restricted to your network. If you have access to the full range of functionality including mobile apps then even better.
One Note has the potential to be a very powerful tool across multiple users especially for remote teams who need a simple and convenient way to keep in touch and share files, thoughts, images etc. quickly and easily. Even if you can’t use it to its full potential, simply using it as a private notebook is an excellent way to manage all your projects and all kinds of information in a variety of media types.
As a quick note, if you have Windows 10 installed, One Note is pre-installed, but this is only the app version not the desktop version. It works fine, but you don’t have all the functionality of the full version. You will need to download the desktop version direct from Microsoft.
So if you haven’t tried using One Note yet, take a look. Once you start using it you’ll find out just how easy it is to use and how useful it can be.
Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.
So what is going on?
This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.
And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.
As an example I’ll use the data which caused me the most recent problem.
The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.
The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.
This was done using a COUNTIF function. However, there were in fact 6 records that contained the number 26.
So on the one hand the COUNTIF function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the COUNTIF function.
Initially I looked at formats – text vs. numbers for example.
I also looked for any spaces that might have caused an issue.
I manually added a COUNTIF function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.
Finally, realising that this was an extract from a non-Excel source, I tried using the CLEAN function suspecting that there may be some non-printable characters in there somewhere. The CLEAN function removes all non-printable characters from text. The syntax is very simple:
In a new column I wrote this formula and then copied all the “cleaned” text values and used PASTE SPECIAL VALUES over the original cells and retested the COUNTIF function.
…and hey presto…it found all 6 records.
So next time your functions are not working and seem incapable of identifying values in cells, try applying the CLEAN function to your data, it might just be the solution to your problem.
Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.
It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection, or has questions about just how secure a password-protected spreadsheet really is. When people know the facts without scare tactics or hyperbole, they can make the best decisions for themselves when armed with objective, unbiased information.
As protection platforms go, Microsoft’s products have inherent weaknesses. In its defense, Microsoft has never claimed to have reliable spreadsheet protection. In Office applications, a password is like the lock on your home’s front door; its primary purpose is to keep your friends out. If someone really wants to get in, they will get in.
Try this: open a new workbook, go to Sheet1 and protect it with the password “test” (without the quotes, lower case just as you see it here). Now unprotect Sheet1 but instead of using the password “test”, use the password “zzyw”.
Take comfort that Microsoft is like any other company, in that virtually any application is hackable. Here’s some background on Excel spreadsheet password protection:
When someone password protects a sheet in Excel, they generate a 16-bit 2-byte hash, a technical term for a number generated from a string of text by a function called the MD5 Message Digest Algorithm. An MD5 hash has fewer numeric characters than the actual password text, making it unlikely but not impossible to be replicated. Note that “replicated” is not the same as “duplicated”.
When unprotecting a protected sheet, the password value is compared to the MD5 hash. Excel allows for up to 255 password characters in its worksheet protection scheme. Since it is a case-sensitive scheme, there are over 90 acceptable characters, which translate into the multiple trillions of password text possibilities. Since the combination of possible passwords is much greater than the combination of possible MD5 hashes, some passwords can share the same MD5 hash value.
The MD5 hash is a standard mixing algorithm, executed as follows:
- Take the ASCII values of all characters.
• Shift left the first character 1 bit.
• Shift left the second 2 bits.
• Continue for quantity of characters up to 15 bits, with the higher bits rotated.
• XOR those values.
• XOR the count of characters.
• XOR the constant 0xCE4B.
As you may know, XOR is a logical term associated with a mathematical compound statement, an acronym for “exclusive or”. In this case, statement “A” is the password value you type in. Statement “B” is the generated MD5 hash. The XOR operation returns TRUE when only one of its combinations is TRUE. This translates to more than one password value possible in the context of a truth table:
A B XOR Result
T T FALSE
T F TRUE
F T TRUE
F F FALSE
By the way, if you wanted to reproduce the actual password, and not just a compatible one, it’s a virtual certainty that it literally could not be accomplished during your lifetime.
There are 94 standard characters (26 of A-Z; 26 of a-z; 10 of 0-9; and 32 special such as #,%,!, and so on). That means, for every character there are 94 possibilities.
To extrapolate using the example of an eight-character password, the number of characters to test is
94 x 94 x 94 x 94 x 94 x 94 x 94 x 94
At the hefty pace of 100,000 password attempts per second, it would take 1,932 years to recover the exact password. And that’s just with 8 characters; with the 255 max it can take millions of years.
What all this boils down to is, if you don’t want to expose your Excel spreadsheets to *any* possible password circumvention, don’t share them. However, the likelihood of someone guessing a compatible hash is very slim, though there are commercially-sold password cracking programs.
One thing is sure, you are in good company: the whole world is in the same boat with this Excel protection issue. As you understand the spreadsheet password protection scheme, you can make your own informed decisions about what and what not to risk putting in your workbook, and how or with whom you share access to your workbooks.
Blog kindly provided by Tom Urtis (Excel MVP). If you are interested seeing more blogs and tips by Tom follow him on Twitter (@tomurtis) or visit his website Atlas Consulting
If you have ever looked around for tutorials on charts or read through books on charting techniques, one thing that pops up regularly is the X Y Chart Labeler by Rob Bovey.
This is a great little add-in, and best of all it’s free. You can download it from http://www.appspro.com/Utilities/Utilities.htm
So what does it do, and why is it so great?
When you create a chart in versions up to 2010, you can add labels to your series with one of the following;
- Series name
- Category name
In most cases this is ample, but what if you wanted to put something different? You could re-label everything in your data table but then it probably won’t make sense to anyone else. If you use scatter diagrams or bubble charts you can’t even do that as you need both x and y coordinates to place your markers and these act as labels. Identifying the individual scatter points or bubbles becomes quite difficult. You could add text boxes manually to everything but if the data point moves the text box doesn’t, so you have to move everything again by hand. All quite painful really.
So this is where the X Y Chart Labeler comes in.
Let’s take a simple example using a table showing sales by customer. What might be of interest is to show the name of the account manager against the customer sales. I could show sales for each customer or sales for each account manager but not both at the same time. Not easily, anyway.
You create your chart as normal, but then you create a set of custom labels, or as in this case the names of the account managers. You will need one label per data point/series in order for it to work, but there is functionality within the tool to show/hide individual labels.
Assuming you have installed and turned on the add-in, click on the X Y CHART LABELS tab and click on ADD LABELS.
Pick the series you want to label from the drop down list, then select the cells that contain your new custom labels. Select where you want them to appear (top/bottom/centre) and click on OK.
Your new labels will now appear on the chart. Job done!
If the position is not quite right, click on MOVE LABELS.
Set the number of points you want your labels to move with each click of the arrows, and using the arrows, relocate your labels. You can apply the move to all labels in one go, or select individual labels to apply the movement to.
Other functionality within this add-in;
Manual labeller: allows you to manually label individual data points using either already existing values in cells or by typing a value directly into the form.
Delete labels: as the name suggests, you can delete labels for a whole series or all labels in the chart.
Help: it comes with a searchable help file, although to be fair, you can’t really go wrong using this add-in.
If you have Excel 2013 (and I’m guessing that this will continue to apply to later versions too) you do have the option to attach custom labels, using cells from your worksheet.
Click on the chart to activate it and click on the plus symbol that you see top right hand corner of the chart area. Select CHART LABELS then click on MORE OPTIONS. Near the top of the window that appears on the right hand side, you will see the option VALUE FROM CELLS. Tick the option and then select the cells containing your custom labels.
Knowing that this functionality exists in 2013+, is there a need for the add-in? Probably yes! You still need the add-in to move, show/hide labels etc. more easily than doing it all manually, so from point of view it’s worth having. Perhaps later versions will include this sort of extra functionality, but until then use the add-in.
Now…some of you will be looking at this and thinking “wow, this is brilliant….but they’ll never let me download it at work”. We all have over zealous IT security police and if they don’t know what it is or can’t support it (even though it needs no support) they block it. The add-in runs off VBA code and Rob Bovey, the developer, has not locked the code down, so you, or any despotic IT jobs worth can look at the code quite easily and read all the helpful notes and comments that are in the modules and forms.
The add-in is perfectly safe. Rob Bovey is a recognised MVP or Microsoft Most Valued Professional, so he’s not some random programmer who is dumping dodgy code on the internet. The add-in is also mentioned by the likes of Mr Excel, Jon Peltier and other leading lights in the Excel world as the go to tool for this job. So the only reason the IT bod will refuse to load it is because he/she knows nothing about VBA, not because the code is seen as a justifiable threat!
So if you need to add custom labels to a chart, get this add-in. Once you have it you’ll be able to apply it to all sorts of custom charts. It’s quick and easy to use.
If you find you are working a lot with long lists of data, you probably find after some time that distinguishing one line from another becomes increasingly difficult…a form of snow blindness if you like. You can format the list as a table and shows lines in alternating colours which might help but wouldn’t it be nice sometimes to be able to see only one line at a time.
Well…you can, using a FORM. You can go to the length of creating a form in VBA, and adding all kinds of buttons and clever functionality, but there is actually a built in FORM creator which will build a form when required on any data as long as it is in a list format i.e. column headings only, no row headings.
In 2003 or earlier versions of Excel, the FORM was available from the standard menu. Since 2007, it has been relegated to just another button in the CUSTOMIZE THE QUICK ACCESS TOOLBAR (2007+) or CUSTOMIZE THE RIBBON (2010+) options.
You will find the FORM button under COMMANDS NOT IN THE RIBBON or ALL COMMANDS.
Add this to either the QAT or RIBBON and you are ready to go!
So now you have access to the FORM, what does it do and how does it work?
Click anywhere in your data table and click the FORM button.
And without any further intervention by you, or working your way through multiple steps of a wizard, Excel will create a user FORM for you.
Using the column headings, there is a separate line for each heading and a text box displaying the information held in that column for a single record.
In the top right hand corner you will see the total number of records in your list. Using the scroll bar between the data and the buttons you can move from one row to another using the arrows at the top and bottom of the scroll bar or click and drag the scroll bar itself to move quickly through the records.
Not only does the FORM allow you view records, but you can also edit the information directly in the FORM which will update the spreadsheet. There is extra functionality available using the buttons on the right hand side.
New: To add new records to your list click here and start typing the information directly into the text box next to the heading. Sometimes, you will see some values/data in the form that does not appear in a white text box. This will be a calculated field or perhaps a look up function based on something you enter in another box. Because these cells in the worksheet are reliant on a formula you cannot enter information directly here.
One major downside to using the FORM is that if your data table has drop down lists to select values from, these are not transferred to the FORM, so you need to type values in manually.
One advantage of using a FORM to add new records, is that if your data is stored as a NAMED RANGE, any new records you add are automatically added to the NAMED RANGE so you don’t have to manually adjust the REFERS TO bit in the NAME MANAGER. If you are unsure about NAMED RANGES then refer to my blog http://wp.me/p2EAVc-99 to find out more. There is however, one caveat to this. A FORM will only add to an existing NAMED RANGE if it is called “database”. Call it anything else and the NAMED RANGE is fixed and it will not automatically expand to include new records.
Delete: Does exactly what you would imagine…it deletes the record from your table/list – permanently. Note that I said it deletes the record, not just the highlighted piece of information in the FORM.
Restore: If you make a change to anything in the FORM other than delete a record, this button becomes active and allows you restore the data back to its original value.
Criteria: I’ll jump to CRITERIA next because the FIND PREV and FIND NEXT buttons are generally used in conjunction with this button. When you click on CRITERIA it clears the FORM. This now becomes a search FORM. Type search criteria in one or more of the text boxes. You can enter whole values or you can use wildcards. Using FIND PREV and FIND NEXT then allows you to scroll through records that match your search criteria. When you click on CRITERIA you will see a CLEAR button that will clear the form of any criteria you have entered, and a FORM button that will return you the standard form.
So what wildcards can I use?
*(star/asterisk) denotes any number of characters.
H* will search for any value that begins with H followed by any number of characters.
*h will search for any value that ends in h.
? (question mark) denotes any single character
H?p will search for Hip, Hop, Hup, H3p etc.
You can then combine these together or use them in conjunction with < or >.
>h* searches for any names (for example) that begin with the letter h, I, j, k etc. and beyond.
<>h* will search for any values that don’t start with h.
So use any operators you are familiar with (<, >, <=, >=, <>) either on their own or with wildcards and you should be able to find just about anything in your list. Use the FIND PREV and FIND NEXT to scroll through the records that meet your criteria.
And finally Close which unsurprisingly closes the FORM.
So next time you are working with a list of data, give the FORM a go, it might be of use…might not.
In Excel 2013, amongst all the other amazing features that have been added since 2010 is a new add-in that can analyse your workbook for errors or inconsistencies.
The problem with this add-in, is that it has not been particularly well promoted and it is not in the standard add-in list that most of us go to turn on add-ins.
Firstly, note that this add-in is only available in the Professional Plus version of Excel. If you do have this version, then go to FILE, OPTIONS, ADD-INS. Go to the bottom of the screen and click on the drop down where you see EXCEL ADD-INS and click on COM ADD-INS and then click on GO.
Tick the box next to INQUIRE to activate it and click on OK.
You should now see a new tab on your Ribbon.
You will now access to a range of functions that can help you to examine your workbooks. If you don’t have this tool, you can still use some of the FORMULA AUDITING tools in the FORMULAS tab such as SHOW DEPENDENTS etc. but of course this is a more manual process and won’t give you the same level of detail that the new add-in can.
Make sure that the workbook you want to analyse has been saved. This will not work on an unsaved workbook. Click on the WORKBOOK ANALYSIS button and let it do its thing. After a few moments (this will vary depending on how big and complex your workbook is) you will see this window;
You will see an overall summary of the workbook contents and if you click on each element in the ITEMS window, you will see the details in the RESULTS window on the right hand side.
OK, you may not necessarily be interested in how many cells contain formulas etc. but there is some useful information in here such as external references, cells containing errors etc. As you click on each element, you will see exactly which cells on which sheet meet those criteria.
Click on EXCEL EXPORT to create a copy of the report.
As the name suggests, this will show you a diagrammatic view of how your workbook is connected to external data sources – other workbooks, databases etc.
Use the buttons at the bottom of the window to print the diagram, preview the diagram, or simply to refresh it.
If your workbook relationships are particularly complex you can rearrange the various workbooks and databases by clicking and dragging the icons in the window into an easier to read layout.
Similar to above, but this time looking at how the worksheets are connected rather than at workbook level.
Same buttons at the bottom of the window and the layout can be changed too just as before.
When you run this you will get a few options to select from in terms of searching for precedents and/or dependent cells. Pick whichever options suit your requirements best and click on OK.
And it does exactly what is says…looks at precedents/dependents of any selected cell (rather than every single cell in your workbook). As I mentioned earlier, this can be achieved using the FORMULA AUDITING tools but this is a more convenient way of seeing everything. As with all the other relationship diagrams, same options are available.
In Excel you have had the option to look at workbooks side by side for some time but again this was a very manual process scrolling through your workbooks carrying out visual checks to see any differences between workbook versions.
This option gives you an automatic check.
Click on COMPARE FILES and use the drop downs to select the correct workbooks to compare. Note that both workbooks must be open in order to run this. Click on COMPARE to run the comparison showing differences
This will produce a full screen report. Where there are variances the cells will be colour coded depending on the type of variance.
You can review each worksheet independently. Check the lower half of the screen to see the colour coding and the details about the differences.
There is also a small chart to show you how many of each variance type it has detected. Not the most essential bit of information but looks pretty in the exported report as a sort of dashboard.
COMPARE FILES also compares VBA code between workbooks if it is present, so quite a useful tool.
Clean Excess Cell Formatting
You well find that a workbook is slow to open, or may take a while to refresh every time you make a change. This could be for a variety of reasons, but one of those may well be excessive cell formatting. When you set up a worksheet it may be convenient to apply conditional formatting, or just simple formatting to entire rows or columns. This can make your workbook quite bloated. CLEAN EXCESS CELL FORMATTING looks at empty cells beyond the last cell that contains data and removes any formatting that may be applied.
When you run this you get the option to run on the active sheet or the entire workbook. Click on OK once you have made your choice. You may be prompted to save changes but otherwise no other windows to interact with.
This is used to store passwords for workbooks so that next time you open the workbook to run a comparison analysis you don’t have to enter the password. This probably has only limited appeal but is useful if you run any analysis or comparison when accessing the INQUIRE add-in via the START menu.
Click on START, go to the MICROSOFT OFFICE folder and open OFFICE 2013 TOOLS.
From there click on SPREADSHEET COMPARE 2013. This will open a new window. Use the COMPARE FILES button to select the two workbooks you want to run the comparison on. If passwords have been stored in the PASSWORD MANAGER then you won’t have to enter any passwords to open or access the file(s).
Click on OK once you have chosen your files and continue as above to review your files.
So next time you need to run a quick analysis of your workbook, or need to compare two files, (and you have Excel 2013 Professional Plus) remember to turn on your INQUIRE add-in and give it a run…much quicker than doing it all manually.
Within Excel there are literally hundreds of keyboard shortcuts, from accessing all the buttons on the Ribbon, saving, editing or selecting data. Having said that, unless you have an amazing memory and are a touch typist, it is highly unlikely you will ever remember or even need to use all of these shortcuts.
Here are a handful of shortcuts that I have found useful over the years. This is by no means a definitive list of “must know” shortcuts as everyone has different needs, but I think these are a useful selection for most users of Excel.
The conventions I use here are as follows;
Plus Symbol +
If the plus symbol is shown between two or more keys then the keys have to be pressed at the same time. E.g. Alt + = means press Alt and = at the same time to insert the AUTOSUM function in a cell.
Greater Than Symbol >
If this symbol is used between keys then this represents a sequence of keys. E.g. Alt > D > P means press Alt first, release the key and then press D. Again, release the key and then press P which in this example opens the Pivot table wizard.
Forward Slash Symbol /
If this symbol is used between keys then this represents “or”. E.g. Ctrl + →/←/↑/↓ means press Ctrl and right/left/up or down arrows.
Everything on the Ribbon can be accessed via a keyboard shortcut. To view the shortcuts press Alt.
A letter will appear for each tab. For example, if you want to access the FORMULAS tab you need to press M.
After pressing the letter that corresponds to the tab you want to get to, that will activate the tab and because you pressed Alt the letters you need for that tab are visible.
Again, press the relevant key to activate the function in the Ribbon. For example, if you wanted to activate the CALCULATION OPTIONS, you would press Alt > M > X and in this case this activates a further drop down set of options. Press the key you need when this is displayed – here we have the options of A, E or M.
I won’t go through every possible set of key combinations as each person will have their own favourite actions or buttons that they need to access on a frequent basis, but I will try to split the shortcuts into categories where possible…
|Alt > H > AC||Align text centre in a cell|
|Alt > H > AL||Align text left in a cell|
|Alt > H > AR||Align text right in a cell|
|Alt > H > AT||Align text top of a cell|
|Alt > H > AM||Align text middle of a cell|
|Alt > H > AB||Align text bottom of a cell|
At least these are fairly obvious in terms of which letters represent a position within a cell.
|Ctrl + 1 (one)||Open FORMAT cells dialog window|
|Ctrl + B||Make cell contents BOLD|
|Ctrl + I||Make cell contents ITALIC|
|Ctrl + U||UNDERLINE contents of a cell|
Simple ones but very useful, very often.
Selecting cells quickly and moving around a worksheet at speed are great time savers and well worth knowing.
|Ctrl + Home||Returns you to cell A1 from anywhere in a sheet|
|Ctrl + End||Goes to last cell containing test/data in your worksheet|
|Ctrl + Page Down||Move to next worksheet to the right|
|Ctrl + Page Up||Move to next worksheet to the left|
The next set of shortcuts allow you to move quickly to the end of a contiguous set of cells containing data. The cursor will stop as soon as it hits an empty row or column.
|Ctrl + →||Go to last cell to the right (with data) in the row|
|Ctrl + ←||Go to last cell to the left (with data) in the row|
|Ctrl + ↓||Go to last cell (with data) in the column|
|Ctrl + ↑||Go to first cell (with data) in the column|
If you hit a blank row or column and there is data beyond it, simply repeat the key combination until you reach the end of the data table. If you go too far in any direction (i.e. to the far right or bottom of the sheet), just use the opposite arrow with the Ctrl button.
To select cells between your current location and the end of a row/column or the entire table combine Ctrl with Shift and the arrows.
|Ctrl + Shift + →||Select all cells up to last cell to the right (with data) in the row|
|Ctrl + Shift + ←||Select all cells up to last cell to the left (with data) in the row|
|Ctrl + Shift + ↓||Select all cells up to last cell (with data) in the column|
|Ctrl + Shift + ↑||Select all cells up to first cell (with data) in the column|
|Ctrl + Shift + End||Select all cells to the last cell in the data sheet (with data)|
If you select too many rows or columns, leave the cells selected and press Shift + ←/→/↑/↓ to deselect one row/column at a time. The same can be used on a single highlighted row or column of cells.
|Ctrl + G||Opens the Go To dialog box|
|Alt + S||Opens the Go To Special dialog box from the Go To window|
|Ctrl + F||Opens the FIND dialog box|
|Ctrl + H||Opens the REPLACE dialog box|
Note you can only use Alt + S after opening the Go To dialog box.
Go To is useful to get to cells or named ranges quickly. The Go To Special dialog box gives you the ability to navigate to or highlight certain types of cells in your worksheets such as any cells with conditional formatting applied to them.
One thing you find yourself doing often is deleting or adding new rows and columns or wanting to hide some. So you end up right clicking and choosing from the options there or start searching for a button that can do it for you. I have found these to be quite useful little time savers;
|Shift + Spacebar||Selects the entire row of the current selected cell|
|Ctrl + Spacebar||Selects the entire column of the current selected cell|
|Ctrl + +||Inserts cell(s) or a new row/column|
|Ctrl + –||Deletes cell(s) or a row/column|
|Ctrl + 9||Hides the row(s) of the selected cell(s)|
|Ctrl + 0 (zero)||Hides the column(s) of the selected cell(s)|
|Ctrl + Shift + 9||Un-hides the row(s) of the selected cell(s)|
|Ctrl + Shift + 0 (zero)||Un-hides the column(s) of the selected cell(s)|
A few notes about this last set of shortcuts:
If you use Ctrl + +/- without selecting an entire row or column first then you get a dialog box asking you whether you want to insert or delete the selected cell(s) or the entire row or column.
Also note that if you are using a laptop without a full numerical keypad, you need to use Ctrl + Shift + +/-. If these dialog boxes appear then press the letter key that matches your choice e.g. R to delete or insert an entire row.
So if you do want to use these keyboard shortcuts you are likely to do the following;
Ctrl + Spacebar > Ctrl + + which will insert a new column to the left of the selected column.
Using Ctrl + 9/0 will hide the row or column of the selected cell and if you IMMEDIATELY press Ctrl + Shift + 9/0 it will unhide whatever you just hid. However, it is more likely that you will want to unhide something at a later stage, so you will need to select the rows/columns either side of the hidden ones before you use this shortcut.
The final section is just a selection of other keyboard shortcuts I use, including some very basic ones that you can use in any application;
|Ctrl + S||Saves the current document/workbook|
|F12||Opens the SaveAs dialog box|
|Ctrl + N||Creates a new document/workbook|
|Ctrl + C||Copies the selected cell(s) or object|
|Ctrl + V||Pastes the copied cells(s) or object to another location|
|Ctrl + X||Cuts the selected cell(s) to the clipboard|
|Ctrl + Z||Undo the last action (repeat for multiple undo actions)|
|Ctrl + Y||Redo the last action (repeat for multiple undo actions)|
|Ctrl + Shift + L||Applies AUTOFILTER to the selected cells/row|
|Ctrl + F1||Minimise/Maximise the Ribbon|
|F11||Creates a chat in a new chart sheet (uses default chart type)|
|Alt + F1||Creates an embedded chart in the same sheet (uses default chart type)|
|Shift + F2||Insert cell comment|
|F3||Opens the PASTE NAME dialog box to show a list of any named ranges you have in the current workbook|
|Ctrl + Shift + F3||Opens CREATE NAMES FROM SELECTION dialog box to create named ranges using existing column/row headings|
|F7||Runs spell check|
|Alt + F11||Opens the VISUAL BASIC EDITOR|
|Ctrl + `||Toggles between showing/hiding formulas in the worksheet (Note this is the combination on a UK QWERTY keyboard)|
|Shift + F11||Insert new worksheet to left of current worksheet|
|Alt + =||Insert the AUTOSUM function|
|F4||Toggle between relative and absolute cell references or repeat last action (limited use). See below for more information on the use of F4|
After entering a cell reference in a formula press F4 to add in the “dollar” characters. Continue pressing F4 to get the required level of absolute/partial referencing.
And there you have 56 keyboard shortcuts that hopefully you will find a use for. As I said at the beginning this is not a definitive list. You still have about another 200 to learn beyond this list, but personally, these are shortcuts that I use regularly and cover what I think are the day to day actions that you will use in just about every spreadsheet you open.