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 How to Improve Your Computer Skills to Get Ahead in Your Career 2 18 Best Time Management Apps and Tools (2018 Updated) 3 Master These 25 Mac Shortcuts to Work Faster and Smarter 4 7 Clever Goal Tracker Apps to Help You Make the Most of Your Business 5 Evernote vs OneNote: Which Improves Your Productivity Better?

              Read Next

              Advertising
              Advertising

              Published on November 9, 2018

              How to Improve Your Computer Skills to Get Ahead in Your Career

              How to Improve Your Computer Skills to Get Ahead in Your Career

              Technology has taken the business world by storm. People and businesses are progressively reliant on technology world these days. As a result, the people with impressive knowledge and experience with a computer are in demand.

              Considering this growing market trend, if you too wish to bag a profitable job, this article is apt for you. Here, we will dig deeper into the business and technology world to look into what are the best computer skills one should invest in and what resources to get help from.

              So, are you ready? Here we begin.

              6 Most-In Demand Computer Skills This Year (and Beyond)

              When talking about computer skills, following are the 6 computer skills that can fill your mailbox with endless job opportunities (besides, having an English and Hindi typing speed of 40+ WPM):-

              1. Spreadsheets and Databases

              Considering the treasure trove of data companies are handling these days, Spreadsheet and Database management skill tops the list. It is considered the most wanted job skill in the market, with a salary of around $53,129/yr for an entry-level position. However, the spreadsheet skill does not confine to auditing data from cells.

              To get hired based on this computer skill, you need to become a Spreadsheet ninja. From storing data to organizing them, applying complex mathematics formulas, and creating complex macros, you need to be an expert at all.

              In other words, you need to have an expertise at working with:

              • Microsoft Excel
              • Microsoft Access
              • Statistics
              • Data Analytics
              • Adobe Campaign
              • Quickbooks
              • XML Database management system
              • Filemaker Pro
              • MATLAB
              • Hive
              • FORTRAN
              • RDBMS (Relational Database Management System)
              • SAS

              2. Social Media, Email, and Blogging

              Though people are socially active, not everyone knows how to use the social media tools on the professional front. In such a scenario, having an expertise at writing engaging yet professional emails, writing blogs, and managing social media activities is a ticket to entry in reputed organizations.

              Some of the skills related to social media, email and blogging that will have a leg up in the hiring process are:

              • Content Management System (CMS)
              • Google Analytics
              • Cascading Style Sheets (CSS)
              • Email Marketing
              • Web Page Design
              • WordPress
              • Digital Media
              • Search Engine Optimization (SEO)
              • Campaign Management Software

              3. Graphic Design and Word Processing

              A solid grasp of skills to present the gained information in the much readable form is another market need. When considering this computer skill category, you need to enhance your skills at:

              • Adobe Photoshop
              • Adobe InDesign
              • Adobe Illustrator
              • AutoCAD
              • CorelDRAW
              • Microsoft Visual Studio
              • Maya
              • Microsoft Word
              • Microsoft Publisher
              • Operating Digital Cameras

              4. Software and Hardware Design and Development

              The Software and Hardware development, in simpler language, means designing, implementing, and testing the computer software/hardware applications. It is the most profitable computer skill, with a different job opening, including Software Architect, Software Analyst, Software programmer, Hardware configuration, etc.

              To be capable of developing and testing the software and hardware, you need to peruse your users’ needs and build an application that hits their pain points. You need to work with different web and mobile app coding languages for gaining the attention of your audience, some of which are:

              • HTML
              • C/C++
              • Java
              • Python
              • XML
              • UI/UX
              • LINUX
              • CISC and RISC architecture
              • Embedded Processor Hardware Design
              • Memory Management
              • PCB Layout
              • SQL

              5. IT Troubleshooting

              Working on computers is one thing and having a knack of looking into the associated issues and resolving them in real-time is another. There are various employees and graduates who work with computer systems in their daily lives, but get confused when it comes to dealing with frozen programs and performing routine maintenance.

              So, if you are familiar with the basic anatomy of computers and machines and know the tricks to perform such tasks to streamline the working process, enter the IT support arena. The prime IT troubleshooting skills one needs to work upon are:

              Advertising

              • Backup management
              • Diagnostics
              • End User support
              • Client-Server management
              • Installation and configuration
              • Issue tracking system (ITS)
              • System administration
              • Tech support

              6. Enterprise Systems

              Many Enterprises have their own enterprise solutions like PeopleSoft for managing their data and operations.

              Becoming a master at using the enterprise solutions mentioned in their ‘Requirements’ area can also lessen the barrier to get a job.

              The Roadmap to Excel Your Computer Skills

              Now that you know the 6 most popular computer skills in the market, the first decision, you need to take is which computer skill you want to work upon. All the skills are equally considerable for making a career ahead. So, choose one as per your interest.

              Once done, look forward to the following resources for boosting your computer skills and getting a reputed, handsome job.

              Depending on the computer skill you opt, here are the online courses you can turn to for elevating your career opportunities:

              1. Become a Spreadsheet and Database Ninja

              a. Microsoft Excel Training Online Course – Basic & Advanced

              Whether you are a beginner, intermediate or expert at working with spreadsheets, this online course has something new for everyone to become excel efficient users.

              Divided into 48 interesting lessons, the course empowers you to learn the technique of using mathematical, statistical, logical and text functions, sorting and filtering the data, creating Pivot Tables, building macros, finding a value with VLOOKUp, and much more.

              b. Excel- Online Courses, Classes, Training, Tutorials on Lynda

              This is another impressive course to unlock the power of Excel. Designed for people from all walks of life and familiarity with Excel tools. This course helps you gain in-depth knowledge from the experts about this data-analyzing tool and make the magic in spreadsheet creation and management process.

              c. SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL

              Build a successful career in the data analytics world with this online course.

              Revolving around the two top databases, MS SQL and PostgreSQL, this course will give you a comprehensive knowledge of the working of the databases and understanding of all the related elements, including how to create, update, organize, and safeguard the data.

              2. Rule the Social Media World

              a. Free Social Media Course by HubSpot

              Advertising

              Introduced by the top experienced inbound professors from Hubspot, here you will get a comprehensive knowledge of Social media marketing.

              From understanding how to build relationships on different social media platforms to creating a perfect strategy to engage them, leveraging the benefits of social advertising, and analyzing your social media ROI, this course will bring out the best out of you.

              b. Write Professional Emails in English by Coursera

              This online course will be the right weapon to look into the differences and similarities between different email styles, improve your English writing skills, write effective subject lines, maintain the formality tone in emails, and more to make a powerful impact on the receivers with your email.

              c. Ninja Writing: The Four Levels of Writing Mastery

              Founded by Udemy, this online course will excel your skills to build a masterpiece everytime you write.

              Here, you will get the guidance from the editors of the top newspapers and websites, and understand what kind of words and tone to use for leaving a spellbinding impact on the readers.

              3. Launch Your Own Software or Hardware Equipment

              a. Hardware and Software Courses by Coursera

              From Big Data to IoT, Sensor Circuit Design, Embedded Software and Hardware Architecture, and Motor Control Circuit, this course will give you a glimpse of all the hardware devices, equipment, and programming languages to build wonders.

              b. Free Software and Web Development Certifications Online by Alison

              Become the software developer market demands with Alison’s Java, C#, Python, R, PHP, or MySQL database course. From backend to frontend development and software testing.

              The course helps you to learn how to design, develop and test software and working in a team while contributing to the team success.

              c. Free Online Software Development and MOOCs Course by Class Central

              The Class Central online course will make you a master of software development. Here, you will learn the basic concepts of development like Agile, Design Patterns, and Version Control with Git, along with gaining experience of working with the top programming languages.

              Advertising

              4. Live and Breathe Creativity

              a. Udemy: Introduction to Graphic Design

              It is a top-rated program for the new learners and designers to explore the Graphic Design world.

              From the history of communication to the design principles, the course will help the students to learn everything specifically and implement in the real world.

              b. Canva Design School

              From a beginner to advanced level, this course will provide you with an elaborated and comprehensive knowledge of graphic design. In the form of 4 modules, this course will help the interested graphic designers to try out their skills at Graphics, ranging from fonts to colors, backgrounds, images, shapes, and layouts.

              c. Microsoft Word – Basic & Advanced

              62 personalized lessons, premium video tutorials, and unlimited tests and quizzes – this certified course has everything that will make your entry to the business world easier.

              d. Microsoft Office: Word, Excel and Access Productivity Bundle

              The course will make you productive in Word, Excel, and Access through video lectures, articles, and various other supplemental resources.

              Besides, it will offer you exclusive tips and tricks to enhance your skills at using Microsoft office.

              5. Raise Your Reflexes to IT Troubleshooting

              a. IT Troubleshooting Skill Training by Udemy

              IT Troubleshooting Skill Training will upgrade your logical concepts over technical components to find and repair faults.

              It will provide you with a methodological approach to learn the basics of troubleshooting, practice them via exercises and drills, and enhance your IT troubleshooting intelligence.

              b. IT Help Desk Online Course on Lynda

              Advertising

              The Lynda’s IT troubleshooting online course will train you how to respond to the issues related to Windows 10, Office 365, JIRA software, and more, from a local as well as a remote location.

              Mobile Apps and Websites to Enhance Computer Skills

              The aforementioned online courses will undoubtedly train you for a successful career ahead. But, in case you want a long-term support, turn towards the following websites and mobile apps:

              • Duolingo – Duolingo app will improve your spoken and written communication skills in different languages. With the magic of gamification added to the study, the app will make learning a fun and addictive task.
              • Hemingway – Hemingway app defines the length, complexity, and common errors associated with the sentences. In this way, it helps you to write effectively and go ahead in the career.
              • Udemy – The Udemy, with its 2000+ courses, is a great platform to master new skills, advance your career and explore the newer technologies and programming languages. Enrol a course as per your budget and make a brighter future.
              • eDX – eDX is yet another commendable platform you can look forward to. The platform offers you the best of the tutorials prepared by the business professionals and technology experts, ensuring a great boost in your computer skills.
              • Excel Easy – Excel Easy, as the name depicts, will make working with Excel easier for you via their detailed tutorials and 300+ real-life examples.
              • GCF LearnFree.org – It is a one-stop platform for accelerating your future in the computer world. From graphic design to Microsoft Office, resume writing, enhancing adaptability, and much more, the top experts will guide you with everything on this platform.
              • QuarkXPress – The QuarkXPress will aid you to add soul to your design with its unparalleled collection of font style, size, and typography. The best part is that it is avails all the options in different languages, including English, Spanish, German, French, and Italian to name a few.

              Bonus: Tips to Accelerate Your Approach For a Better Career Ahead

              Though these resources will make you stand out from the crowd and get hired at once, you need to focus on various other things, such as:

              1. Practice, Practice, Practice

              It is common to forget what we learn. To ensure that you do not miss any opportunity just because you forgot, keep practising.

              Make it your routine. Polish your computer skills and you will surely end up getting an offer letter from a recognized firm.

              2. Work on Your Efficiency

              Remembering what you learned is not enough; practice to enhance those skills to do better.

              For example, if you take around 50 minutes to create a design, target for 40 minutes.

              3. Look for Shortcuts

              Smart work rules over hard work more often. So, look forward to learning shortcuts to do any task. This will increase your chances to crack an interview and get selected.

              4. Apply for an Internship

              Last but not the least, apply for an internship to see what’s trending in the market and how much you are ready for it. While doing an internship, pay attention to your weak points and work on them. This will ensure a success when you apply for your dream job.

              By the late, it’s never too late to be an intern or grab whatever opportunity to learn something new! Here’s how:

              How to Start Over and Reboot Your Life When It Seems Too Late

              The Bottom Line

              Seeing the current market scenario, it is evident that having computer skills is a boon for a better career ahead.

              So, rather than struggling with thousands of other things and trying to crack interviews, focus on the computer skills mentioned in this article. Utilize the resources and experts’ tips shared and look forward to having a successful and prosperous future.

              Featured photo credit: Glenn Carstens-Peters via unsplash.com

              Read Next