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 9 Best Calendar Apps to Stay on Track in 2020 2 7 Best Project Management Apps to Boost Productivity 3 10 Best Keyboards Under $90 on Amazon 4 18 Best Time Management Apps and Tools (2020 Updated) 5 10 Best Task List Apps to Boost Productivity in 2020

              Read Next

              Advertising
              Advertising
              Advertising

              Last Updated on June 26, 2020

              9 Best Calendar Apps to Stay on Track in 2020

              9 Best Calendar Apps to Stay on Track in 2020

              The success of our day is largely dependent on the quality of our planning. Not to miss out anything in their to-dos, some people prefer to make a list of upcoming tasks in a notebook, while others have long started using digital technology solutions.

              Calendar applications are some of the main tools that are worth using to organize our life and plan your time carefully.

              Many people have switched to specific tools; however, there are still some who do not use calendars on a daily basis. They may find some applications uncomfortable to use, non-functional, or expensive.

              In this article, we are going to check out the best calendars apps to help you stay organized.

              1. Any.do Calendar

                This calendar has direct integration with Any.Do To-Do List, which gives you a unique tandem of two applications.

                Apart from its extended functionality, Cal Calendar is easy to use. The creation of events is very simple and fast.

                What is more, depending on the name of the event, the application automatically adds contacts and geolocation data to the entry description. You can even import your lists and entries from Any.do.

                Any.do Calendar is a great option for any type of user. It is very convenient and doesn’t overcomplicate the mode of display.

                Another good thing is that this tool is available for free, so you can use it without spending a dime for the software.

                Download Any.do Calendar here!

                2. Google Calendar

                  Google Calendar is the official calendar for Android devices that has been tested out by many users around the globe. If you are right now trying to get away from it, consider changing your mind.

                  Since this application is installed on most Android devices by default, many users think that there is nothing special in this program. They are wrong.

                  Advertising

                  Google has been updating its calendar for quite a few years, and now it comes in Material Design with advanced event features, direct integration into other Google services (for example, supports reminders and Google Now), and comes with Exchange support.

                  The program is super easy and will not cost a dime for you. It is a good thing, right?

                  Download Google Calendar here.

                  3. Jorte Calendar

                    Jorte Calendar is one of the most popular calendar applications in the Google Play store.

                    The program features a wide range of configuration options, with many design alternatives. You can adjust display mode to your needs and view calendar entries sorted by month, week, or day (by hours). It is convenient for quickly picking the desired date, using task bars, and setting reminders.

                    Apart from its ordinary information storing function, this application can be a special cloud service, Jorte Cloud, allowing you to synchronize calendars, schedules, and task lists on multiple devices. The application also supports data importing from Google Calendar.

                    For those paying attention to the program design, there is also Jorte Store, where you can buy styles and icons to personalize the calendar. This feature makes Jorte is one of the brightest calendar applications.

                    A basic version of the app is free of charge, so if you do not want to spend money on a calendar application, it is a good option.

                    Download Jorte Calendar here.

                    4. Business Calendar

                      Business Calendar is geared towards people who use their calendar for work purposes and business task planning. It offers different modes with wide configuration capabilities.

                      The application gives a default view mode by months, and events can be marked in different colors. Display modes/ sorting can be adjusted to your needs (by month, day, year, or events).

                      You can also set a multi-day viewing mode to see how things look for the next few days. Scrolling up and down moves you by month, and if you check a few days, they will be shown in a more detailed form.

                      Advertising

                      The day display mode offers hourly scheduling, and the schedule mode provides a detailed schedule for a single event.

                      Business Calendar is a great tool for planning/ scheduling cases, tasks, and events. There is a support for recurring events, which can be set up in just a few clicks.

                      Having purchased software, you can use it to import and export other calendars, delete, copy, or move several events at the same time.

                      Android Business Calendar application may seem somewhat chaotic, but it works fine and is easy to work with if you play with it for a while.

                      A full version of the application is available for $4.99, but you can also find a free version for the app test drive.

                      Download Business Calendar here.

                      5. Calendar

                        Calendar is a relatively new app. It works as a web app and for both iOS and Android devices. It is an intelligent app that learns your contacts, schedule and tasks. It also helps you schedule and arrange meetings according to your available time slots.

                        A good thing about Calendar is that it allows you to sync up with other calendars you use such as Apple Calendar and Google Calendar. And so you can manage all the calendars you have in one place.

                        Calendar also gives you analytics of your meetings, giving you a clear picture on how you can improve your time management.

                        Download Calendar: Meeting & Scheduling here.

                        6. aCalendar

                          aCalendar opens our collection of top 10 calendar applications available on the market today. With its appealing design, easy navigation, and great functionality, it is one of the most popular calendar apps in our list.

                          Some of extra functions include color schemes for each case type (48 colors to choose from), different types of demonstrations, different widgets, moon phases, and much more.

                          Advertising

                          Taking into account it functionality, aCalendar is a reliable calendar application that has an easy-to-navigate interface with three display options. Scrolling from side to side allows you to switch between the display modes of the month, week and day.

                          When scrolling down and up, you are moving through the calendar at intervals in accordance with the selected display mode.

                          Apart from its time planning feature, aCalendar synchronizes photos from contact lists or social networks to remind you about birthdays, anniversaries, or any other special dates.

                          The program also supports data transfer through NFC and full-screen widgets, which eases your work with any data.

                          The program is available for free, but you can also get even more features if you buy the extended version of software for $4.99.

                          Download aCalendar here.

                          7. DigiCal Calendar

                            DigiCal Calendar is very similar to Cal Calendar in the fact that the application focuses on design more than on its functionality. However, this doesn’t mean that the application doesn’t serve the purpose.

                            With this calendar application, you can synchronize all your calendars and view them in different ways.

                            Along with the basic functions, this program comes with support for Google Calendar, Outlook, and includes some unique and interesting features. You can match keywords to the image or set up a dark theme.

                            The app can even show you the weather forecast for three days. There are many other features that deserve the attention of people who really like to use calendar applications.

                            Download DigiCal Calendar here.

                            8. SolCalendar

                              SolCalendar can be called a universal application. It claims to be an all-in-one digital solution having a basic calendar functionality combined with some other advanced features, such as weather forecast for a specific day.

                              Advertising

                              The application supports Google Calendar, as well as tasks, widgets, lunar calendar and even Foursquare.

                              Those searching for a calendar application to cover just everything in its functionality, SolCalendar is a program to consider. There are a lot of interesting things in this application; the program does an excellent job working in “all-in-one” mode.

                              Test SolCalendar – the application is available for free. You can test it out without purchasing the service.

                              Download SolCalendar here.

                              9. Today Calendar

                                Today Calendar is one of the most hip and edgy calendars in our list. The solution was one of the first ones that really embraced Material Design and remains one of the few that adhere to the neat style.

                                The calendar application offers bold colors, simple controls, and great functionality. This is not as heavy an application as many others; it will not eat all the memory of your device.

                                If you are not searching for something complicated and over-functional, Today Calendar is what you need. You can always test the application before paying for it – the program is available for free.

                                Download Today Calendar here.

                                Our Verdict

                                Searching for the right application to manage your various calendars and plan your busy day can sometimes turn into a streak of obstacles.

                                Most of us need flexible applications that can be easily used to manage our tough schedule. The application should have all necessary time planning functions and be intuitive.

                                Stylish design and limitless compatibility also matter. It is not always easy to find such a program.

                                The above digital calendar solutions fall under the category “worth” of being used. They are modern, multifunctional, easy, and easy. Pick the one you like!

                                More Productivity Apps for Better Time Management

                                Featured photo credit: Unsplash via unsplash.com

                                Read Next