Excel TIP (Add Data Validation with a Drop-Down List)

 

It is important to be mindful of how you are entering repetitive data in excel. There is nothing worse than trying to validate and modify a column that has the same value written multiple ways! Look at this contact information, where the same state is recorded differently.

texas2

If you needed to filter this list on state you’d have to account for all representations of Texas!

Adding drop down validation is a great way to ensure values will be recorded consistently – and it is super easy to do in excel!

Let’s say Maggie’s Pet Supply sales team is calling a list of pet stores. One of the items they need to capture is the product they are interested in ordering

4

Rather than rely on the sales team to capture the correct product name, we can store the list of products as a validation list that can be selected by a drop-down.

  • To do this, add a second sheet to store the list of possible products

3

  • Next, highlight the column you want to be able to select the drop-down items
  • Under the Home tab, select ‘Data Validation’

5

  • For Validation criteria, allow ‘List’

6

  • Click in the Source box and then Click on the Product sheet
  • Highlight the values in the list and select OK

7

  • Navigate back to the Call Sheet and you can now select items for your drop down!

8

 

 

Advertisements

The Curiosity Habit (How To Discover Interesting)

chuttersnap-552548-unsplash

The world we live in is an incredibly interesting place. I was fortunate enough to visit Italy a few years ago and I remember the satisfying experience of being able to walk down any alley in Rome and finding something new to explore. I felt grateful to take part in the Human journey.

Our daily lives can feel a little less interesting and a bit routine:

  • Wake up
  • Shower
  • Eat
  • Work
  • Socialize
  • Sleep

Day-in and day-out. If we could enter a state of curiosity at any time we might begin notice the interesting things in the mundane. For example:

  • just an hour before we wake up, our body temperature begins to rise, our brains are flooded with chemicals like norepinephrine, acetylcholine and serotonin that wake us up and keep us alert. It’s is an incredibly complex process.
  • Have you ever stopped and considered that when you turn on the shower, miles and miles of plumbing must work together to provide you with water? Every soap, conditioner, and skin cleanser is a different product with ingredients sourced from different places and marketed by different companies (unless you use a 3-in-one).
  • What we eat provides nutrients and the fuel necessary to carry us through the day.
  • Where we work, most likely, is a complex network of roles and responsibilities working together to accomplish a single goal where each department has its own set of skills, its own systems and processes that have been created through years of trial and error. If you work for a successful company, the odds of it ever being successful were always stacked against it!
  • Each of our family members and friends that we socialize with have separate lives, thoughts, interests and desires that are heavily influenced by the way we interact with them.
  • There are pages and pages written about the wonderful and rejuvenating process of sleep, and where our minds journey during sleep.

What if we could put on our ‘Curious Hats’ on all the time? What if every day felt a bit like walking down an ancient cobbled alley in Rome? I’ve recently began challenging myself to be more curious more often. I look at curiosity as an important habit that has the ability to fuel personal development and happiness . If you’re curious, I’d like to share some advice from modern psychology, business, and personal experience about how to  engage in a more curious life.

Read Voraciously

Voracious, Adjective – wanting or devouring great quantities of food

I love this word – Just saying it is satisfying. Vor-a-cious. Pronouncing it makes you bear your teeth. The curious read everything: Signs, posters, shampoo bottles, books on chemistry, history, pop-culture, sci-fi, journals, magazines – anything that may pique their interest. For many people their is a gap between what interests them, and what they learn – the curious constantly try to close this gap with reading. Psychologist and sociologist B.F Skinner recommended that “When you run into something interesting, drop everything else and study it.” Don’t feel intimidated by the amounts of knowledge or literature available for a certain subject – starting anywhere is good enough. Skinner also mentions that “The feeling of being interested can act as a kind of neurological signal, directing us to fruitful areas of inquiry.” Starting the process of reading lends itself to interest in reading. I’ve sometimes come across a book I’ve been interested in and thought someday, I would like to read that. When that happens, just open up and start reading – even for five minutes. It may lead you down a completely new path.

Don’t Worry About Messing Up

No one is as critical of you as you are. One of the biggest road blocks to curiosity is the fear of getting something wrong. Curiosity means asking questions and making some assumption. More often than not, your assumptions will be wrong. Don’t worry about it! It is not so much about getting the right answer as it is moving towards answers. In a scientific journal titled The Psychology of Neuroscience and Curiosity, the author notes that curiosity has a tendency to build upon itself by ‘Information Tradeoffs’ where the subject utilizes “probabilistic elements” to  “explore other possibilities, leading them to better overall choices” (Kidd). In short, curiosity builds on itself.

Be Mindful

Take action in your actions. The next time you are performing a task try to think critically and purposefully about what you are doing. Is there a better way to do it? Why is it done this way? If you had to teach someone else to do what you are doing, could you? As you start to pay more attention to your own actions, you’ll also be able to think critically and empathetically about the actions of others. When you open your mind to being mindful curiosity will come naturally.

Sources:

The Psychology and Neuroscience of Curiosity
Kidd, Celeste et al.
Neuron , Volume 88 , Issue 3 , 449 – 460

Wai, Jonathan. Seven Ways to Be More Curious.Psychology Today. https://www.psychologytoday.com/us/blog/finding-the-next-einstein/201407/seven-ways-be-more-curious

https://www.tuck.com/waking-up/

To Forget or Not to Forget: An Examination of the GDPR ‘Right to be Forgotten’

dhruv-deshmukh-266273-unsplash

 

The architects of GDPR stress their intention of the regulation: to increase both individual privacy and innovation. If innovation includes finding ways to be exempt from GDPR, they would be right.

In a growing consumer marketplace that heavily relies on massive amounts of data, it only makes sense that the most realistic approach to compliance will be to find ways to fit through its ‘loopholes’. At the thousand-mile level the regulation is innocuous enough: individuals must be aware of how their data is being used by giving consent and if they choose to, they can request that their personal data be completely removed from further ‘processing’.

A closer look of the regulation lends itself to a few scary sections – especially for data driven industries – which are followed by rather vague exemptions.

To Forget

Business and other organization are increasingly finding ‘secondary’ uses of data – That is to say, data that collected for one purpose, later ends up fulfilling the need of another purpose. An example would be if an online retail company collected address information for shipping purposes, and later ran models on all address data to determine where frequent buyers reside. Under the GDPR’s ‘Right to be forgotten’ – there are some obstacles to this:

  1. “The data subject shall have the right to obtain from the controller the erasure of personal data concerning him or her without undue delay and the controller shall have the obligation to erase personal data without undue delay where one of the following grounds applies:
  2. the personal data are no longer necessary in relation to the purposes for which they were collected or otherwise processed” (GDPR, Article 17.1)

We may be able to understand quite easily, the concept of consent. What is unclear, however, are the events where obligation becomes the condition for erasure. As mentioned previously in the case of the online retail company, would the data have an obligation to be erased? Here we have a condition where the data is no longer is needed for the original purpose but remains very valuable.

Of course, the company may foresee the need for the data and include something in the written consent along the lines of ‘this information will be used for shipping purposes and general marketing reasons’ but this could be a violation of the law’s definition of consent “‘consent’ of the data subject means any freely given, specific, informed and unambiguous “ (GDPR, Article 4.11).

A more concrete example may be the tremendous value of Google search queries. Google Trends, is a way to visually see in infographics how people are using the search engine – what they are searching, what news they are looking for, and what is interesting to them. In 2006, AOL released over 20 Million search queries. Each user remained ‘anonymous’ by substituting their name for a unique ID.

An article written by The New York Times reported that the identities of some users were able to be discerned based on search history, leading to AOL removing the information (Barbaro, Zeller 2006). This is a case of seemingly general data revealing a personal identity. Even though Google Trends represents a mind numbingly large amount of data being aggregated, would it not be possible to discern an identity from it? Furthermore, can the aggregation of each trend ever be considered the “purpose(s) for which they were collected or otherwise processed” (GDPR, Article 17.1)?

 

Or Not To Forget?

The architects of the GDPR accounted for reasons to continue processing data past its original purpose and allowed for a variety of exceptions. One of such exceptions are in cases of law compliance, essentially leaving all government agencies exempt (as if this was any surprise). Other conditions relate to the public value of the data stated in Article 89 which allows exceptions for “Safeguards and derogations related to processing for archiving purposes in the public interest, scientific or historical purposes or research purposes”.

This would also seem to exempt government sponsored research such as anthropological and other population based research as well as medical and scientific research. For a great read about the research exceptions, check out this article from the International Association of Privacy Professionals: https://iapp.org/news/a/how-gdpr-changes-the-rules-for-research. Thus the main innovation that would come from the law may be for business to find a way to fall into an exception category by expressing a reasonable need for data retention after its initial use.

Consider for example, another use of search query aggregation where Google claimed that they could use the information to locate the spread of the flu virus by analyzing user’s symptom searches. An article from the Guardian, notes that “They also found that the Google statistics, which can be gathered daily, were up to two weeks ahead of the federal government’s data, which took time to assemble because it came from so many doctors” (Pilkington, Google Predicts use of Flue using huge search data). Under the GDPR regulation, this specific use case may qualify as being exempt. However, it is highly unlikely that Google could have foreseen the exact use of its query data. Had GDPR come a few years earlier – this incredibly valuable analysis may have never come to light.

Although regulations and their interpretation have a way of veering in different directions from each other, it will be interesting to see how GDPR will be enforced, what exceptions or exemptions will be made, and how companies, especially ones that rely heavily on large amounts of data will adapt.

 

 

 

 

Sources:

Barbaro, Michael and Tom Zeller Jr. “A Face Is Exposed for AOL Searcher No. 4417749” The New York Times 9th  August 2006. https://www.nytimes.com/2006/08/09/technology/09aol.html

Maldoff, Gabe. “How GDPR changes the rules for research” iapp.org. 2018. https://iapp.org/news/a/how-gdpr-changes-the-rules-for-research/

General Data Protection Regulation. https://www.eugdpr.org/

Photo by Dhruv Deshmukh on Unsplash

In Defense of the Salesperson

pexels-photo-327540.jpeg

When my wife and I walk into an environment where we know we will be met by commission motivated Salespeople, we typically reaffirm ourselves:

‘Remember, we are just here to look’

‘Don’t ask any questions’

‘Don’t make eye contact’

Like most people, I blamed the salesperson for feeling pressured or anxious. It seems to be the general opinion that salespeople can be overbearing, aggressive and even misleading. This conception has lead company’s like Carmax to adopt a a no-haggling policy, claiming not to play pricing games with the consumer or Living Spaces, where employees reassure their customer’s that they do NOT work for commission.

Why are we uncomfortable around the commission motivated salesperson? Of course, many of us have had an obnoxious encounter- but I think the reason is more complex than they are overbearing, or aggressive. I’ll share a few of my opinions:

People are afraid to say ‘no’

I have found myself leading a salesperson on just because I did not want to reject them. I’ll give objections such as ‘I’m looking for something further down the line’ or ‘these are too expensive’. To a salesperson, these are just clues that help them point out other suggestions. There is nothing wrong with being clear about your intention – most likely, they will understand and want to educate you anyways. Take advantage of their knowledge – even if you are not looking to buy then, salespeople know the value of building the relationship.

Guilt Over Indulgence 

Self indulgence may be a rare thing for certain people. I know that most times I’m window shopping at an outlet with my wife, we know we WANT something, but don’t NEED it. I think this makes us feel guilt about our purchase. Our fear of the salesperson is more a guilt about indulgence.

The Perception of Money Motivation

The common perception is that salespeople only care about money. Well, what do you work for? It’s not unusual to work for money. Even for the associate who is not working on commission, their prime motivation for engaging in conversation with you is a paycheck at the end of the week. It may be possible that the commission based associate has the purest of intentions because their motivation is transparent.

What do you think? Do you find yourself anxious or uncomfortable in a commissioned sales environment? If so, why do you think that is?

 

 

The BSA’s of The Business Systems Analyst

rawpixel-com-565456-unsplash

My job title can sound pretty vague, ‘Business Systems Analyst’ sounds like a few important words strung together. To those outside of the software development world, and perhaps even to those inside, it’s anyone’s guess to what that means. Whenever I’m asked ‘what I do’ , I typically respond one of three ways:

“I am in Software Development”

“I am in IT”

“I wear many hats…”

All are true, but none of them – even when combined, paint a complete picture.  I did a bit of research to find out how others would describe the role of the Business Systems Analyst. I found some pretty insightful things, here is a video from the Technology Profession YouTube channel that does a great job highlighting the job responsibilities as well as the main skills needed. For me, the easiest way to define the role is to break it up into three fundamental components that are already included in the title: Business Systems Analyst.

is for Business

The first, and most important of the three. This is who the Business Systems Analyst serves. The BSA must know the Business, it’s mission, and it’s goals, including an understanding of how the business generates revenue, how the business positions themselves within their market and the overall growth strategy.

I often find myself going down a rabbit hole of tasks and emails before bringing myself to and thinking how does what I am doing effect the goals or mission of my organization?

is for Systems

This is the technological arm of the BSA. Each business relies on systems to successfully deliver their product or service to the customer. A BSA must understand the main systems that the business unit utilizes including the most common use cases, their limitations, and gaps.

The BSA is also responsible for knowing the data each system relies on, how that data interacts with other systems, and the importance of the data.

Perhaps equally as important as knowing current systems, the BSA will need to know how to develop and manage new systems. To do this, he or she should understand the software development life cycle and be relatively up to date with current technologies and trends in their space.

is for Analyst

This is the creative problem solving arm of the BSA. Ultimately, a BSA’s key value proposition is in their ability to implement and come up with new solutions.

Understanding the current state is a fundamental part of the role and the other part is discovering how to improve. The BSA continually challenges the way things are done and looks for ways to optimize.

On the road to optimization, the BSA collaborates with the business, developers, and stakeholders to create a wonderful and hopefully adopted solution.

The Business Systems Analyst is a highly enjoyable and rewarding role. The BSA gets to collaborate with many different business units, take part in the creativity of the development team and contribute to the goals of the business.

If you’re a BSA or work with BSA’s what are some other things you think are important to the role?

Combating ‘they ought to’ mentality

It can feel like a battle to maintain a positive outlook at work. Between organizational shifts, office politics, and general fatigue, staying motivated can be difficult. Most days  I am able to stay productive and engaged, but nothing can hijack momentum like the wrong mentality. By ‘mentality’, I am referring to a state of mind that may influence how work is accomplished or viewed. There are obvious negative and positive mentalities, a ‘can-do’ attitude is an example of a positive mentality while ‘things never go my way.. ‘ is a negative mentality.

I encountered one particularly insidious mentality while I was refilling my coffee in the break room that I began to coin as ‘They Ought To’ mentality. Many people were huddled in a circle discussing the changes to company benefits. I could hear that one person was leading the discussion, while the others were listening and taking turns to also express their disapproval, ‘They Ought to provide additional vacation’…..’at Laura’s company they give every man 6 weeks of paid paternity leave!’…..’If you’re lucky you’ll get a 3% raise!’, who can live with that?’

Now, I want to clarify I am not stating that paternity leave, maternity leave, and other employee benefits should not be advocated for. These are all great benefits that I believe in many places can strengthen the organization that provides them.  The problem with this mentality is that it is disguised as a legitimate concern but offers no alternative solution. It is simply just stating a problem and  provides no constructive approach to resolve it, since no one can argue on behalf of the organization and no one can identify ‘they’. Regardless of what service ‘they ought to’ provide is, in most cases it has already been identified as not being available. We can argue who should provide it, but the fact remains that it is not being provided and that is likely not to change any time soon. How would the internal or external conversation go from this perspective?

Approaching the same issue in another way might look like this:

The organization or institution does not provide (x).

Is (x) important to me or my family?

Am I willing to look for other opportunities so that my family and I can be provided with (x)?

What are other ways I can get (x) without having to rely on my organization?

If someone is not interested in following through the reasoning above, then would (x) continue to be a real need for them?

 

 

Bridge Above The Water: The Hard Worker vs The Valuable Worker

I remember a monotonous and time consuming project I was working on involving a series of data manipulations and entries. My coworker Rich, our systems analyst, implored me to let him develop a way to automate the process. I told him that by the time he was done I wouldn’t need it anymore. I joked with him by saying ‘you build a bridge, and I’ll drain the river dry – either way we both get to the other side’. He chuckled and returned to work. A few moments later he turned his chair and asked, ‘What if it rains again?’

His response was brilliant. It most likely would rain again, since, well…that’s how nature works. A bridge would not be effected by the rain because a bridge was a solution and not a state – like an empty or flowing river. Regardless of how much water was in the river the bridge would always be an option. If someone was not strong enough, or did not have the time or resources to drain the river, they could use the bridge. The bridge would provide value to the entire community. When you take a step back, the bridge was definitely the better of the two solutions. It’s smarter. Sure enough, a few weeks later I was wishing I had built a bridge!

At the time I was being the ‘Hard Worker’, while my coworker was being the ‘Valuable Worker’. I would not stop until my work was done, which made me a hard worker. However, I could’ve spent my time on something more valuable.

I decided to compile  ‘Hard Worker’ habits and traits compared to a ‘Valuable Worker’ traits. Please let me know what other distinctions you can think of in the comment section!

A Hard Worker? A Valuable Worker?
spends most of their time working spends most of their time learning
knows a lot documents a lot
sticks to the process Improve the process
knows how to do their job understands why they do their job
is skeptical of change welcomes change
is aware of the deadline is aware of the mission
checks the to-do list from top to bottom prioritizes tasks
holds on to responsibility delegates responsibility
is concerned with their role only knows how other roles work together
is in competition with others collaborates with others