[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Spreadsheets are one of the most widely-used pieces of software in the world. Over 1 billion people use them to collect, process and report data every day. A large portion of those users still have a lot to learn to make the most of worksheets. They will usually get drawn to flashier features like formulas, but spreadsheet data structure is just as important.<\/span><\/p>\n Microsoft created Excel back in the late 1980s and it still dominates the market. Google Sheets is a strong contender but it\u2019s still catching up. Sheets has many (although not all) of Excel’s features, but it also offers the power of real-time updates and collaborative functions.<\/span><\/p>\n Regardless of the program you use, or the data you\u2019re working with, good spreadsheet structure can transform your work. Organizing your data will boost productivity and provide you with better insights.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”glue” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n With all the new software out there, surely spreadsheets are becoming a thing of the past? You might think so, but research from <\/span>Gartner<\/span><\/a> shows that the number of spreadsheets is actually doubling every year. Among other things, this is because spreadsheets can be great bridges between different apps.<\/span><\/p>\n A lot of new online applications aimed to organize and manage data, such as Airtable, mimic spreadsheets. However, they package it in a more modern and user-friendly interface.<\/span><\/p>\n Why? Because columns and rows are still the best format for collecting, transforming, analyzing and reporting almost any form of data.<\/span><\/p>\n The proliferation of new software is leading to more spreadsheets. As companies generate ever more data, from ever more sources, spreadsheets still provide a solution that is simple, user-friendly and accessible. No matter what software you use, surely you store, share and process a lot of that data in spreadsheets.<\/span><\/p>\n Spreadsheets are often the glue between different software. They are multipurpose tools that are relatively accessible but still very powerful.\u00a0<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”the” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The sheer volume of data stored in spreadsheets \u2014 and the incredibly important decisions based on that data \u2014 has led to warnings about the dangers of spreadsheets, especially from the world of finance.<\/span><\/p>\n In <\/span>Forbes<\/span><\/a>, Economist Tim Worstall said that Excel might be the \u201cmost dangerous piece of software on the planet.\u201d He said it was responsible for some painfully expensive decisions: the entire financialization of the economy over the past 30 years and therefore, indirectly, the financial crash of 2007.<\/span><\/p>\n Why are spreadsheets so dangerous? There isn\u2019t a single culprit, but rather a combination of drawbacks:<\/span><\/p>\n A survey<\/span><\/a> in the UK found that 33% of large businesses report poor decision-making due to spreadsheet problems, and 17% have suffered financial losses due to poor spreadsheets.<\/span><\/p>\n Another study<\/span><\/a>, carried out in the US by spreadsheet expert Dr Felienne Hermans, analyzed 15,000 Excel spreadsheets from one company. She discovered that 24% of the spreadsheets with formulas had errors, and that 6% of emails were about spreadsheet issues.<\/span><\/p>\n Spreadsheets are here to stay<\/b>, and making better use of them is critical. Luckily this is becoming easier with online options like Google Sheets that offer a centralized and team-oriented approach.<\/span><\/p>\n Good spreadsheet design will save you time and frustration, and of course, provide you with accurate data.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”best” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”start” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As with any strategic activity, you can only plan a course of action when you have a clear goal in mind. It\u2019s the same with spreadsheets and data systems.<\/span><\/p>\n Start by asking yourself:<\/span><\/p>\n Focus on what data you want to see, not what data you already have. Design it with the audience in mind and remember that data must be presented in a format that\u2019s easy to understand.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”collect” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Once you have a clear goal in mind you need to go back to the beginning and look at how you gather the data. A common mistake here is that people design input spreadsheets to emulate reports.<\/span><\/p>\n An input spreadsheet is where you collect raw data. That could be anything from a list of supplies received to a list of contact details. If your spreadsheet is being used for data collection and collation, don\u2019t be tempted to make it look good.<\/span><\/p>\n If your spreadsheet is not structured correctly, you will encounter problems applying formulas and processing your data. Plus you\u2019ll get errors.<\/span><\/p>\n When planning the structure of a spreadsheet, columns are for groups of related data. Your spreadsheet needs to have a logical, tabular format that can be processed correctly by the software!<\/span><\/p>\n For good spreadsheet data structure, remember:<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2019\/12\/Spreadsheets-structured-as-database.jpg” align=”center” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”validation” _builder_version=”4.27.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Manual data entry inevitably increases the chance of errors, you can minimize this risk by using <\/span>data validation<\/span><\/a>. This is a really simple and effective way to make sure that new data is introduced correctly.<\/span><\/p>\n Remember that technology is smart, but not as smart as you! A word can be typed differently, that means errors can be caused by spaces, keyboard variations and punctuation\u00a0 (e.g., \u201cMarketing\u201d, \u201cMarketing \u201c,\u00a0 \u201cMarkting\u201d; 1000, 1,000, 1.000; the number \u201c0\u201d and the letter \u201cO\u201d). At a quick glance, cells might look the same but a slight difference will make spreadsheets interpret them as different data.<\/span><\/p>\n Applying data validation to your spreadsheet ensures that people entering data in the spreadsheet don\u2019t have to type anything manually. For example, if a column in your spreadsheet tracks order status, you can reduce options to \u201cpending\u201d, \u201cordered\u201d or \u201creceived\u201d.<\/span><\/p>\n All you need to do is add a dropdown menu to your input cells.\u00a0<\/span><\/p>\n In <\/span>Google Sheets<\/span><\/a> go to Menu > Data > Data Validation.<\/span><\/p>\n In Excel, go to Data > Data Validation and select an option under Allow. You can read step-by-step instructions on data validation in Excel <\/span>here<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”safer” _builder_version=”4.27.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You might think of forms only as a way to conduct surveys. But we recommend using them whenever you want to gather any kind of information from colleagues or customers.<\/span><\/p>\n You can use them for a variety of purposes:<\/span><\/p>\n When someone enters data themselves into a form, it\u2019s more likely that the details will be correct. No more spelling mistakes or wrongly entered phone numbers.<\/span><\/p>\n And this is where Sheetgo Forms come in, they are a great way to keep track of important data. They are also easy to create, you just have to select a spreadsheet and turn it into a form. The process automatically identifies the information type for each field.\u00a0<\/span><\/p>\n The other advantage of forms is their user-friendliness. Let\u2019s say you\u2019re a school administrator and you want to create a system for your teachers to report grades. You may be familiar with using spreadsheets to analyze data, but that might not be true for your teachers.<\/span><\/p>\n Sheetgo Forms offer a much more user-friendly interface than a spreadsheet and you can import the data directly into a Sheet, processing reports from your teachers automatically.<\/span><\/p>\n Check out<\/span><\/a> everything Sheetgo Forms can do for your projects.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2019\/07\/blog-How-to-connect-Google-Forms-to-Google-Sheets.svg” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”keep” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This data is stable or fixed; it might change but at a much slower rate. Your database needs to be well organized so you can cross it with activity data.<\/span><\/p>\n Imagine you run a restaurant, you need a database of all your recipes including products, ingredients and quantities. This information is essential every time you generate a production order. Also, it needs to be connected to your inventory system to calculate the necessary ingredients to fulfill a single order.<\/span><\/p>\n It\u2019s important that database data is kept isolated in a private or view-only spreadsheet. Only people who fully understand the system should be able to alter this data (either intentionally or accidentally). This guarantees data reliability and privacy.<\/span><\/p>\n You may need to use multiple sets of database data for one specific process. For smaller spreadsheets, it\u2019s ok to group them together in a single spreadsheet with multiple tabs. But for larger datasets it\u2019s better to keep them as separate spreadsheets.<\/span><\/p>\n Single source of truth (SSOT) means structuring your data so that it is only created or changed in one place. It\u2019s really common to find the same data entered into multiple spreadsheets from a variety of silos, across the same organization<\/span><\/p>\n When a data entry or update is made in one location, it should be updated everywhere. To do this with spreadsheets, you need to share files (for example, using Google Sheets) and make your spreadsheets talk to each other.<\/span><\/p>\n A tool like <\/span>Sheetgo<\/span><\/a> can help automate data transfer between spreadsheets to maintain one single source of truth. This means you no longer need to manually copy-paste data or share spreadsheets as email attachments.<\/span><\/p>\n Activity data is generated by your processes. This is the \u201ccurrent\u201d data that reflects what\u2019s happening in your business on a day-to-day basis.<\/span><\/p>\n In a sales team, for instance, activity data might include the contact details and status of new leads. The team will constantly log and update this information. In finance, activity data includes things like expenses, funding and loans.<\/span><\/p>\n Activity data must have a date (time) attached to it. This is essential for creating a dashboard or report.<\/span><\/p>\n It\u2019s important that people can input activity data in a user-friendly way. But that\u2019s not all, it\u2019s also important that they do not interfere with another person\u2019s data. At the same time, the data needs to be well structured so you can apply formulas for analysis.<\/span><\/p>\n There\u2019s different ways to gather data efficiently:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”prepare” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Spreadsheets play a key role in every business<\/b>. Thanks to the power of the cloud, it\u2019s now easier to work with them more safely and productively than ever before.<\/span><\/p>\n Taking the time to implement a well organized spreadsheet data structure is really important. Doing this enables us to take advantage of all the benefits that sheets have to offer, while minimizing the risks.<\/span><\/p>\n Looking for more help and advice for working with spreadsheets?<\/span><\/p>\n Check out our posts on <\/span>How to connect Google Sheets automatically<\/span><\/a> , how to <\/span>upload Excel to Google Sheets, how to improve the performance of Google sheets<\/span><\/a> or <\/span>how to create a dashboard.<\/span><\/a><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" Spreadsheets are one of the most widely-used pieces of software in the world. Over 1 billion people use them to collect, process and report data every day. A large portion of those users still have a lot to learn to make the most of worksheets. They will usually get drawn to flashier features like formulas, […]<\/p>\n","protected":false},"author":39,"featured_media":22952,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[29],"tags":[28],"class_list":["post-22773","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-spreadsheets-tips","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/22773","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/39"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=22773"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/22773\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/22952"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=22773"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=22773"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=22773"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Spreadsheets: the glue between applications<\/h2>\n
The most dangerous tool in the world?<\/h2>\n
\n
Best practices for good spreadsheet data structure<\/span><\/h2>\n
1. Always start at the end<\/h3>\n
\n
2. Collect data in a tabular format<\/h3>\n
\n
3. Data validation is your best friend<\/h3>\n
4. Get safer input data with Sheetgo Forms<\/b><\/h3>\n
\n
5. Keep your reference data and activity data separate<\/h3>\n
Database data is data that you use as reference.<\/b><\/h4>\n
Maintain one single source of truth<\/b><\/h4>\n
Activity data changes frequently<\/b><\/h4>\n
\n
Prepare your data, plan your system<\/span><\/h2>\n