I, Eli, dev

#Errors #REF! Automation Broken Excel Excel Errors Forms Goals History Microsoft MS Forms O365 Office 365 Planning Sharepoint Troubleshooting

  • Creating a Microsoft Office Form (Forms)

    Microsoft Office Forms are extremely user friendly and are used in many settings for a wide variety of purposes. Microsoft offers a range of templates to get users started and these range from registration forms, to class quizzes to in depth surveys.

    Types of Microsoft Forms

    Despite looking very similar on the surface, there are two basic types available in Microsoft Forms:

    1. Forms
    2. Quizzes

    Forms are primarily used to gather information from the user with the response or outcome to be provided by outside sources; this may include Power Automate Flows and/or input from the receiving user/team.

    Quizzes can include answers and provide scores and feedback to the user after completion. For example, the quiz may provide the correct answer and a reason why if a user has responded to a mathematical question incorrectly.

    It’s important to consider which type of form you need prior to commencing the development of your Form.

    Navigate to Microsoft Forms.

    In the top left-hand corner of any Microsoft Office 365 (O365) App you’ll see the vertex menu of nine dots. This menu will give you a table of the most popular Microsoft O365 apps.

    In many instances, as with the above image, you’ll see the app that you want within this display. If not, you also have the option to search for it (shown below).

    Click on the Forms icon to navigate to Microsoft Forms.

    Decide what sort of Form you need.

    At the top of the screen, you’ll have three options: create a new form, a new quiz, or to import a document to use as the basis of your form.

    As described above, we need to identify the purpose of our form before we choose what type of form we need.

    Are we quizzing our users and want the form to provide an outcome rather than having to score them all manually and write an email in response? Are we seeking user registrations or requesting user feedback about something specific?

    Regardless of which form type you create you are presented with the same quick start options (shown below)

    You’ll also have the option of choosing a Template. The template button sits off to the left-hand side and provides you with a list of quizzes or forms depending on your first choice.

    The form templates are more wide ranging and are broken into categories

    Identify your questions.

    It’s important to start with your questions in mind, as it can be annoying to have to go back and shuffle them or change the question type later. It’s certainly not that you can’t do this it’s more that you really don’t want to.

    It’s important to consider both what information you need to gather from the user and the users experience when creating forms.

    This might look like considering if a question needs to be compulsory which would force all users to respond to the question, but it may also involve providing drop-down or radio box options for the user to make responding quicker and increase clarity.

    It’s also worth carefully considering exactly how many questions you need. It can be great to have lots of information but it’s never as much fun to provide it!

    Consider your user.

    Considering your user is an important step when designing any solution big or small. Microsoft Forms includes a couple of features to break up or control the flow of your form/quiz and it’s important to consider how the user will interact with your end product.

    Are they tech savvy and will intuit the branching and sections? Do users need to see certain questions to understand the context of others?

    Choose a template or start entering your questions.

    Different types of questions require different input. Obviously, in all instances your question will require a question and in some instances, you’ll have the option to provide suggested answers for users.

    • Choice
      • Drop down box
      • Radio box
      • Multiple Answers
      • Required
      • Other Option
    • Text
      • Long answer
      • Required
      • Restrictions (input restrictions on type and value)
    • Rating
      • Variable value response from 2-10
      • Symbol selection (e.g. Stars)
      • Label for range beginning and end
      • Required
    • Date
      • Required
    • Ranking
      • Shuffle options
      • Required
    • Likert
      • Required
    • Upload File
      • Number of file limit
      • File size limit
      • File type restrictions
      • Required
    • Net Promoter Score
      • Required

    Tips!
    Questions can be duplicated!

    Sometimes it’s efficient to duplicate question structures to avoid repeating steps. This might be a situation where you’re asking different questions with the same answers (eg. Slightly Agree, Agree, etc).

    Questions can be reordered!

    If you have a form design that has the right elements but in the wrong place you can reorder them by up or down arrowing them to where they need to be.

    Answers can be pasted!

    If you have an existing bank or list of questions/answers that you’re building your form from you can past the responses straight into your Microsoft Forms options.

    Make sure that your answers are all on new lines, and that Option 1 is either cleared or highlighted. Simply paste all of your selected responses!

  • Form to Spreadsheet (Excel, MS Forms)

    Any Microsoft Form that you create automatically records the responses to an Excel Spreadsheet. The location of this spreadsheet is somewhat dependent on the location of the form itself.

    Forms can be created as a personal form or a group form – this choice changes a number of things about your form and its responses.

    Personal Forms:

    Personal forms are stored with your own O365 user account. Responses are automatically saved to an Excel spreadsheet within your OneDrive and the form and associated data are no longer available if your account gets deactivated.

    Personal forms can be shared with a Collaborate link allowing others to edit your form. The Collaborate link can provide access to:

    • Anyone with an Office 365 work or school account
    • People within your organisation
    • Specific people within your organisation

    If your account is deactivated these links will cease to work.

    Group Forms:

    Group Forms allow users within the group to access and edit the form and associated responses.

    Responses are automatically saved to an Excel spreadsheet within your OneDrive and the form and data will remain if your account is deactivated.

    Group Forms can also be shared with Collaborators.

    How do I know which it is?

    When you navigate to Forms you can see the below menu:

    Under Recent all forms are grouped together regardless of owner or location.

    Each of the forms you can edit will display an image (if one has been selected), the title of the form, the owner of the form, and the number of responses.

    Any form you have responded to will display an image (if one has been selected), the title of the form, whether the form has been filled or not, and the date and time of completion.

    Who can respond to Forms?

    When you are ready to request responses, you will be asked about who is responding to your Form. You can specify the following:

    • Anyone can respond
    • Only people in your organisation can respond
      • Record name
      • One response per person
    • Specific people in your organisation can response

    These options allow you to limit your form responses as needed. In some instances, the information you provide, or request may be protected by privacy laws or have broader implications if seen by a wider audience. Always keep this in mind when creating links!

    Find more about creating your own Microsoft Forms here

    What else?

    Forms can also be used to feed information to other Microsoft products such as Lists and Outlook depending on your purpose.

    W

    Find more about creating your own Microsoft Forms here

  • Microsoft O365?

    For many years Microsoft Word, Excel and Outlook have been synonymous with business to the extent that the question of which office suite to use often isn’t even asked. As online services change and the call for new products with end user customisation grows Microsoft’s product offerings have also grown.

    From first release in floppy disk format in 1990 which featured only Word, Excel, and PowerPoint to the swathe of online user/creator features shown in the screengrabs below, Microsofts growing product portfolio reflects the rapidly changing world of business.

    Current as at 2024/01/06

    Forms, Lists, Excel, Bookings, etc and other less well known products like Clipchamp, Insights, Delve

    Microsoft O365 Office 365 Apps Screenshot
    Microsoft O365 Office 365 Apps Screenshot 2

    Outlook, OneDrive, PowerPoint, OneNote, Planner, etc

    Word, Sharepoint, Teams, Visio, etc

    Microsoft O365 Office 365 Apps Screenshot 3

  • Something isn’t working! What now? (Excel)

    So, you’ve found an error and now you need to figure out what the issue is and hopefully correct it. There are plenty of pages that address specific error types; if you’re looking for one of those, look here (TBA). This article is more about the process of troubleshooting than the trouble itself.

    Generally, with Excel you’ll have one of two types of problems:

    a) The formula isn’t working

    b) The formula is “working”

    In both options, the process for troubleshooting is quite straightforward but often full of steps that require careful reading and/or testing of your previous work.

    Option ‘a’ generally involves an error value (eg. #VALUE!, #N/A, #NAME?, etc) and no actual value returned. Option ‘b’ is often when things appear to be working but the output isn’t what you expected it to be.

    1. Is my problem type ‘a’ or ‘b’?
      • Is my output an error?
      • Does my output make sense?

    This is really the first question we need to ask because it forms the basis of our understanding when searching for the issue.

    After answering that first one, let’s continue with questions you’d ask for both, and how you might answer them. Now, these might seem straight forward and like common sense – that’s because they are. Occam’s razor and anecdotal evidence all support looking for the most obvious and simple answers first.

    1. Is my problem type ‘a’ or ‘b’? (as above)
    2. Does my formula have any obvious errors?
      • Are any of my formula arguments1 appearing as an # error? (Relates to Q3)
    3. Can Excel answer the question I’m asking?
      • Does the sheet I’m referencing exist? (Q2)
      • Does the table I’m referencing exist? (Q2)
      • Does the cell I’m referencing have data?
      • Is that data the right data type (most relevant when comparing or searching for values)
      • Does the cell I’m referencing have the data I expect?

    These are the most obvious answers that are often the cause of simple errors, particularly in shared workbooks, lets walk through them.

    Q2. Does my formula have any obvious errors?

    Consider the formula below.

    =VLOOKUP(D2, A1:B5, 2, FALSE)

    That formula contains no errors. An error might look like the snippet below;

    =VLOOKUP(#REF!, A1:B5, 2, FALSE)

    This indicates that the item you were referring to has been renamed, moved, or deleted.

    Fixing this is often as simple as finding what argument should be there and either adding a new argument that will correct the error or returning the missing data to where it should be. In the above example, we need to find what value this VLOOKUP is meant to be finding in the range defined in the function.

    Often when you delete a cell or modify a column/row, Excel will be smart enough to figure some of it out.

    Looking at the below example:

    Fig 1. shows the working formula, i.e. there are no errors in the formula, and we have the result that we want and expect.

    =VLOOKUP(D2, A1:B5, 2, FALSE)
    Fig 1

    Fig 2. shows it ‘working’, i.e. there are no errors in the formula, and we have exactly the result that we’ve asked for but NOT the result we were expecting.

    By ‘working’ I mean that there are no errors in the formula and it’s attempting to do exactly what you’re asking it to but that there can be no result. Read on to see why that’s the case.

    =VLOOKUP(C2, A1:A5, 2, FALSE)
    Fig 2

    Q2 Answer: No, your formula contains no obvious errors.

    Q3. Can Excel answer the question I’m asking?

    Does the sheet I’m referencing exist? Yes, I only have one sheet.
    Does the table I’m referencing exist? Yes, my table exists at A1:A5
    Does the cell I’m referencing have data? Yes.
    C2 = 3
    A1:A5 = numbers
    column index 2 = …..

    And there’s our problem.

    You’ll notice that in Fig 2, there are no errors in the formula as the search value reference has updated from D2 to C2 meaning that Excel still know where to look for the search value. It has also updated the search range to be A1:A5 so it’s searching in the remaining column. It hasn’t, however, been able to modify the column index number, which is still 2.

    The formula is effectively trying to return a value that doesn’t exist because the count of columns is 1 but our column index is 2. Due to this error the function returns a #REF! error.

    Great news – we’ve found our error! Let’s try another one.

    This is an example of type b. The formula is definitely ‘working’ but it isn’t giving us the result that we want.

    You can see that the function is summing the numbers in column a (or Count). This can also be referenced like =SUM(A2:A9), shown below.

    Q2. Does my formula have an obvious error? No, my formula has no errors in it. It has the right number of arguments and no #REF! errors.

    Q3. Can Excel answer the question I’m asking?

    Does the sheet I’m referencing exist? Yes, I only have one sheet.
    Does the table I’m referencing exist? Yes, my table exists at A1:B10
    Does the cell I’m referencing have data? Yes.
    Count or A1:A5 = number values but one of them appears different….

    And there’s our problem.

    Excel likes to help us out a little with these ones and you can see the little green tag to the left top corner of the cell and the yellow error symbol on the right. Clicking on that displays the pop-out menu above and identifies the potential issue: Number Stored as Text. Because the number is stored as a text value, it cannot be processed as a numerical value by the sum function – or any other function for that matter.

    Clicking Convert to Number in this menu will change the data type to a number and your formula will return the correct value.

    1. An argument is a value, object or variable passed into a function. In most cases the value is being provided to the function to give the function what it needs to complete is purpose. That purpose might be to perform a calculation and return the result or to complete a set task and provide the outcome. ↩︎

  • What’s in a goal?

    There’s a problem at work and it keeps happening. Joe keeps filling out the customer follow-up form and while he writes out their life story, he doesn’t capture all the useful information. So you made him a form, but people forget to refill the stack when they use the last one so there are lots of random bits of paper in the filing cabinet. The filing system, and I use the word system loosely, has been growing for years. You’ve taken to scheduling time to clear out the filing system and always find other random documents stored there. People regularly have to refer to the person who lodged the follow-up item because the forms aren’t up to date or can’t be found. Sounds like a nightmare factory for customer complaints, right?

    While Joe is not real, this loosely outlines a real life problem that can occur in any place where customers exist, and the existing CRMs (Customer Relationship Management systems) doesn’t account for it either through feature block or multiple CRMs being used to address different customer needs.

    Why do I need a goal? Surely, I just need to automate out all that stuff. The point of a goal is to identify the specific objectives of your automation, to make them measurable, and to make them achievable.

    Of the above, we have a few distinct problems.

    • Form completion (printing forms, supplying the correct information)
    • Document management (printing forms, storing forms, finding forms)
    • Customer experience (lost information, failure to follow up, multiple enquiries)
    • Information management (keeping more information than needed, storing without security measures, information not routinely destroyed when not needed)

    Clearly, we need to fix the above list but when you break it into defined issues it’s not something that we can simply automate out by blindly starting to create flows.

    Our initial goals may be as simple as those outlined blow. Once these are defined, we can start to create objectives that can be achieved as we continue through this process. Objectives often resemble a broad to-do list and give our project structure and act as progress markers.

    GoalsObjectives
    Eliminate hard copy paperwork associated with followup itemsCreate follow-up form in Microsoft Forms
    Reduce customer complaints associated with followup processInclude followup status check in daily standup.
    Achieve fewer than three followup associated complaints per month over a three month period.

    Now we’re not delving too far into the art of writing project goals here because our purpose at this moment is to look at why the goals themselves are important.

    Achieving the above goals will see team members fill out an online form for customers which will be stored in one Excel spreadsheet and introducing the follow-up status check daily will reduce the number of follow-ups falling through the cracks therefore reducing complaints. Great! Problem solved, right?

    Maybe. This is definitely a step that will improve the identified issue, but it does introduce others.

    • Where is the Excel spreadsheet stored?
    • Where did my data go?
    • Why is management on my case about my follow-ups every day? I never forget them
    • Do I really have to hold my team members’ hands? It’s their job to remember follow-up

    “But I just want to automate one form!”, I hear you say.

    Upon review, it’s clear that we’ve just shifted the impact of this particular problem to another place and THAT is the moral of this story. Maybe that’s an outcome that you’re happy with because that part of the business/team has capacity to absorb that issue or maybe it doesn’t. Either way, the point of identifying goals and treating process adjustments like a small project is to protect ourselves and our teams from committing to processes that further undermine the strength, efficiency and/or wellbeing of our teams.

  • To automate or not to automate; is that the question?

    A lot has been made of automation in recent years, much of the public understanding of automation focuses on replacing human workers with robotic ones. Depending on your perspective, this either moves us closer to the utopia of people spending more time on leisure and less time at work, or a world where corporate bottom lines have drastically increased unemployment and fiscal strife abounds.

    Is it actually that black and white? Personally, I don’t think so.

    In my mind, the outcome reflects the goal. If your business is seeking to reduce costs by reducing the number of team members then you’re going to pursue automation solutions that achieve that goal. A great example of this is self-serve checkouts. One person to manage five to twenty checkouts is a lot cheaper than paying one person per checkout. Obviously, shrinkage needs to be taken into account and some consideration for customer sentiment but on the balance of things its a net financial win.

    Does this confirm that automation is out to get us and put us all out of jobs? No, it confirms that businesses pursuing cost reduction and increased efficiencies will sometimes seek to reduce staffing costs.

    When you set your air conditioner to a specific temperature it turns off once it reaches that level, saving you power. When you sign up for a new product online and it sends you a welcome email with all your details, it makes sure that you won’t forget. When your calendar sends you a pop-up reminder about that all important birthday or appointment, it’s protecting you from errors or potential embarrassment.

    Automation features in our everyday lives, much of it improves our quality of living by reducing the number of things we need to actively monitor in our busy lives.

    How does this play into business efficiencies if not to reduce staffing costs? Often these automations can be leveraged to shift the focus or reduce the load of regular activities. Creating processes that leverage automation to reduce the number of steps, the number of errors, or the number of touch points can achieve a variety of things.

    So the question really is; what is our goal?

    If your goal is to reduce customer complaints, then your path there might be conforming and automating some of the customer followup process so reporting on timeframes is available, reminders to staff are automated, visibility is there for managers, and customers know that you have their information at your fingertips.

    If your goal is to reduce the number of steps in a task, then the path there may be to automate the creation of documents and outgoing communications that are relevant to the task based on existing data.

    So how do I define my goal? I’m so glad you asked.