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 10 Best Calendar Apps to Stay on Track in 2020 2 24 Best To-Do List Apps to Keep You on Track in 2020 3 How to Type Faster: 12 Typing Tips and Techniques 4 11 Meeting Scheduler Apps to Boost Your Productivity This Year 5 11 Google Chrome Apps & Features for Getting More Done with Less Effort

              Read Next

              Advertising
              Advertising
              Advertising

              Last Updated on March 30, 2020

              10 Best Calendar Apps to Stay on Track in 2020

              10 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. Cal Calendar

                Cal Calendar has recently appeared on the market of mobile applications (Android, iOS). It is the brainchild of the development team of Any.do that features impressive functionality and well-thought-out design.

                The application comes with many great features, including support for Exchange and Google Calendar, widgets, voice recording, and many more.

                Cal 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.

                Cal 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.

                Available for iOS and Android

                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?

                  Available for iOS and Android

                  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.

                    Available for iOS and Android

                    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.

                      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.

                      Advertising

                      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.

                      Available for Android

                      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.

                        Available for Desktop, and iOS

                        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.

                          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.

                          Advertising

                          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.

                          Available for Android

                          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.

                            Available for Android

                            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.

                              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.

                              Available for Android

                              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.

                                Advertising

                                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.

                                Available for Android

                                10. Calendar Widget: Month

                                  Sometimes people do not need a heap of calendar applications. They would prefer to have one efficient calendar widget.

                                  Calendar Widget: Month perfectly fills this niche and meets all the needs of a modern user. It has some features of an ordinary calendar, but the real great thing about this software is that it has 80 or more themes in its library.

                                  The design of the application is great; the themes are perfectly tuned. There are options for almost any theme of your smartphone. The only drawback is the fact that the widget memory is not too large, and you should be ready for it.

                                  This simple calendar widget was created in the image and semblance of widgets from Yahoo. The widget window displays one calendar month with the ability to go to the previous and move to the next one.

                                  If you need a simple and affordable calendar to plan your tasks and affairs for one month, this widget is ideal for you. Calendar Widget: Month is so simple that it does not even have Google Calendar support.

                                  At the moment, it does not have the function of connecting any third-party calendars and services. Developers allow the possibility of introducing support for Google service as an addition to the widget, if users request to have this opportunity.

                                  Available for Android

                                  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