Advertising
Advertising

Last Updated on August 6, 2018

20 Excel Tricks That Can Make Anyone An Excel Expert

20 Excel Tricks That Can Make Anyone An Excel Expert

After Microsoft developed Excel to the 2010 version, it offered more surprises than ever. In order to deal with tons of big data, you can’t ignore the important role Excel plays in daily work. However, both for beginners and advanced users, there are still many useful tips and tricks that are inevitably overlooked. Here are 20 useful Excel spreadsheet secrets you may not know. Please note that all these functions are based on Microsoft Excel 2010.

1. One Click to Select All

You might know how to select all by using the Ctrl + A shortcut, but few know that with only one click of the corner button, as shown in the screenshot below, all data will be selected in seconds.

One Click to Select All

    2. Open Excel Files in Bulk

    Rather than open files one by one when you have multiple files you need to handle, there is a handy way to open them all with one click. Select the files you would like to open then press the Enter key on the keyboard, all files will open simultaneously.

    Open Excel Files in Bulk

      3. Shift Between Different Excel Files

      When you have different spreadsheets open, it’s really annoying shifting between different files because sometimes working on the wrong sheet can ruin the whole project. Using Ctrl + Tab you can shift between different files freely. This function is also applicable to other files like different Windows tabs in Firefox when opened using Windows 7.

      Shift Different Excel Files

        4. Create a New Shortcut Menu

        Generally there are three shortcuts in the top menu, which are Save, Undo Typing and Repeat Typing. However, if you want to use more shortcuts, like Copy and Cut, you can set them up as follows:

        File->Options->Quick Access Toolbar, add Cut and Copy from the left column to the right, save it. You will see two more shortcuts added in the top menu.

        Advertising

        Create New Shortcut Menu

          5. Add a Diagonal Line to a Cell

          When creating a classmate address list, for example, you may need a diagonal link in the first cell to separate different attributes of rows and columns. How to make it? Everyone knows that Home->Font-> Borders can change different borders for a cell, and even add different colors. However, if you click More Borders, you will get more surprises, like a diagonal line. Click it and save—you can now make it immediately.

          Add Diagonal Line for a Cell

            6. Add More Than One New Row or Column

            You may know the way to add one new row or column, but it really wastes a lot of time if you need to insert more than one of these by repeating this action X number of times. The best way is to drag and select X rows or columns (X is two or more) if you want to add X rows or columns above or left. Right click the highlighted rows or columns and choose Insert from the drop down menu. New rows will be inserted above the row or to the left of the column you first selected.

            Add More Than One New Row/Column

              7. Speedily Move and Copy Data in Cells

              If you want to move one column of data in a spreadsheet, the fast way is to choose it and move the pointer to the border, after it turns to a crossed arrow icon, drag to move the column freely. What if you want to copy the data? You can press the Ctrl button before you drag to move; the new column will copy all the selected data.

              Speedy Move and Copy Data in Cells

                8. Speedily Delete Blank Cells

                Some default data will be blank, for various reasons. If you need to delete these to maintain accuracy, especially when calculating the average value, the speedy way is to filter out all blank cells and delete them with one click. Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Go back to Home and click Delete directly, all of them will be removed.

                Advertising

                Speedy Delete Blank Cells

                  9. Vague Search with Wild Card

                  You may know how to activate the speedy search by using the shortcut Ctrl + F, but there are two main wild cards—Question Mark and Asterisk—used in Excel spreadsheets to activate a vague search. This is used when you are not sure about the target result. Question Mark stands for one character and Asterisk represents one or more characters. What if you need to search Question Mark and Asterisk as a target result? Don’t forget add a Wave Line in front.

                  Vague Search with Wild-card

                    10. Generate a Unique Value in a Column

                    You are aware of the key function of Filter, but few people use the Advanced Filter, which will be repeatedly applied when you need to filter a unique value from data in a column. Click to choose the column and go to Data->Advanced. A pop-up window will show up. As the screenshot shows, click Copy to another location, which should be in accord with the second red rectangular area. Then specify the target location by typing the value or clicking the area-choosing button. In this example, the unique age can be generated from Column C and show in Column E. Don’t forget to choose Unique records only, then click OK. The unique value showing in column E can be the contrast of the original data in C, that’s the reason why it is recommended to copy to another location.

                    Generate Unique Value in Column

                      11. Input Restriction with Data Validation Function

                      In order to retain the validity of data, sometimes you need to restrict the input value and offer some tips for further steps. For example, age in this sheet should be whole numbers and all people participating in this survey should be between 18 and 60 years old. To ensure that data outside of this age range isn’t entered, go to Data->Data Validation->Setting, input the conditions and shift to Input Message to give prompts like, “Please input your age with whole number, which should range from 18 to 60.” Users will get this prompt when hanging the pointer in this area and get a warning message if the inputted information is unqualified.

                      Input Restriction with Data Validation Function

                        12. Fast Navigation with Ctrl + Arrow Button

                        When you click Ctrl + any arrow button on the keyboard, you can jump to the edge of the sheet in different directions. If you want to jump to the bottom line of the data, just try to click Ctrl + downward button.

                        Fast Navigation with Ctrl + Arrow Button

                          13. Transpose Data from a Row to a Column

                          You would use this feature if you want to transpose data to get a better display; however, retyping all data would be the last thing you would need to do if you know how to use the Transpose function in Paste. Here’s how: copy the area you want to transpose, move the pointer to another blank location. Go to Home->Paste->Transpose, please note that this function won’t activate until you copy the data first.

                          Advertising

                          Transpose Data from Row to Column

                            14. Hide Data Thoroughly

                            Almost all users know how to hide data by right clicking to select the Hide function, but this can be easily noticed if there is only a little bit of data. The best and easiest way to hide data thoroughly is to use the Format Cells function. Choose the area and go to Home->Font->Open Format Cells->Number Tab->Custom->Type ;;; -> Click OK, then all the values in the area will be invisible, and can only be found in the preview area next to the Function button.

                            Hide Data Thoroughly

                              15. Compose Text with &

                              Complicated formulation is unnecessary, as long as you know how to use &. You can compose any text freely with this symbol. Below I have four columns with different texts, but what if I want to compose them to one value in one cell? First, locate the cell that is to show the composed result, use the formulation with & as shown in the screenshot below. Click Enter: all texts in A2, B2, C2 and D2 will be composed together to become [email protected] in F2.

                              Compose Text with &

                                16. Transforming the Case of Text

                                With all the tricks shared here, I’ve tried my best to avoid complicated formulation. But there are still some simple and easy to use formulations to show you, like UPPER, LOWER and PROPER, which can transform texts for different purposes. UPPER will capitalize all characters, LOWER can change text to all lower case and PROPER will only capitalize the first character of a word.

                                Text Transform

                                  17. Input Values Starting with 0

                                  When an input value starts with zero, Excel will delete the zero by default. Rather than reset the Format Cells, this problem can be easily solved by adding a single quote mark ahead of the first zero, as shown.

                                  Advertising

                                  Input Value Starts with 0

                                    18. Speed up Inputting Complicated Terms with AutoCorrect

                                    If you need to repeat the same value and it is complicated to input, the best way is to use the AutoCorrect function, which will replace your text with the correct text. Take my name, Liza Brown, for example, which can be replaced by LZ. Therefore, every time I input LZ, it can autocorrect to Liza Brown. Go to File->Options->Proofing->AutoCorrect Options and input Replace text with correct text in the red rectangular area, as below.

                                    Speed up Inputting Complicate Terms with AutoCorrect

                                      19. One Click to Get More Status

                                      Most users know how to check the data status in the bottom of an Excel sheet, like Average and Sum Value. However, do you know you can move the pointer to the bottom tab and right click to get more status, as shown below?

                                      One Click to Get More Status

                                        20. Rename a Sheet Using Double Click

                                        There are multiple ways to rename sheets, and most users will right click to choose Rename, which actually wastes a lot of time. The best way is to just click twice, then you can rename it directly.

                                        Rename Sheet by Double Click

                                          Want to level up your excel skills further? Don’t miss this article:

                                          Excellent Excel Shortcuts That Very Few People Know

                                          More by this author

                                          20 Excel Tricks That Can Make Anyone An Excel Expert 24 Useful Tricks for the iPhone And iPad Most People Don’t Know 15 iPhone Alarms That Wake You Up Right Away 15 World’s Best Free Online Music Streaming Platforms 25 Hidden iOS 8 Tips & Tricks That You’ll Regret Not Knowing Now

                                          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