Advertising
Advertising

7 Tricks To Make You A Spreadsheet Expert

7 Tricks To Make You A Spreadsheet Expert

Computer spreadsheet programs, like Excel, are an excellent tool for organizing, storing and manipulating data. Here are tricks that will assist with data entry into the spreadsheet:

1. Control the cell pointer during data entry:

Excel automatically moves the cell pointer to the next cell down when you press Enter after entering data into a cell. This gets distracting, especially, if you wish to add data in a row instead of a column. To change the setting, use the Excel options dialog box.

  •   Choose: File ->Excel Options -> Advanced ->Editing Options.

control_pointer2
    • Enable Check-box “After pressing Enter, move selection” and the corresponding drop-down, controls the action of the cell pointer during data Entry.
    • Tip: On disabling the check-box, arrow keys can be used to control the direction of the pointer. For example, if you’re entering data in a row, press the right-arrow key rather than Enter to move fluidly to the next cell in the same row.

    2. Store data within Data Tables :

    Excel Spreadsheet is grid of rows and columns. While adding data into Excel, intuitively one does not use Data Tables. However, Excel Data Tables are simple and powerful. They help in easily adding more rows and columns without worrying about updating formula references, formatting options, filter settings, etc. Data Tables take care of everything.

    To add Excel Data Tables, select a Range of Cells comprising of data and click on Table button residing inside the Insert Ribbon.

      Data Table Tricks:

      • Data Tables comes with its own set of Data Filters and Sort Options by default. Without Data Tables, Excel allows only one set of filters per worksheet. However, if a worksheet has more than one table, then each table comes with its own filters and sort options.
      • Formulas inside a data table can use meaningful header names instead of using cell references. Moreover, on adding or removing rows, one need not worry about updating the references.

      Formula
        • With structured references, calculating inside columns becomes very easy. In addition, excel automatically fills the formula inside rest of the cells in the column.
        • Data Tables can be formatted with a single click inside Table Tools
        • Duplicate Data can be deleted from the Data Tables with a single click inside Table Tools
        • Data Tables can be converted back to a Range with a single click inside Table Tools
        • Data Tables can be exported to a share point site with a single click inside Table Tools
        • Data Tables can be summarized by adding a Total Row with a single click inside Table Tools

        3. Data Entry Forms

        Once the Data Tables are set up in a worksheet, Excel Data forms provide the ability to enter data into those tables using a Dialog box. To get the Form Button on the Quick Access Toolbar, follow the below steps:

        • Click on Customize Quick Access Toolbar
        • Select More Commands from the list
        • Choose All Commands from the Dropdown
        • You should find Forms in the list of All Commands
        • Click on Add, and it should get added to the Quick Access Toolbar, once you click on OK

        In order to access the Forms Dialog box, simply select the Data Table Range in the worksheet and Click on Forms from the Quick Access Toolbar.

        Data Form

          The options on the Dialog box are as follows:

          • New: Adds a new record into the Data Table
          • Delete: Deletes the displayed record from the Data Table
          • Restore: Restores the changes made in a Data Table Entry
          • Find Prev/Find Next: Finds the records as specified in the Criteria Field
          • Criteria: Retrieving data that meets a set criteria
          • Close: Closes the Dialog Box

          4. Data Validation Lists:

          Creating drop-down list and enforcing data entry using drop-down, ensures that data consistency is always maintained in your worksheet. To create a drop-down list:

          • Enter the list of items in a range.
          • Select the cell that will contain the drop-down list
          • Choose Data -> Data Tools -> Data Validation.
          • In the Data Validation dialog box, click the Settings tab.
          • In the Allow drop-down list, select List.
          • In the Source box, specify the range that contains the items.
          • Make sure that the In-Cell drop-down option is checked and click OK.
          Tip: If the drop-down list is short, then you can enter the values directly in the Source Field separated by commas.

          5. AutoCorrect for short-hand data entry:

          AutoCorrect, by default is meant to do useful things like capitalizing the first letter of sentences, or correct the accidental use of the caps lock key. In addition, one can customize the AutoCorrect to create shortcuts for commonly used words or phrases. For example, below I use the abbreviation: “gro” for Groceries, “subsc” for Subscriptions and so on.

          In order to get to the AutoCorrect Options,
          Click on Files -> Excel Options -> Proofing -> Auto Correct Options.
          Alternatively, the key-board short cut is: ALT+T+A.

          AutoCorrect2

            AutoCorrect Tips and Tricks:

            • To Add an AutoCorrect Shortcut, type the shortcut text in the Replace Field, and type the text that it will expand to in the With Field, and then click Add.
            • To remove an AutoCorrect shortcut, locate the shortcut in the AutoCorrect list and click on Delete.
            • To override AutoCorrect Press CTRL+Z while entering information into the cell
            • To share the AutoCorrect entries with your friends or across machines, simply locate the *.acl file on your hard drive, and copy it to the corresponding location on the other machine.
            • Excel converts email address or web URL entries into hyperlinks using AutoCorrect. To override a single automatic hyperlink, just click Undo (or press Ctrl+Z) after you enter the text. The hyperlink disappears, but the text you entered remains intact. To disable this feature completely, go to AutoCorrect Options -> Click on Tab: Auto Format as you Type -> Disable the check box: “Internet and network paths with hyperlinks” .

            6. AutoComplete to automate Data Entry:

            Excel’s AutoComplete feature makes it easy to enter the same text into multiple cells. Simply type the first few letters of a text entry into a cell and Excel automatically completes the entry, based on other entries that you already made in the column. It helps to not only reduce the typing, but also to maintain consistency in the data entered.

            Auto Complete Tips and Tricks:

            • Auto complete works only on contiguous column of cells, so do not leave blank rows to fully utilize Auto complete’s potential
            • Auto complete changes the case of letters automatically
            • If the column contains multipls entries that match the first few characters, then Auto Complete does not kick off until your entry matches one of them quickly.
            • You can access the Mouse controlled version of Auto Complete by right clicking the cell and choosing the “Pick from Drop Down List” Option.
            • You can access the same drop down list from keyboard by pressing: Shift+F10.

            7. Proofread with Audio

            Excel has a handy text-to-speech capability, that is capable of reading the contents of the cell as you enter it. In addition, it can also read back a specific range of cells.

            To enable the feature in the Quick Access Tool bar:

            • Click on Customize Quick Access Toolbar
            • Select More Commands from the list
            • Choose All Commands from the Dropdown
            • Select all the Cells starting with Speak Cells and Add them to the Quick Access Toolbar, by clicking on Add.

            Advertising

            AudioProof

              To read a range of cells, select the range of cells, and Click the Speak Cells button.

              You can change the orientation, by clicking on “Speak Cells on Column” or “Speak Cells on Rows”.

              If you wish the excel to speak the contents of the cell as you enter, then click on “Speak Cells on Enter”.

              The below Image shows the symbols for each of the options:

              Have I missed out on some of your favourite tricks on data entry? I would love to hear about them in the comments section.

              More by this author

              29 Incredibly Useful Websites You Wish You Knew Earlier 20 Uses for Microwave That Will Surprise You For Sure 7 Tricks To Make You A Spreadsheet Expert 10 Tips to Travel in Crowded Public Transport What Every Introvert Should Do To Live A Great Life

              Trending in Technology

              1 8 Most Effective Games and Apps to Learn to Type Fast 2 15 Organization Apps to Boost Your Personal Productivity 3 10 Best Calendar Apps to Stay on Track in 2019 4 7 Clever Goal Tracker Apps to Keep You on Track in 2019 5 How to Type Faster: 12 Typing Tips and Techniques

              Read Next

              Advertising
              Advertising
              Advertising

              Last Updated on September 11, 2019

              8 Most Effective Games and Apps to Learn to Type Fast

              8 Most Effective Games and Apps to Learn to Type Fast

              Computers and cell phones have become an integrated tool in our professional and personal lives that the original methods of using pen and paper may not be so common anymore.

              Although our old-school methods of note taking may not have entirely left us, technology is advancing with no intention of slowing down; iPads are moving into service industries, video calls are taking the place of in-person interviews, and store receipts are making its way into our email inbox – all of which requires the skill of typing.

              Learning a new skill doesn’t have to be boring and never had to be. Thankfully, there are effective games and apps that can help you learn to type fast with swift precision and accuracy.

              Why Typing Fast Matters?

              Learning how to type fast is a game changer. In fact, you can save 21 days per year by typing fast!

              Although shaving several minutes from curating a long email or texting paragraphs in a text message may not seem to be of great significance, the minutes soon do eventually add up and the long list of tasks then evolve into frustration. By the end of the day, time is being wasted, and the work pile is stacked high over your head.

              Why not alleviate some of those frustrations through practice and dedicating your spare time to build muscle memory?

              Learning a simple skillset like speed typing can drastically improve other essential areas in life including time-management and prioritization. Not only does it help you efficiently complete tasks at work and in your personal life, but it also boosts your productivity.

              8 Most Effective Typing Games and Apps

              Everyone learns at different speeds and uses various methods. While some work better under pressure and tight deadlines, others thrive when given ample amounts of time to learn and soak in the knowledge that is being provided. Despite the number of resources that are available in the hollow corners of the internet, it’s all about finding one source that helps you learn at your fullest potential.

              Whether you’re a keyboard ninja or not, here are some effective typing games and apps that allow you to test your speed, accuracy, and maybe shoot some spaceships along the way.

              Advertising

              For Beginners

              1. Speed Typing Online

                What’s more fun than to type to the story of Alice in Wonderland or the lyrics to “Hey Jude”? Speed Typing Online is an online typing game that allows you to dive into the creative and familiar world of famous books, fables, songs, and even hone your skills in data entry.

                The bright blue frame holds the text, which then turns green after punching in the accurate keystrokes. After the end of the personal timer, a statistics page appears to show you your typed words per minute, accuracy, correct and incorrect entries, and error rate.

                2. Typing Trainer

                  Typing Trainer

                  is another online platform suited for beginner typists looking for step-by-step lessons. Learning the keys on a keyboard can confusing especially for those who aren’t as familiar or getting adjusted to typing on a computer keyboard.

                  Typing Trainer has a collection of step-by-step tutorials that covers everything from sentence drills, introduction to new keys as the lessons progress, and skills test. The Typing Trainer specifically highlights unique features in each lesson including a warm-up section where the user begin to build muscle memory and learn to type without looking at the keyboard.

                  The website is also programed to identify difficulties the user is facing when typing specific words or sentences.

                  3. TapTyping – Typing Trainer

                    There is the feeling of physically typing on a keyboard and then there’s the feeling of typing on a touch screen mobile device.

                    Advertising

                    Since the use of cell phones has become closely integrated into our everyday lives, learning to type on a mobile is much of a skillset as it is to type on a computer. The mobile typing app, TapTyping – Typing Trainer, allows users to practice while on-the-go making it perfect for commuters who want to practice typing during their down time.

                    The app allows you to challenge other typists around the world with TapTyping’s global leaderboard and test your skills by taking advanced lessons. There’s always room for improvement and with the app, you’ll be able to find your mistakes by watching a heat map of your finger strokes.

                    For professional writers and programmers

                    4. The Most Dangerous Writing App

                      Suitable for writers facing a creative block or on a tight-deadline, the Most Dangerous Writing App is a website that forces your fingers to type as quickly as your ideas.

                      If you stop longer than 5 seconds, everything you had written will slowly disappear from the screen.

                      Sessions are timed from 3 minutes to 20 minutes, or can go from 75 to 1667 words. This online app is perfect to brain dump ideas, write a chapter of a manuscript you’ve been stuck on, or help with procrastination.

                      If you’re up to the challenge, try the hardcore mode – an alternative option where a single letter appears on the screen at a time. This level prevents you from seeing the entire word, sentences, or even correct any spelling or grammatical mistakes until the timer is complete.

                      If you’re wondering, copying and pasting is not an option until each the end of each session.

                      5. The Typing Cat

                      Advertising

                        Looking to upgrade your typing skills? Also working as a personal tutor, the Typing Cat has a list of regular typing courses with the option to try other lessons with more complexity such as HTML. Learning to type code is a another valulable skillset worth adding.

                        Even with disregarded interest in the coding world, using the code course enhances your typing skills and allows your fingers to familiarize itself with uncommon word combinations and placement of punctuations on a keyboard.

                        The coding course can be difficult even for typing whizzes, but it’s all a part of muscle memory. According Psychology Today,[1] only a handful of people actually learn how to type by looking at an actual keyboard, while a majority of the population locate specific keys intuitively through muscle memory.

                        Available courses include EcmaScript 6, HTML 5, and CSS 3.

                        Fun typing games

                        6. ZType — Space Invaders Meet Webster

                          Remember playing the iconic 70’s game that allowed you to shoot tiny purple and green aliens from one end of the screen to the other with a two-bullet laser? It’s hard to believe that Space Invaders just turned 40 , but you can still get the same adrenaline rush with ZType, a typing game with the same shooting concept.

                          Ztype works in waves – stages that must be cleared but instead of aliens, you must type out the words before the missiles destroy your ship at the bottom of the screen. Every so often, longer and mor complex words would appear and if the words are not typed in the allotted time, a series of letters will disperse like missles.

                          The game is quick on the fingers and will still have your heart pumping until the very end.

                          7. Epistory – Typing Chronicles

                          Advertising

                            Although this game does cost money to purchase, it is worth the investment if you’re looking for a refreshing and alternative mode to learning how to type fast.

                            Epistory – Typing Chronicles is a role-playing action and adventure game of a young girl riding a fox in a magical and fictional realm; together they combat enemies in the shapes and forms of words.

                            Once you’re starterted, you almost forget you’re playing a typing game. The paper craft art aesthetics of the game has you captivated by the vibrant colors and character’s storyline, while having you build your typing skills.

                            8. Daily Quote Typing

                              Need some inspiration? Say no more.

                              Daily Quote Typing is one of many gammes available on Wordgames.com – a website that offers a variety of typing games ranging from different levels based on your experience.

                              With Daily Quote Typing, users are able to type out inspirational quotes by famous leaders, inventors, and innovators such as Mark Twain and Albert Einstein.

                              Bottom Line

                              At the end of the day, discipline and patience is what teaches to type faster. It comes down to making that commitment to improving not only your typing abilities, but in a lifelong skill that benefits other areas in life.

                              By practicing daily and using effective games and apps, it’s only a matter of time before keystrokes will become second nature and your brain will adapt to learning other skills faster.

                              Featured photo credit: Unsplash via unsplash.com

                              Reference

                              Read Next