Category Excel

Excel Visual Basic for Applications (VBA): How to Get Started

April 9, 2014 Excel  No comments

Microsoft Excel has a built-in Visual Basic tool called Visual Basic for Applications (VBA), which allows you to customize your workbook and save time on repetitive tasks. Instead of repeating the same thing over and over, you can write a short code to replace the repetitive task. Check out these tips for using VBA to make using Excel easier.

Using VBA in Excel

First, you will need to open the Visual Basic Editor (VBE), to do this you can either go to the “Developer” tab and click on “Visual Basics” or click “Alt” + “F11.” In the VBE, click on “Insert” and select “Module” to open a blank window. Now you need to decide if your program is a “Sub” (subroutine, a task that doesn’t return a result) or a “Function” (a task that returns a result)...

Read More
Excel Database Tips

April 2, 2014 Excel  No comments

An Excel database is a great way to organize your data. It includes a series of records in rows with fields of data entered in columns. Here are some tips to make using Excel as database easier.

Organizing Your Data

To use Excel as a database, your data has to be structured in a specific way. First, each column has to contain the same category of data in every row of that column. Similarly, each row must contain all of the data for the same one thing (person, group, organization, object, etc). To indicate this thing that the row represents, the first column of each row must contain a unique name. The top row must contain a unique column heading for each column to indicate which data is stored in that column...

Read More
Excel Tables: Easily Organize and Present Your Data

March 26, 2014 Excel  No comments

There are a variety of ways to sort and organize your data in Excel. These features allow you to quickly process data to get essential analytics, statistics, trends and other information to inform decision-making. One of feature is using Excel tables. Excel Tables are great way to quickly and easily organize and display your data. The following quick guide will provide you with tips, so you can start creating and using Tables for your data.

Creating a Table

To create a table, first highlight the data that you want included, then go to the “Insert” tab on the top toolbar and select “Table.” Your table will be created, and you will automatically be in the “Design” tab. Now, you can choose how you want your table to look by choosing from the different table styles...

Read More
Creating 3-D Formulas in Excels for Multiple Worksheets

March 19, 2014 Excel  No comments

A 3-D formula in Excel can be used to calculate data using multiple worksheets in a workbook. Check out these tips to learn how to create and use 3-D formulas in Excel.

What 3-D Formulas in Excel  Look Like

The syntax for a 3-D formula is “worksheetA:worksheetB!reference”

  • WorksheetA is the first worksheet that you want to be included in the calculation.
  • WorksheetB is the last worksheet that you want included in the calculation.
  • The reference is the cell or cells that contain the values that you want to be a part of the calculation.

Functions that You Can Use with 3-D Formulas

  • SUM: This calculates the sum of all the values you select
  • AVERAGE: This calculates the average of all the values you select
  • COUNT: This counts the number of cells in the range that contain numbers
  • MAX: This r...
Read More
Sorting Data with PivotTables in Excel

March 6, 2014 Excel  No comments

The PivotTable is one of the most useful features in Excel. Sorting data with PivotTables allows you to examine your data from multiple points of view. This convenient tool allows you to summarize your data. You can sort data, count totals and find averages. In addition to giving you the ability to organize your data, PivotTables also help in making your data more presentable. This brief overview from Desktop Training Academy gives you solutions for sorting data with PivotTables in Excel.

Creating a PivotTable

Once you have highlighted the data that you want in your PivotTable report, go to the “Insert” tab and click on “PivotTable.” You can then be able to choose to create the PivotTable report on a new page or keep it on the same page as your data...

Read More
Excel Conditional Formatting: Quick Guide for Easy Use

February 26, 2014 Excel  No comments

Using Excel in the workplace, or for personal needs, is made much simpler when you know who to use all of the tools and features effectively. This week, Desktop Training Academy brings you our tips for using Excel conditional formatting. The conditional formatting tool in Excel allows you to apply many different formatting options (such as background colors, borders, and font formatting) to data that meets certain conditions. This makes sorting and recognizing date easier

 In order to set a formatting condition, first select the cells that you want to format. Then on the “Home” tab, click “Conditional Formatting” in the “Style” section. See the image below for a reference point.

 

For the next step in the process, choose “New Rule...

Read More
Excel Visual Tools to Help You Save Time

February 19, 2014 Excel  No comments

Excel has many visual tools built in to help you effectively display your data. With features like themes, tables, and SmartArt, you can easily make your data viewer-friendly. Check out these tips to help you effectively use Excel Visual Tools.

Themes:

Themes help you make quick adjustments to the entire workbook for consistent colors and fonts. To choose a theme, click on the “Themes” button on the “Page Layout” tab. The themes you can choose from involve different color schemes and formatting that affect your font, cell background colors, and other colors throughout charts and diagrams. Identical themes exist in Work and PowerPoint, so it’s simple to standardize the appearance of different file types for the same project.

Form Controls:

These help you create interactive worksh...

Read More
Excel Macro Security: A 2007-2010 Comparison

February 5, 2014 Excel  No comments

Excel’s Trust Center, which causes security prompts, was introduced in Excel 2007, so when you are working with a workbook in Excel 2007 or Excel 2010, it runs automatically and handles macros in different ways depending on the settings you select. This post will go through the similarities and differences in excel macro security in Excel 2007 and Excel 2010.

Developer Tab

The first step you will need to perform in both 2007 and 2010 is to show the “Developer” tab. To do this, go to the Microsoft Office button in the top left corner and click on “Excel Options.” From there, in 2007, you will need to check the box that says “Show Developer Tab in the Ribbon...

Read More