How to Improve your Microsoft Excel Skills?
Image source: pixabay.com
Introductory review of Microsoft Excel
Improve your Microsoft Excel Skills – Microsoft Excel is one of the powerful office tools from the stables of Microsoft Office. The first initial release of Microsoft Excel was made in 1985 as a spreadsheet arranged in numbered rows and lettered columns to organize data manipulations using arithmetic operations and formulae. Since, then Microsoft Excel has grown to become one of the most reliable and potent spreadsheet and report managing software. The current version of Microsoft Excel -2016 is a version of the Microsoft Office productivity suite of Microsoft Office Excel 2016 succeeding both Microsoft Office 2013 and Office for Mac-2011.
Using Microsoft Excel
For a beginner or novice it is important to hone certain microsoft excel skills for mastering Microsoft Excel to make the most out of this powerful software tool. To become a professional Microsoft Excel user, it is important to learn nine unique ways or tips that would help you in taking full advantage of the features of Microsoft Excel. The below mentioned ways would help you in honing microsoft excel skills.
Nine ways of improving Microsoft Excel Skills
Learning Microsoft Excel formulas
Arithmetic/logical formulas are the backbone of any Excel program as without formulae, Excel software is just like a huge grid where you can store data. Therefore, it is imperative on the part of the learner to hone his/her microsoft excel skills of using formulae such as Auto sum, IF and Else formulae, Count of values, Sum of values meeting a criteria, Average Formula, Mod Formula etc
Mastering the shortcuts
Using short cut tools available in Microsoft Excel would help you saving precious time in addition to finishing the work/project on time. These excel shortcuts not only boost productivity but also help you in learning Excel in a quick manner. Some of the important short cuts in Excel include using short cut keys such as–
- “Ctrl + Space” for selecting the whole column
- “Shift + Space” for selecting the whole row
- “Shift + Home” for selecting range from start cell to far left
- “Ctrl + /” for selecting array formulae range
- “F3” for displaying range names etc.
Using Pivot Table Tools
The Pivot Table Tool is one of the powerful features of MS Excel that are used to summarize, analyze, explore and in presenting the data. Learning and using pivot table tools helps you in presenting your data and in reporting in lesser time and as an attractive way of graphical presentation. By using the “Group” feature of Excel, you can easily create monthly, quarterly reports using pivot table tools.
The Pivot table tool allows you to select the column of data that you want to analyze and then choosing how to manipulate the data. It requires simply utilizing formulae tools like adding, averaging, calculating the standard deviation by using the Value Field Settings option of Excel. The introduction of Power Pivot table which is a key tool in Excel-2011 and Excel-2013 is remarkable as it can load even the largest data sets from any source and is able to process massive amount of data within seconds. Firms and corporate are utilizing the dashboards created from Power View – a complement to Power Pivot. For digital marketers Power Pivot along with GeoFlow is enchanting tools through which these marketers can sift and visualize big data with ease.
(Table showing PPC Acquisition performance by using Pivot Tool)
Table Formatting Tools
Knowing about table formatting is another key Microsoft Excel skill especially for marketers who are required to collect and interpret the marketing data. By learning Excel’s table formatting techniques, you can turn your data into an interactive database. The table format tools helps in creating own table styles quickly and in a convenient manner. The table format gallery of Excel in the form of “Format as Table” button on the Style group of the home tab provides you an interface element called gallery with a number of formatting choices for the table as shown below-
Excel users can then choose any one of the predetermined formats through a range of cells to convert into a table. If the table contains a heading row you should ensure that checkbox showing – My table has headers – is checked. After defining the table, the table area gains the special functionalities with number of filters to be selected. The other formatting tools that are embedded in Excel table include-
- Automatic expansion of table through which Excel has the feature of automatically increasing the size of the table according to the data entered into the table.
- Automatic reformatting – This feature enables the table to automatically adjust the formatting with alternate shading taking into effect.
- Automatic adjustment of charts and other objects – Through this feature, you can add rows to the table and any object that uses it and the concerned table data will automatically include the new data.
- Summarizing with Pivot Table – Another remarkable feature present in the table formatting tools include the automatic up date of Pivot Table. For instance, if you add data to the table, Excel would automatically expand the source range of the Pivot Table to reflect the concerned changes.
Making use of Auto fill
The other important feature that should be mastered is the “Autofill” option in Microsoft Excel that helps in filling out numbered data in columns and rows thus saving you hours of data entry. This Autofill option can be used for formulas without typing keystrokes. Learning Excel formulas that include using Auto fill for formulas makes the work simple. Majority of people using Excel are not aware of Auto fill feature that can save time of data entry. The method of using this feature involves typing data into the first cell that ends in a number and then clicking and holding the lower right hand corner of the cell and then dragging the mouse down the column. By doing this you can see that the number on the right will automatically increment for each cell.
Using Auto fill option for formulas can save a lot of time. For instance, when averaging all of the values in a column by using Average formulae can be extended to other columns in an Excel sheet by auto fill feature for doing the same calculation at the bottom of each other column.
The procedure involves clicking and holding the lower right hand corner of the cell where the data/number is just typed in the formula and then dragging it across the other columns to the right of it. On releasing the mouse, all of those column calculations will automatically be corrected for the right column letter without typing another keystroke in few minutes.
Aligning charts, shapes, objects on spreadsheets
Presentation of data in the form of graphs, charts, diagrams plays an important role in making the reports presentable. Therefore, the key skill lies in learning the fundamental design principles featured in Microsoft Excel in the form of “Align or distribute option”. Aligning charts for power point presentations require more professional look with some additional formatting tips. For instance, you can resize the charts to the same height (for instance 3 x 6 inches) by following these steps by using Excel as shown herein below-
- Press Ctrl key while selecting all charts
- Go to Format Tab | Object
- On the size tab, enter the number 3 for the height and 6 for the width
- Click Ok
By taking these steps, all the charts will now resize together even though these charts can be resized graphically by using the mouse. By clicking and dragging the corners of one chart, the entire group will resize accordingly. The other important tip for improving your microsoft excel skills is to know about creating/adding shapes to an Excel table. Shapes can be defined as the individual objects that can be added to a spreadsheet and edited if required.
The Smart Art Graphics tab is quite helpful in creating different shapes for a flow chart. There are varieties of shapes to choose from Smart Art Graphics tab like lines, arrows, geometric shapes according to the set dimensions. For a comprehensive menu of editing and styling options, you can click a shape or Smart Art Graphic tool.
Importing data from a website
To speed up the workflow it is necessary in certain cases to import data from other sources such as websites, other software such as MS-Word, Ms-Access or from any other source. In such cases, it is important for you to have microsoft excel skills of importing the data to the worksheet. Powerful Excel tools such as Power Query Ribbon are helpful in transporting the data from webpage to Excel worksheet. The command in Excel under the Data tab helps you in selecting Get External Data from Web that will in turn open the New Query Window where you can enter the URL of the site that you want to get data.
This New Web Query page contains a number of small yellow and black arrow markers scattered around the page. The yellow markers or yellow arrow icons next to the table data can be imported or added to the Excel. When clicking the areas of the data to be imported, the boxes turn into green check marks. Then by clicking on the import button, you can choose the cell where you want the data to appear in the spreadsheet. The data will then begin to be retrieved from the website depending on the amount of data that one has selected.
Using comments for specific cells
The other skill that needs to be polished by you is the knowledge about defining the content of specific cells in the worksheet or adding information that may be needed for others for making further corrections. Comments are usually notes that can be inserted into any cell of Excel which are useful for other viewers as reminders, notes or for cross-referencing other workbooks. Excel 2010 & 2013 versions have New Comment Tab. By clicking on this tab, a small dialog window appears where the user can begin typing the text. In addition, there would be small little red triangle at the top right of the cell that indicates that the cell has a comment. For using the comments options appropriately, one should also know the basic intricacies of document validation option of the Microsoft Excel.
Working with multiple worksheets
Last but not the least, the best way to improve your Microsoft Excel skills is in knowing about how to work with multiple worksheets or workbooks that have similar formatting needs. For maintaining the consistency of these worksheets, you as a user of Excel just need to be thorough with group and format techniques provided in the Excel for grouping worksheets together to enter, edit, format or print the data all at the same time. The grouping feature in Excel helps in selecting multiple worksheets and simultaneously adding or entering data on all grouped selections. The following steps would help you in working with multiple worksheets at a time.
- Click the first sheet tab of the group by holding the shift key.
- Click the last sheet of the tab you want to include in the group.
- In case, the worksheet is not adjacent to one another, it is required to hold the Ctrl key down and select the required sheet tabs.
- Once the worksheets had been grouped together, you can enter the titles and labels on one worksheet and then they will be entered in all grouped worksheets. The worksheets can also be formatted simultaneously while they are grouped together.
- Copying an existing worksheet forms part of working with multiple worksheets. For copying a worksheet, it is required to use the Edit, Move or Copy Sheet command and then selecting the new sheet location and then creating a Copy option will result in a copy of the selected sheet inserted in the selected location.
These nine ways of working with Excel will help you in improving your microsoft excel skills like a professional Excel user. However, for gaining expertise in Excel commands one should practice these microsoft excel skills in his/her day to day work schedule.
Here are some articles that will help you to improve your Microsoft Excel Skills so just go through the link.