Learnit Training
« Back to all themes

Training Excel

At Learnit Excel you can follow courses at all levels. From the basic course Excel to the Excel Expert course. In all versions, from Excel 2010 to Excel 2016 and Office 365. In addition, you can also use the Excel courses as e-learning Follow.

In order to determine which course is suitable for you, establish we have an online course guide available.

Link

Excel-Access 0:1

In the summer of 2015, there has been a large-scale research into the use of Excel worksheets in America. Many thousands of sheets have been viewed. And what turned out? Most of these worksheets do not contain any formula. This apparently means that Excel is mainly used to store data without being counted.

In practice I also come across many Excel users who create all sorts of lists or list in Excel. I understand that too, because the pane layout is useful and simple with filters the correct data can be selected. The problems occur when data from a worksheet needs to be linked to other worksheets. This is not possible with a few simple formulas and is often almost impossible. Although Excel has a number of database functions (DSUM, DCOUNT,..), they are again meant to be counted.

The solution that is not obvious, but often much better, is to import the data into MS Access. This is generally problem-free and Access creates tables. Establishing relationships between data is now easy. In the ' Relationships ' menu option, lines between fields from different tables can be drawn to create relationships. Various options can be used to ensure the integrity of the data. For example, only an order can be added if a customer has been created first. This data integrity is not present in Excel.

Access makes it easy to create forms (input screens) to make it even easier for users. Also to export is thought. Through reports, the information can be printed very flexibly to paper or PDF. It can also be easily exported to Excel. In Access, even most Excel functions can be used to create calculations.

It is always important to use the right tool when working. This also applies in the office. When an EXCEL worksheet does not contain formulas, it is usually better to choose Access.

Muhammad Nadeem

Office Trainer

Do you want personal guidance, feedback and practice with your own situations? Sign up for an Excel training or Access training You can follow our Office training in versions 2007, 2010, 2013 or 2016

Link

Pivot tables: Excel's best Feature

If a top 40 would exist of best functions in Excel, the pivot tables would be in the first place uncontested. As for me in any case. Shortly thereafter followed by the pivot charts. Why am I so excited about the pivot tables? I'll explain it to you.

When you want to calculate something in Excel, you have to indicate exactly what the purpose is. Count the contents of A2 with the contents of B2 and multiply it with the contents of C2. Excel counts this fine for you and shows the result. With more complex functions such as vertical search or nested functions, it becomes more difficult to select the correct values in the correct cells. The pivot tables do not require this.

For example, Excel automatically groups the data by location, region, department etc. Then, all kinds of functions such as Sum, Average, max, and Min can be used to display information by group. Consider the total sales by region or the average cost per department.

The starting point is the data table. This is a worksheet of data, divided into columns, where each column contains the same type of information. For example, names, phone numbers, and addresses are columns. There must be a name above each column. These names are used in the pivot table.

Select a single cell in the data table and choose the PivotTable function from the Insert tab. Confirm the next screen so that the pivot table is created on a new worksheet. Left is the space reserved for the report and on the right side the fields, the column names, can be placed in the appropriate boxes. A column name placed in the row labels allows for grouping of the data among each other. The column labels are placed next to each other. In The Values box, the numbers are placed and the desired function can be chosen for each field. This can be by clicking on the list arrow and selecting the desired function at the Values field settings.

That's all. You indicate where the data is and Excel gives you all the information you want. If you don't want to see the information as numbers but as a graph, perform the exact same procedure, but then select a PivotChart instead of a pivot table.

I'm sure when you're going to use pivot tables you'll agree with me that the topper within Excel are the pivot tables. Good luck with using it!

Muhammad Nadeem

Office Trainer

Do you want personal guidance, feedback and practice with your own situations? Sign up for our one-day training Excel pivot tables, formulas and functions. You can follow this training in versions 2007, 2010, 2013 or 2016

Link

20 Tips for Excel 2013-part III

Already can make good use of Excel 2013 after the tiplijst in our previous blog? We will gladly help you to increase the ease of use. That's why there are six tips below to help you get on with one of the most important computing and presentation programs in the workplace.

Tip 15: The fill handle

The fill handle allows you to quickly copy formulas and series. When you hover the cursor over an active cell at the bottom right, your cursor changes to a cross and you can copy it to the desired direction. In the figure below, the fill handle is shown in red. When you double-click instead of dragging, the formula/series is automatically copied. In the figure below this will be to cell C5.

TIP 16: Shortcut keys Worksheets

When you work with many worksheets in Excel, you can quickly navigate with the following keyboard shortcuts:

  • Worksheet to the right: Ctrl + PageDown
  • Worksheet to the left: Ctrl + PageUp

TIP 17: Shortcut keys workbooks

When navigating a lot between different windows, ALT + TAB is the solution. This allows you to switch quickly between the last two screens used. If you hold down ALT and press TAB repeatedly, you will switch through all windows. By releasing ALT + TAB, the selected window becomes active.

Tip 18: Make the cell absolute shortcut

As described in Tip 7, we can definitely make cell references. When we move the cursor in the formula bar to the appropriate cell, we make this cell absolutely with the F4 button. By pressing F4 repeatedly, you can switch between the following options: $A $1, A $1, $A 1 and A1. The next character after the $ character is absolute.

Tip 19: Keyboard Shortcuts for selections

Large files often want to select large ranges. This can be a tricky task with the mouse. When the CTRL + SHIFT + arrow buttons are pressed, you can quickly extend the selection by row and column. CTRL + SHIFT + * Selects all cells around it until Excel encounters an empty cell.

TIP 20: Excel 2013 Knowledge widening

A training Excel 2013Basic/continued/Expert at Learnit training helps you to broaden your knowledge. In our training you learn to work with Excel 2013 at your own pace. You will be accompanied by an experienced trainer. If you have already run into problems with Excel, it can be enlightening to take an example from your work environment to the training. You can get even more practice-oriented. Colleagues or employees who want to follow the training? We also give this training very tailor-made and (Incompany) for large groups.

You can click below to view the other tips:

Link

20 Tips for Excel 2013-part II

Already can make good use of Excel 2013 after the tiplijst in our previous blog? We are happy to help you increase the ease of use and therefore follow seven more tips for working with one of the most important computing and presentation programs in the workplace.

TIP 8: Macros

When you often have to do recurring work, a Macro is the solution. An example of such a kind of common job may be that every Monday you have to create a report of data from a database. The next time you do this, you're going to record a macro, with every mouse click and keyboard attack recorded. Then, the recorded actions can be performed with a single mouse click.

You do this as follows:

  • Go into the cell where you want to start recording the macro (eg. cell A1).
  • Choose View > Macros > Macro recording.
  • In the Record Macro window, enter a macro name and keyboard shortcut.
  • Use an orange fill color for the cell A1.
  • Stop the macro by clicking the Stop button at the bottom left of the screen.

Now you have a macro recorded and you can make any cell, using the hotkey, orange. Of course, you can make the macro much longer and it is also possible to have it referenced relatively.

Tip 9: Vertical Search

Vertical search looks for a value in an array that returns a value from the same row. This is often used to look up associated data from a large database. The function we use is called VERT. SEARCH and we can query through formulas > Search and refer > VERT. Search.

Syntax: Trans. SEARCH (search value; table matrix; Kolomindex_getal; approximate)
Lookup_value– Is the value that we want to search for in the matrix table (cell A9).
Table_array: Is the range of cells in which to search (blue lines).
Kolomindex_getal– Is The column number of the table array to be returned (red dotted lines).

Access: Choice of
FALSE: Exact as Search value
WHERE: Looks like search value
Syntax: TRANS. SEARCH (A19; A2: C6; 3; WHERE

If we enter the syntax above in cell B9, let's search for "Week 2" in the Matrix table. When this is found the result of column 3 is displayed. In the figure below is that 95.

Tip 10: Autosomal

To quickly create a SUM function, the button with the sum character (Home tab) exists. This allows us to quickly add a few cells with just a few clicks.

  • Enter the cell where you want the answer to occur.
  • Click the button with the sum character
  • Use the mouse to select the cells to be added.
  • Exit with ENTER.

When the arrow next to the Somteken is clicked, the following function choices appear: Average, number of numbers, Max and Min.

Tip 11: Tricky formulas

If you need to create tricky formulas, it is useful to fill them in the ' Functieargumenten' ' screen. This way, you increase the chance to make the formula correct, because special characters are automatically filled in. There is also a short explanation for each argument. This screen can be called using the ƒx button (to the left of the formula bar) when the cell is active.

Tip 12: Select Special

When data needs to be selected for editing, you can use ' Select Special '. Here you can think of empty cells, comments, formulas and differences in rows or columns.

  • Select the area to search for.
  • Press F5.
  • In the Select Special window, specify where to select.

Tip 13: Merge data "text"

For example, if you want to create new usernames using FirstName, last name and year of birth, this is possible with text formulas such as RIGHT, MIDDLE, and LEFT. In the figure below we see in the top row the data of Pieter, in row 2 The function that is used and underneath, in row 3, we see the result.

The Syntax for LINKS is: = LEFT (D1; 2). Or: From the text in cell D1 we want to return the left two characters. In cell E4, all data from Row 3 is joined together as a new user name. The formula for this is shown at the top of the picture. The "" Returns a space between Pian and 75.

Tip 14: Remove Duplicate values

If your data has been contaminated with duplicate values, it can be corrected in a few steps. We do this on the basis of a conditional format, as shown in the figure below.

  • Select the area where the duplicate values occur.
  • Choose Start > Conditional formatting > Cell marker Rules > duplicate values.
  • In the Double Values window, specify a format.

Now that the conditional formatting has been created, we can select and delete the rows by using a filter (by color). Note: Click the rows with Ctrl (control), or you will delete intermediate rows as well!

And that was the second part of this tip list around using Excel 2013. You can click below to view the other tips:

If you want to learn more about Excel, take a look at our Excel Training courses (also to be followed via e-learning).

Link

20 Tips for Excel 2013-part I

Microsoft Excel has been one of the most important calculation and presentation programs for the workplace for many years. Excel is a program that is used to process data and present this data clearly. Excel 2013 is very similar to its predecessor 2010 in terms of interface, but in terms of style and design the software has moved towards the so-called 'metro interface' of Windows 8. In addition, this version comes with a large number of templates that immediately assist the user with the creation of specific tables (reports, calendars, budgets, etc.). And finally, it is easier than ever to share created work online and work together on certain documents at the same time.

In this tiplijst (part I) We'll discuss some of these new features, so you can get even more out of Microsoft Excel.

Tip 1: Quick Completion

Excel has now become even smarter in recognizing patterns. If we follow the example in the screenshot, we see that in cell B2 when you type the FirstName from cell A2, a supplementary list is shown. By pressing ENTER, we can accept this list.

  • This also works inverted (in column B, type the last name)
  • Add a character (type in column B: FirstName? Surname
  • Change data (type in column B: Last name)

TIP 2: Quick analysis

Data is analyzed faster in Excel 2013. Make a selection of the data you want to analyze. The Quick Analysis button appears in the lower right. This button creates quick analyses, see the figure below.

In This example, a ' Sum-analysis ' is chosen as a result. Analysis can include: formatting, charts, totals, tables, and Sparklines.

TIP 3: Recommended graphs

The chart recommendation creates several recommendations based on the selected data.

  1. Select the cells that you want to display in a chart.
  2. Choose Insert > recommended graphs
  3. In the Insert Chart window, click the chart that you want, and then click OK.

Now that the graph is created, there are three buttons visible. The filter button now also allows you to quickly filter the data to be included in the chart.

TIP 4: Recommended pivot Tables

Pivot tables allow you to analyze a large number of data-this helps to make better decisions. As a beginner, it can be difficult to get to the point when there are many data points. Excel helps you to automatically generate pivot tables and works in the same way as recommended graphs (Tip 3).

  1. Select the cells you want to create a pivot table for.
  2. Choose Insert > recommended pivot tables.
  3. In the recommended Pivot Tables window, click the PivotTable you want.

The PivotTable Wizard is still usable if you want to customize the PivotTable. To view it, click the PivotTable that appears on the right side of your window.

TIP 5: Pivot table timeline

For pivot tables, a new function has been inserted to create a time filter. This works roughly the same way you might be used to using slicers. Please Note: A column of dates must be present in the source data!

Inserting the timeline:

  1. Click the PivotTable to display the pivot table tools.
  2. Choose Analyze > insert timeline.
  3. In the Insert Timelines window, select the date fields that you want, and then click OK.

Now that the timeline is inserted we can adjust it in the desired unit of measurement. We Use the timeline to select a period.

Tip 6: Templates

When formatting is not your strongest side, you can use preset templates. Excel has a large number of templates for budgets, invoices, calendars, lists, loans, and schedules. By using these templates you can focus on the calculation section and Excel provides very nice spreadsheets.

  1. Choose File > New.
  2. Under suggested searches, click one of the suggestions, or type a suggestion in the search bar.

Tip 7: Absolute and Relative cell reference

We use this when formulas are copied to multiple cells. Relative cell reference lets the cells in the formula change with it. In the figure below, the formula of cell D3 can be copied down to calculate the totals of the weeks. The formula for cell D4 then becomes: = B4 + C4

Now it is also possible to set a cell (absolute) when copying a formula. In the figure below we want to continue to use the 15% bonus (cell C1) If we copy the formula from cell C4 down. The formula of C4 then becomes: = B4 * $C $1. The $ character indicates that the character is behind it. In this case the C and the 1 are fixed. When we copy the formula from C4, we get the result in cell C5: = B5 * $C $1. In cell C6: = B6 * $C $1.

So, those were the first seven tips around using Excel 2013. You can click below to view the other tips:

If you want to learn more about Excel, take a look at our Excel Training courses (also to be followed via e-learning).

Link

The 8 most convenient Office 2016 features

Microsoft Office, for many known as Word, Excel, PowerPoint, Access and Outlook, has been completely revamped for the 2016 edition. As in every new version, a lot of changes have been made, and we like to put the 8 most convenient of these on a row.

1. Real-time co-authorship

Collaborating efficiently is one of the main objectives of Microsoft Office 2016. It will not surprise you that from this version you can work with different people simultaneously on the same document. While you are already reading the first paragraph in your Word document, your colleague can continue to write to the following paragraphs, without your work being overwritten by the other. The same goes for PowerPoint, but Word has a unique perk: Here you can even see each other in real-time typing.

2. Send larger files

Send your PowerPoint presentation of 300mb by mail? This is no longer a problem in the new Outlook. As soon as you add an Office 2016 product as an attachment to an email, it will be converted into a link where the recipient can easily download the file. The only condition for this is that the file is already synchronized with a OneDrive account or a SharePoint folder.

3. Smart Lookup

The Smart Lookup function ensures that you don't have to leave Word, Excel or PowerPoint when you want to look up the meaning of a word in your document. Simply select the word in question, right click and then select the option "Smart Lookup". The results you receive are generated by Bing and will be displayed in an "Insights" tab within your document.

4. One-click Forecasting

Excel-Ninjas will undoubtedly count this feature up to their favorites. In the chart section of Excel, you can now conjure up predictions with a simple mouse click. To this end, Microsoft uses the standard "Exponential Smoothing algorithm (ETS) to show effective reliable forecasts.

5. Skype Integration

As mentioned earlier, working together efficiently is very important in Office 2016 and that also proves the Skype integration in the various desktop apps. This allows you to start a Skype call within Word, Excel or PowerPoint, both with Instant messaging, voice calls and video calls. But you can also without leaving the document, share your screen via Skype.

6. Office groups in Outlook

This is perhaps the least discussed novelty of Office 2016, but therefore certainly not the least useful. Thanks to Office groups, it has been done once and for all with traditional distribution lists or cluttered mail conversations. You can now bring together the colleagues you collaborate with in one group, which has a shared Inbox, Calendar, File store, and even integrated OneNote notes.

7. New graphs

For those who are looking forward to the standard charts in Microsoft Office, there is good news. In fact, new chart types have been added that help you to visualize financial or hierarchical data better.

8. Better cross-device support

Finally Office 2016 also puts a big step forward in cross-device support of various programs. Create your Word document on your smartphone, update it on your tablet and add the finishing touch to your PC, all can be done easier than before.

Link

Filters in Excel

Many of our students use Excel to make large amounts of data transparent. With Excel, you can arrange and present large amounts of data in different ways. You can fill graphs with it, summarize the data in pivot tables, etc.

One of the way to search in large amounts of data is by using filters. Using filters, you can quickly hide (or show) some of the rules so that you only see the rows that are relevant to you at that time. How that works we explain below. If you want to work with filter you have to turn on this first. You do this on the Data tab by clicking the Filter option, also see the image below.

The filter buttons (gray squares with a black triangle) are added to each cell in the selected row (in older Excel versions, it is in the upper cell of a block of data). When you click on the filter you will see the window shown. Depending on what is in the selected column, you will see other data.

Clicking or selecting checkboxes can make a selection of the rows to be displayed.

When a column contains numbers, this window also gives you the option to make a selection based on certain values. Then choose Number Filters and enter the desired range.

When a column contains text, a similar filter appears that allows a selection on specific text. A column that allows a date can be selected in the same way at a specific time period.

By using a filter you hide the unselected rows, these rows are not deleted or erased. In The line numbers you will still see the original numbers. The advantage of this is that you can show the hidden data here later. However, if you want to copy the filtered data, this may be a problem. Copying also copies the unshowed lines so that the filter does not affect what you copy.

Fortunately, in Excel there is a rather hidden option to copy only the displayed (filtered) rows. This option is located on the Home tab in the find and select group. Choose the go to option and choose Specialin that window. The dropdown window then allows you to select the only visible cells option . Now when you copy the selected area to a new worksheet, the rows get new consecutive line numbers and the unselected rows are not copied with them.

If you want to practice with filters once, you can use this Excel file .