Excel » Characteristics
Clear Excel files
Below are some topics that may be of importance in the development of high quality, user-friendly Excel files
Everything after the comma can be translated as a time. So does the number 41813.25 Jun 23, 2014 at 6:00 in the morning. (0.25 means that one quarter of the day has passed, 1/4 * 24 hours = 6 hours)
So, you can in Excel easily create all kinds of (over) hours of calculations and develop comprehensive planning programs.
Do not try too much data to process in one graph. That is the not improve readability. It is better to process the data into multiple graphs.
- Structured work with worksheets, columns and rows
- series names
- Formulas and Functions
- dates and times in Excel
- Conditional Formatting
- Data Validation
- Number Format
- page layout
- Sorting and filtering
- PivotTable
- Charts
- links
- Worksheet secure
- Share Workbook
- Visual Basic for Applications (VBA)
- Interface (menu, ribbon, buttons)
- Collaboration with other programs
Structured working with spreadsheets, rows and columns
Excel has three dimensions: rows, columns and sheets. To facilitate the use and management to keep it as simple as possible, it is important to efficiently use these 3 dimensions. So do not try to fit everything on one or more worksheets, but use multiple worksheets if the logic demands it. You can then later hide any auxiliary blades so that the user is thereby not taxed. The same applies for rows and columns. Divide complex formulas across columns and hide the relief columns. Avoid blank rows in tables.Series names
In Excel formulas and functions in standard use of cell addresses. Thus, for example, in cell A3, the formula is introduced to calculate the total cost: = A1 * A2. This formula can be replaced with the formula = Amount * Price. The benefits of using names instead of cell addresses are legion. Thus formulas easily readable, easier to manage, not $ signs are needed during copying and is possibly linked Visual Basic code more reliable.Formulas and functions
Particularly in the areas of math Excel is very powerful. At various disciplines many advanced features are available. In practice, you use only a fraction thereof. Some commonly used spreadsheet functions: sum, count, like, VLOOKUP, compare, SUMIF, COUNTIF, sums, like, COUNTIFS.. The correct function (formula) to be used in the right situation fit prevents functions are unnecessarily lengthy and complicated. Related Names Security.Dates and times in Excel
As many Excel users the background of a date and time in Excel does not know where the topic unnecessarily complicated or avoided certain calculations. A date in Excel is just a number, but formatted as a date. Thus, Jun 23, 2014, the number 41 813. This means that since 1 January 1900 41 813 days have elapsed. Jun 24, 2014, the number 41.814.Zo so you can easily calculate the difference between two dates. If you send an invoice on May 7, 2014 with a payment period of 60 days you can in Excel compute with a simple formula that the bill must be paid at least 6 Jul 2014.Everything after the comma can be translated as a time. So does the number 41813.25 Jun 23, 2014 at 6:00 in the morning. (0.25 means that one quarter of the day has passed, 1/4 * 24 hours = 6 hours)
So, you can in Excel easily create all kinds of (over) hours of calculations and develop comprehensive planning programs.
Conditional Formatting
conditional formatting to cells, depending on the content automatically be given a certain format. This format may consist of a background color, font color or an icon in the cell.Data Validation
In order to minimize error rates and create a user friendly entry field data validation can be applied. Thus, for example, can be set to a value must be between 1 and 10, between Jan 1 2014 and Dec 31, 2041, or that a choice must be made of the possibilities in a predefined list. An entry outside reach the set is not accepted.Number Format
Number Format we see in amount (€), dates (Mon Jan 27, 14) and percentages (%). Number format can also be used for eg "3.5 m3", "12 units", or "1,25 liters of". The use of such a number format has the advantage that there is no extra column to be used and that can still be simply with these cells counted.Page Layout
proper page settings can be achieved that the print data is printed neatly so without being unnecessarily distributed over several pages. Use headers and footers, page numbering, logos, file names etc ..Sort & Filter
If a table or list is set up nicely here can easily be sorted and filtered. Neatly in this case means that the list must be contiguous. The list may therefore contain no blank rows or completely completely empty columns. If this condition is met can be a table va this list and play it with one click sorted or filtered. So you can be sure not all will be placed together.DataPilot
See Sorting and filtering. A pivot table is a summary of a dynamic table. A PivotTable is nothing and is ready in a few seconds. Importantly, however, the underlying list is in order and that the user knows which summary data to be displayed. Do not try to handle everything in a turntable but make multiple pivot tables.Graphics
The basis for a good, clear chart in Excel is a neat, structured table. There are all kinds of exotic chart types available. In 'But generally it is best to choose a column, line or pie chart.Do not try too much data to process in one graph. That is the not improve readability. It is better to process the data into multiple graphs.
links
If a formula retrieves data from another (Excel) file creates a link. My advice is to avoid such links because incorrect values could indicate if the link to the other (Excel) file is broken. The latter occurs when the source (Excel) file is moved or if the files are emailed.
An alternative to using links in the processing of the source data in an integrated Excel spreadsheet in the target file or developing an import macro (VBA code).
Worksheet secure
To prevent users from destroying your Excel file and to avoid getting incorrect results workbook presents is advisable to protect your worksheets so that the input cells only be mutable. To make the distinction between secured and unsecured cells clearly is wise to the input cells (unsecured) to give a distinctive color (eg yellow).
Protect Workbook
Worksheets for which the users are not directly relevant are best hidden. This includes the Excel file bright, clear and uncluttered. To prevent the users hide sheets, display, delete, or add the workbook security can be enabled.
Share Workbook
Excel is essentially a single user program. This means that a specific Excel file by only one user can be modified simultaneously. In theory it is possible to share a workbook. For a simple workbook, which is used by only a few individuals, this is an acceptable method. If the workbook is more complex, or if 'many' people working simultaneously in the file, I advise you to use a shared workbook. Reasons for this are many features in Excel can not be used in a shared folder and as a shared workbook can be unstable. An alternative to shared workbooks is to use a database program (SQL, Oracle, Access) or working with Visual Basic procedures which ensure that the data from the various users are automatically synchronized in one mother Excel file.
Visual Basic for Applications (VBA)
Visual Basic for Applications (VBA) is the programming language that underlies Excel. Using VBA, you can perform all sorts of complex and repetitive tasks. Visual Basic macros also called, is a professional, comprehensive programming language. If you master this language, the possibilities are endless in Excel. It is important that language structured and well thought-out to apply so as to prevent 'monsters' of applications emerge that no one understands.
Interface (menu, ribbon, buttons)
If you have developed an Excel application to third parties, it is important that one can operate it in a user friendly way. One of the most important tools of the ribbon is thereby Excel. The ribbon is the dynamic menu structure at the top of the Excel window. This ribbon contains all the commands (menu items) that you can use to perform certain actions in Excel. In modern Excel versions (2007, 2010, 2013, 365) it is not possible to extend this ribbon using VBA with your own commands (macros), however this can be done via the XML (Extensible Markup Language) or through certain utilities .
The advantage of using the Ribbon is that it is user-friendly and that the necessary menu items are always displayed.
It is also possible to use spreadsheet buttons (buttons) and certain shortcuts to perform macro actions.
Collaboration with other programs
Using Visual Basic for Applications, it is possible to make Excel work with other programs such as Word Outlook or other mail programs. This allows you some calculation results or have charts from Excel to automatically process a report or report in Word, or you can automatically send data or files from Excel to addresses that are managed in Excel.
