Kiza Solutions

New series starting today!? What if you are more of an analyst, or software developer, less so the trailblazing #champion or #intrapreneur??What should you do to help drive innovation???? In advance of our sessions at #ClarisEngage at Apple's offices in Austin TX, here are a few ideas for how you can charge up your innovation leadership game: # 1. Find a #Champion. That’s right!?If you know that you are not naturally aligned with #intrapreneurship, simply start by finding an Intrapreneur to follow. You’ve heard it said that certain teams struggle with a lack of leadership.?We’ve certainly also all been on teams that lack #followership! (And If no one is following, is anyone actually leading? ??) ——————— Here are some concrete steps to take before you kickoff your next big idea: ? Identify an internal champion willing to trail-blaze innovation with you ??Generate ideas, prototype solutions before you get approval and budget ??Determine the level of best fit with corporate mission, vision ??Assess other initiatives already in progress and how that might compete ??Identify an executive sponsor to get behind it ??Build your team of supporters Don’t have your tickets yet!?Still time to join in Austin March 25th:?https://lnkd.in/gDDeUTG9. Part 2 coming soon!?Thank you.

Here’s our full list of the "Top 10 Dysfunctions of #Spreadsheets”, and a few new #takeaways: 1. Versioning Confusion 2. Tabs Sprawl 3. Broken Formulas 4. Color Formatting Ain’t Data 5. Funky Data Types 6. Data Entry With No Form 7. Cell Comments, the Silent Killer 8. Too Many Rows 9. No Pickers, Free-Form 10. Security Blunders Takeaways:? - Good, Better, Best — think of the roadmap, the practical steps you can take to improve your current state and plan for the best. ?? - Make big plans — assume that you will have smashing success and that your small spreadsheet prototype will be the makings of a robust system. ?? - Trail-blaze on! —?don’t let our warnings and gotchas slow you down.?You got this.?#Onward!????

#Security!?Of course, security?is # 10 on our list of the?"Top 10 Dysfunctions of #Spreadsheets”.?? A client of ours once said “manage risk and all else takes care of itself.”?That stuck with me all these years, and I hope it helps encourage you too!?Security might seem like a bit of a boring topic at first glance, but with a few simple changes, you’ll be better than most and dramatically reduce the risk of a significant blunder. ?? Spreadsheets will give you and your team extraordinary flexibility, and loads of hard-to-beat features (I personally love the speed of dragging to "fill down”) ?? … but they’ll also give you ample opportunity to make a #securityBlunder. ?? I’d go so far as to say that #spreadsheets *tend toward* #insecurity.?Even a fully locked down Excel doc with a password can lead to oversharing — where does that password go, in a note or email??How does it get shared to others??What happens when a teammate leaves the company??Does it get revoked or cycled? ?? Beyond simple mistakes and blunders, you may also hit the limits of what spreadsheets can give you. Let’s work biggest to smallest: folders, files, tabs, rows, cells. ?? #Sharepoint, #GoogleDrive and #Dropbox will certainly give you a nice method to control who can see or edit across an entire folder and cascade that permission set into the files.?You’ll have single sign on, which is quite solid and convenient. ? You may hit limits, however, locking down specific tabs, rows or cells. For example, you build a simple sales quote / pricing tool in Google Sheets that takes on some more life over the years.?Perhaps you want to start differentiating commission percentages for your sales staff, based on seniority.?You’ll want to implement security at the record/row level, and that may go beyond the limits of your platform. —————— Let's think through your options: 1. In a #spreadsheet, plan out your structure of folders and files.?Make a visual map in #Miro or #LucidChart and think through who should see what.? And if you have multiple sheets shared with different sales reps, plan out your process for manually copying changes from one sheet to another. Also do a bit of thinking and experimenting with #externalFileReferences and lookups.?Create two logins for yourself so you can go in as the full access architect, and then also login separately (you can use two different browsers), to test it out as a limited end user. ???? 2. In a #RapidApp development environment?such as #Claris #FileMaker, you’ll be able to translate your visual security map into a full fledged secure application. ?? Again, be sure to test it out thoroughly.?Create two logins and then try to find your way into sensitive information.?Better yet, ask a friend or two to hunt around and ensure that they can’t find a back door. Thank you!

What has more #pickers than a banjo conference? A great software system! # 9 on our?"Top 10 Dysfunctions of #Spreadsheets”?… #NoPickers. ?? What is a "picker"??Simply put, a #picker is a visual control that guides data entry.?Also called chips or value lists.?They can be radio buttons or checklists, or more classic pick lists, allowing one or many choices, or overrides. ? Many spreadsheets that don’t have #pickers at the outset when the data entry flurry first starts.?Perhaps the #workplaceInnovator wasn’t even sure how the columns would be used, and didn’t even have the full list to pick from. ?? For example, you may create a simple PO tracking spreadsheet for your department, and you ask your teammates to enter the Vendor.?But you don’t yet have a complete list of vendors.?What do to??Often, we just start asking for the data, without a control in place. And downstream, that can cause a fair amount of inconsistent data that can be cumbersome to unravel.?Customer A may end up with five different spellings and small differences in their billing address … yet they are the same customer.?So which one is the best source of truth??That’ll be a weekend you’ll never get back, ha! ?? ————————— Yes, of course you can add pickers into your #spreadsheets, and in this video we’ll walk you through a bit of how that functions in #GoogleSheets. This video also gets into a bit of data modeling.?You’ll want to try to unpack, understand and document the relationship between columns.?So that when you pick column A (the Vendor ID), then columns B and C (Vendor name and phone number) really just pull through or reference your vendor table.?That’ll need a bit of a think. ------------ What are your options? 1. In a #spreadsheet, try to add pickers from the beginning, even if the lists are relatively small to start.?A small “chip” in #GoogleSheets can save you headaches downstream. Think through as well who can add to that list, whether users can enter items that are not in that list, and whether multiple items are allowed per row (ie. this PO is actually going toward multiple related sales contracts). 2. In a #RapidApp development environment?such as #Claris #FileMaker, you’ll be able to properly model out a relational data model.?That’s techie speak for how tables connect. ? And when you have Vendor info only in the vendor table, and customer info only in the customer table, you’ll find that your keystrokes and opportunities for confusion go way down. #SingleSourceOfTruth for the win!

# 8 on our?"Top 10 Dysfunctions of #Spreadsheets” … drum roll please … #TooManyRows.?Have you run into this brick wall before?!??? We’ve seen this many times — it makes the phone ring here at Kiza.?“Our Excel doc has run out of rows so it is time for us to get a more professional system.” ?? #GoogleSheets and #Excel will walk you through their specific limitations — roughly about 1 million rows, depending upon the number of columns. I had a little fun and copy / pasted a chunk of sample data into an Excel doc.?I did that a few times, selecting all each time so that my clipboard grew (exponentially), and it didn’t take me long at all to max it out. A common use case is when a #workplaceInnovator like you hooks some third party input or #API into a #spreadsheet as the back end database, or they have an admin do some bulk imports of CSVs.?We’ve seen change logs, or website orders or survey results feeding in dynamically that goes a bit wild over time. ?? #CaseStudy —?We worked with a custom furniture builder who basically went viral.?Good for them!????? They brought orders in from #Shopify every day, automatically bringing data into a centralized Google Sheet for inventory tracking and fulfillment.?It worked for a time … and then one day it broke.?Ack! It would probably be harder to reach that 1M limit with only manual data entry, though we’ve seen that too. ———————— What to do? 1. In a #spreadsheet, before you start hooking in a batch loader or asking your teammate to paste in a CSV every day, be sure to do a little math about how your data might grow. #Model it out.?That’ll be easy for you to do … in a spreadsheet, ha! Think not only about current?volumes, but what if your volumes of logs or responses continues to grow 2x or 4x … how many months would it be until you hit that #limit? Try to also keep your #columns narrow, ie. few.?If you do automate bringing in orders from a web system, for example, see if you can select only the columns that you really need, rather than bulking it up with a bunch of extra data. Lastly, set yourself a monthly calendar reminder to check.?If you see the slow train coming, you’ll be able to adjust (or call us) in time before the day comes, avoiding any emergency fix. ??? 2. In a #RapidApp development environment?such as #Claris #FileMaker, you’ll be limited only by the drive size and the file size, which if hosted out on Amazon Web Services (#AWS), will be pretty darn big!??? You’ll also be able to architect scalable solutions to automatically archive data periodically, be that as a part of a nightly or more periodic process. ??? Monitoring will also be a natural part of the process of a solid data solution, so you’ll avoid that dreaded emergency moment and keep smooth sailing as you grow your operation. #NoLimits!?Dream big.

Do you add #comments into your spreadsheets??That’s # 7 on our list of the?"Top 10 Dysfunctions of #Spreadsheets”.?Learn more here.??? Adding comments into cells directly within a spreadsheet may make sense in some use cases, for a quick back and forth with a teammate.?And we use them too! One key tip -- be sure to keep those comments as purely …?#commentary.?ie. notes, or questions about the data.?Once your comments start to turn into #data, you may start in motion a precedent that can get expensive to unravel later. ?? #CaseStudy — we had a client who baked in years of comments within a spreadsheet tracking licensing expiration and renewal dates with 3rd parties.?Those comments ranged from ad-hoc exchanges, to fairly critical information such as reminder dates, contractual obligations and $ amounts.??? Our experts devised a cool way to grab the raw text from the underlying #XML out of #Sharepoint + #Excel, and to present that to the users to parse and enter into a robust database.?That’s all possible, to be sure! The challenge to unravel information like this after the fact comes from the lack of standardization.?Free-form text box, tends toward free-form data entry.?And comments, be they on a specific cell or comments about the entire sheet, tend toward very free form style. Two tips: 1. In a #spreadsheet, we’d recommend simply adding a #column or two to track notes and comments.?That makes it all a bit more #WYSIWYG — what you see is what you get, out in the open for all to clearly see. Comments have the seeming benefit of hiding themselves, collapsing into that little handy triangle icon, which keeps your columns nice and compact.?But just like people, each of our strengths can also be a weakness. Hidden data can become a little "silent killer” in your quest to keep your data clean.?If you can’t see it, you can’t manage it. Comments do allow you to assign tasks / ownership to one-another in Google Sheets.?That’s fairly handy, to be sure.?So consider using a mix of a new column to track all of the notes, plus assigning some ownership for the entire cell using the comments feature. Also consider adding some #instructions and #guidelines in the column header about what type of information you expect within that set of comments (ie. “Please add any notes or questions here. Please use the Key Dates tab for any dates, and do not place them within these notes.”) 2. In a #RapidApp development environment?such as #Claris #FileMaker, you can create a rich comments log, storing the history of an exchange, with clear ownership and date-time stamps. You can also create connections so that your would-be free-form comments now tie directly to data.?For example, you could require comments with each key status change within a contract management module.?Now we’re really talkin’ to each other! With a robust software application, you’ll be driving a rich data exchange that’s trackable and flexible for whatever the future brings! Thank you.

We’re half-way through our "Top 10 Dysfunctions of #Spreadsheets" — # 6 in the list??Using spreadsheets for #DataEntryForms.?What to do???? You may use spreadsheets for data entry, finding this to be a natural and easy way to start collecting information.?You may already have a shared Google Sheet with all of your teammates … why not just as a few questions and have people fill it in? ?? Ok, perhaps for a certain use case that you have confidence will not change or spread or sprawl … maybe that works. For example, you may have a list of parts for an urgent order.?You ask the team to enter the quantity on hand for each SKU to do a quick stock take to see if we can get it out the door today. ?? In our experience, it typically doesn’t stop there … your needs may evolve, the questions may change from a flat response into requiring a rich response.?Now you need to know the suppliers or assemblies for each part, and that creates a #oneToMany… and those don’t fit too well in a 2-D spreadsheet. ?? Most good forms also reference live and accurate pick lists.?Your teammate may fat-finger a SKU# into your survey, and now you have some confusion that needs to be clarified, that is if you catch it before the notes go off to the sales team!??? Instead, a good form will give the users a #pickList of SKUs, including displaying the part description to confirm that they picked the correct part.?In general, you want your teammates to pick rather than free-form type! What should you do about it? 1. If you are stuck with #spreadsheets for now, consider at?least creating a few example responses in the first few rows, and an explainer key off to the side.?You may also want to define the?data types you expect to the entire column (ie. date, text, number). Adding symbols such as $ and “,” and defining the decimals expected can help guide the user toward more accurate data entry.?That little comma will help ensure that you actually have 1,000 parts of SKU ABC, rather than only 100! Consider also adding?“chips”, pickers, drop-downs or controls to the columns.?That will make your spreadsheet more form-like. 2. In a #RapidApp development environment?such as #Claris #FileMaker, you can create forms rapidly, referencing live data to create fast, type-ahead?pick lists, and control / validate the results. You may also be able to create a simple #queue to see who has yet to enter their information, #notify users of their #progress through the queue …?or better yet, #dashboard the results in real time! Heck, you may not even need that stock take if you build it out right: you might simply need to run a report and then get that urgent order confirmed for sales in real-time! Thank you.

# 5 on our "Top 10 Dysfunctions of #Spreadsheets" — mixed #DataTypes, such as text typed into number cells, and garbled up dates, oh my ... ?? We’ve all been there.?Why is this formula giving me the dreaded #VALUE! error code?! ?(And why does Excel make that error all caps??We hear you; you don’t have to shout at us.) Sometimes the answer is mixed up #DataTypes.??? For example, say you are typing up a list of upcoming due dates, and it seems like all of the sudden data entered as 1/15/13 displays in the very next row as "Jan 15th" … what happened here??Is that a legit date??Can I trust it, and can I perform math based on that date, or did my data types get off????? Hmm, so you reformat that entire column of data … again, because you don't trust it to "stick." Or worse yet, sometimes you may encounter data type issues with no visual warning.?#SilentKiller! For example, you may enter?a number that seems to displays correctly, but then evaluates incorrectly in a daisy-chained?formula.?? ?? What to do about it? 1. In #spreadsheets, consider defining data types from the outset on your entire column, top to bottom.?And add new columns for anything such as text comments or status about a number, rather than mixing text into your number column. Plan to also create an entire new tab on your spreadsheet for other new data tables, rather than stacking them vertically within the same tab or sheet. Remember a simple guiding principle: one column, one data type. 2. In a #system such as a #rapidAppDev environment like #claris, you will have a bit more structure to rely upon.? Most robust systems will have you explicitly define one and only one data type for a specific field or attribute. Most will also allow you to define validation rules to go even further in controlling your data input.?For example, a survey response may only expect numbers 1 - 5.?The user interface may help guide that, but the database may also want to validate that data. ---------------- Getting your data types right will ensure that your data stays clean, regardless of your platform!

# 4 on our list of the "Top 10 Dysfunctions of #Spreadsheets” … drumroll please … #ColorFormatting.?????Why can something so simple become a dysfunction for your team?!? Read on:?? Color formatting your spreadsheets may be helpful to those reading the output, or doing a quick review of data, but it may not scale or change well over time. And it may actually lead to conflicting sources of truth. ?? For example, say that you use #colorCoding to review a large set of data, flagging rows for review by another teammate.?Ask yourself, can that teammate quickly filter or search for your color tags after the fact??Might they miss a row or two if they are quickly scrolling and eyeballing? ?? And do you have a key of rules that explains the meaning of yellow vs. red??This #metaData — the data about your data — brings a ton of clarity, and as #BreneBrown says, #clarityIsKind. ? A common example might be that you conduct a one-off review of inventory levels across a set of products. In this case, you would likely want to include some sort of numeric rule, to signify reorder levels per product, rather than simply a color code. That’ll make it much easier next time you do a stock take! ?? Those one-offs have a way of becoming a process over time. ------------ Two ideas for you: 1. If you are stuck in spreadsheets, then consider adding a new column or two with the attributes broken out as 0’s and 1’s, or a checkbox, or text.?And use #ConditionalFormatting instead of cell formatting to bring some visual pop dynamically.?Conditional formatting rocks, in that it actually improves your data entry quality, rather than creating a trap door for your teammates.?You make the rules. 2. If you can migrate your spreadsheet into a system, then model that status or reorder level as a new field, and add controls to dictate how data should be entered going forward. Bonus points: break out the status changes into a historical log, so you know who entered them, where, when, and add any comments. ?? Enjoy!

Continuing our series on the "Top 10 Dysfunctions of #Spreadsheets" -- consider # 3 on our list: #BrokenFunctions.?Such a sneaky one!?What to do?! ?? Plan that it may happen to your spreadsheet.?No way around it, that we know of. So you’ll want to build in a process to carefully *trace through formulas*.?It can happen with something as simple as adding new row into a sheet, without accounting for which formulas might apply to adjacent rows.?Hidden in plain sight. #Excel will do the best that it can to warn you about a broken formula.?But sometimes the formula generates inaccurate results simply from offset / whacked inputs, not from a broken formula per se. ?? ------------ Two ideas for you: 1. In #spreadsheets, consider trying to work more often with tables, and #tableFormulas.?That’ll help a lot! I have a friend and colleague Gary who is an Excel guru, and you should see the things that he can do with a table or array formula.?Yowza!?So there is hope … 2. In a #system, your formulas will look very familiar, and hopefully even simpler to implement and maintain.?If you get your data model correct, then your system will be built to scale. ?? Get that data model right, and all else takes care of itself! Thank you.