Excel Secrets Everyone Should Know

Hidden Secrets In Microsoft Excel

This ebook from Francis Hayes gives you professional tools to help you get the most out of your Excel program. Any one of these secrets could be the only one that you ever need to know for Excel, but this ebook includes bunches of those tips and tricks! Just think of all of the useful information you can get from it! If you have ever been frustrated at your lack of progress in Microsoft Excel, this guide will teach you everything that you need to know to harness the powerful functions or time-saving elements of Microsoft Excel. Excel is used by offices all over the world, but so few people take the time to teach you anything important about it. Too much time is usually wasted searching the internet for tips on how to use it more efficiently Learn the best way to master Excel in this ebook!

101 Secrets of a Microsoft Excel Addict Summary


4.6 stars out of 11 votes

Contents: Ebook
Author: Francis Hayes
Price: $9.97

My 101 Secrets of a Microsoft Excel Addict Review

Highly Recommended

I've really worked on the chapters in this book and can only say that if you put in the time you will never revert back to your old methods.

Overall my first impression of this book is good. I think it was sincerely written and looks to be very helpful.

Download Now

Walkthrough Custom Exporting with Excel

ActiveReports provides custom components for several formats, including PDF, HTML, RTF, Excel, TIFF and plain text. Ultimate customizability is available by using any ASP.NET language. This walkthrough illustrates how to create a simple Web application and set up custom exporting in Excel format. Adding the Excel export control to the Web Form Adding code to the Web Form to export to Excel When you have completed this walkthrough, you will have a report in Excel format that looks similar to the following.

Soft Artisans Office Writer for Excel

SoftArtisans' (www. softartisans.com) OfficeWriter for Excel tool produces native Excel files (Office 97, 2000, XP, 2003, and 2007) without instantiating Excel. For use on high-request web server environments, OfficeWriter is a lean tool designed specifically to generate Excel and Word documents on a server. There are two versions of this product the Enterprise Edition and the Standard Edition. The Standard Edition preserves all of the Excel features in a template or existing spreadsheet. With limited exposure to the OfficeWriter application object in the Standard Edition, features such as cell merging and column and row autofitting aren't available at runtime. Listing 7-12. Creating a Spreadsheet Using SoftArtisans' OfficeWriter for Excel ExcelApplication oExcelApplication oExcelApplication new ExcelApplication() oWB oExcelApplication.Create() OfficeWriter instantiates an object of type ExcelApplication that is the equivalent of Excel.Application. Then, you can create a new workbook...

Building a Data Set Table from Excel

Place the following code behind the btnBuildDataSetFromExcel click event. This will access your Excel document as if it were a database and populate the DataSet. From that point, the client application has no idea where the data came from, only that is exists in a format it understands. You will need to change the path to the Excel document to the path of your Excel document. strConn Provider Microsoft.Jet.OLEDB.4.0 Data & Source C ContactInfo.xls & _ Extended Properties Excel 8.0

Microsoft Excel

Even when not working with data-driven applications, I've long believed that there is no better reporting tool than a well-written stored procedure and a copy of Excel. When used properly, a report writer is just a data-formatting tool. All the intelligence is (or at least should be, in my opinion) stored in the stored procedure, or business class, that feeds raw data to the reporting tool. The stored procedure should provide the detail data in the format it will appear in, minus any totals and subtotals. It should even provide calculated columns. If you need to show this year's and last year's expenses for a series of line items in your report and wish to display the percentage changes between these two data elements, the stored procedure can calculate this for you. As a rule, the more work that can be done in the stored procedure, the better. I like Microsoft Excel for two primary reasons. First, its cell-and-column format is perfect for reporting. Second, its nature as a...

Declaring multidimensional arrays

This tells the C compiler that you want to create an array with one dimension of 10 and another dimension of 25. You can think of a two-dimensional array as a Microsoft Excel spreadsheet with 10 rows and 25 columns. Table 3-3 shows how this array might look if its data were in a table.

Object Variables and Their Binding

In VB .NET, classes and their objects are everywhere. Of course, there are the classes and objects that we create in our own applications. There are also the classes in the .NET Framework Class Library. In addition, many applications take advantage of the objects that are exposed by other applications, such as ActiveX Data Objects (ADO), Microsoft Word, Excel, Access, various scripting applications, and more. The point is that for each object we want to manipulate, we will need to declare a variable of that class type. For instance, if we create a class named CPerson, then in order to instantiate a CPerson object, we must declare a variable

Discussion Identifying the Methods to Work with Application Processes

Consider a Windows application that retrieves data files from a File Transfer Protocol (FTP) server. These data files are Microsoft Excel spreadsheets, Microsoft Word documents, and Microsoft PowerPoint presentations. You want to display all the files to users in a list box so that the users can double-click the files they wish to open. You need to ensure that the program in which a file opens is the default program for that file type. The .NET Framework makes it easy to create this type of an application by allowing you to work with application processes. A process is an application in execution. You can use the Process class available in the .NET Framework to launch the application associated with a specific file type and display the file.

Documentbased User Interfaces

Rather than build a custom Windows-based desktop application to facilitate user interaction, you might find that it makes more sense in some circumstances to allow users to interact with the system through documents created in common productivity tools such as Microsoft Word or Microsoft Excel. Documents are a common metaphor for working with data. In some applications, you may benefit from having users enter or view data in document form in the tools they commonly use. Consider the following document-based solutions Reporting data. Your application (Windows- or Web-based) may provide the user with a feature that lets him or her see data in a document of the appropriate type for example, showing invoice data as a Word document, or a price list as an Excel spreadsheet. Gathering data. You could let sales representatives enter purchase information for telephone customers in Excel spreadsheets to create a customer order document, and then submit the document to your business process.

Preparing the Application

Preparing the development environment Necessary software that was required for FMStocks 2000, such as IIS 5.0 and SQL Server 2000, was installed according to the setup documentation for FMStocks 2000. Then FMStocks 2000 was installed. Visual Studio .NET 2003, the .NET Framework 1.1, and the Visual Basic 6.0 Upgrade Assessment Tool were also installed. The software needed for the assessment tool to run, such as Microsoft Excel 2003, was also installed.

Understanding the Effort Total Worksheet

A complete summary of the effort and cost estimations can be found on the Effort -Total worksheet in the Microsoft Excel workbook that is generated by the assessment tool. this workbook and each has an associated weight indicating the effort required to upgrade that particular aspect, the weights for some aspects are left at zero. This means that the assessment tool does not consider that all aspects are important to the estimation. The aspects that do have weights for effort and cost have values that may not be appropriate for your organization. Also, remember that every upgrade project is different, and although similar worksheets to this one have been used in real upgrade projects, there are always new things to consider or assumptions built into estimate worksheets that do not apply to every project. It is likely that you will have to make some adjustments before applying this estimation to your own projects. For this reason, the next paragraphs explain how the assessment tool...

Administrating Priorities

To change a process's priority, first open an instance of an application such as Microsoft Excel. Now launch the Task Manager and go to the Processes tab. Look at an instance of Excel running as a process. Right-click EXCEL.EXE in the list and choose Set Priority from the menu. As you can see, you can change the priority class as desired wish. It wouldn't make much sense to set the priority of Excel high, but the point is you can if you want to. Every process has a priority and the operating system isn't going to tell you what priorities you should and should not have. However, it will warn you that you might be about to do something with undesirable consequences, but the choice is still left up to you.

Utilizing Cell Oriented Grid Features

You have probably noticed that the DataGridView is much more focused at the cell level that itsDataGrid predecessor was. Part of the reason for this is that a frequent use of grids is where columns don't necessarily dictate the structure of the grid's content. Specifically, users want spreadsheet-like functionality that mimics the interaction model millions of people have become accustomed to with programs like Microsoft Excel and other spreadsheet applications.

Overview of Data Import and Export Tools

One of the more common tasks involved with administering a database is importing and exporting data. The necessity to import and export data can be spawned by a request from a user, an upgrade to a system, data consolidation, archiving, or testing new application technology. Just as numerous as the reasons to move the data are the number of native and heterogeneous formats in which the data reside. Sometimes the data can reside in another database, such as Microsoft Access or Oracle. Other times, the data source is not in a database and is encapsulated in a Microsoft Excel spreadsheet or a text file. Adding more complexity is the fact that the data can reside in various disparate locations a needed file could be located on a network share on the LAN while other needed information is on a database server in another city on the WAN. Issues are further complicated because often the data cannot be used as is and need to be transformed to be useful. Compounding the difficulty is the fact...

Adonet Design Decisions

an excellent commentary on the design decisions and architectural basis for ADO.NET. It was written in 2000, before ADO+ was renamed ADO.NET. So what happens to ADO and OLE DB They continue to exist, and to serve the role for which they were designed. OLE DB continues to be Microsoft's preferred interface for developing a robust, comprehensive interface to an arbitrary data store. We won't, for example, drop our OLE DB interface to SQLServer in favor of a Managed Provider there is just too much native functionality in the store that Manged Providers weren't intended to represent. Our ADO Managed Provider exposes the ADO+ Connection, Command, and DataReader interfaces directly on top of any OLE DB provider, as well as a DataSetCommand for loading OLE DB data into, and propagating changes out of, the DataSet. OLE DB providers continue to be the way to expose data to things like Microsoft SQLServer's Distributed Query Processor, or for plugging multi-dimensional data into Microsoft...

Walkthrough Exporting Output

Included with ActiveReports are several specialized export filters (HTML, PDF, RTF, Excel, TIFF and Text). With these export filters, reports easily can be made available to others in various file formats. This walkthrough illustrates how to export a report to PDF, HTML, RTF, Text, TIFF and Excel files. When you have completed this walkthrough, you will have created PDF, HTML, RTF, Text, TIFF and Excel files which can be found in the Bin Debug (C ) or Bin (VB.NET) subfolder of your project's folder.

Data Set Object Example

Build DataSet From Excel Now you need to build an Excel file. Copy the Excel file from the Ch9 Examples directory from the website for this book a http www.nostarch.com vsdotnet.htm to anywhere on your hard drive. Open the Excel file and modify it so that the data in the Excel file relates to the data in your AddressBook. The second column is the important one. You'll need to open up SQL Server Enterprise Manager and view the data of the AddressBook table to determine what value to store in this column. The Excel document will actually hold notes about contacts in your AddressBook. The Id column of the AddressBook table is related to the AddressBook_Id column of the Excel document. Notice that the AddressBook_Id column has 1 in Column B Cell 2 and also a 1 in Column B Cell 3. Both of these columns relate to the first record in the image of the AddressBook table on the left. Go ahead and look at your data and add notes to your Excel document.

Building Extensibility DSLs

You can use a DSL to extend an application. Consider the macro feature in Visual Studio or VBA in Excel. They're useful tools, and most big applications have something of that sort. Visual Studio has macros, Emacs has LISP, Office has VBA, many games use scripting for the game logic, and so on.

Data Transformation Services

DTS is a set of components used to import, export, and transform data and the tools that let you use the components. The source and destination data can be from a single data source or multiple data sources. The data formats can vary greatly, from an Excel spreadsheet to a comma-separated text file or a relational database. Because DTS is a series of COM components, SQL Server does not have to participate by being either the source or the destination data source.

Multiple document interfaces

In the original conception of this style, a single window acted as a container for other windows, where each contained window displayed a specific instance or view of a type of data. More recently, well-known MDI applications such as Microsoft Word and Excel have taken the approach of displaying all of their windows directly on the desktop, each within a separate application window, while still preserving an MDI look and feel from the menu bar and other parts of the interface. This relatively new style, the Multiple Single Document Interface, or MSDI, is consistent with the manner in which Web browsers have typically worked. While an MSDI interface can be created in Visual Studio.NET, it is not necessarily an easy task.

XML A Data Format for the Future

Applications are already exposing data as XML in a range of ways. For example, as we'll see in later chapters, Microsoft SQL Server 2000 includes features that allow us to extract data directly as XML documents, and update the source data using XML documents. Databases such as Oracle 8i and 9i are designed to manipulate XML directly, and the most recent office applications like Word and Excel will save their data in XML format either automatically or on demand.

Basic Web Architecture

Implementing the architecture, on the other hand, can be a lot harder. You can implement a Web architecture in many different ways, and each one of these implementations claims to be the best. I would argue that each has its weak and strong points, and in some situations, one implementation will excel over others, but in a different situation, another implementation may be better. At this point, there is no best implementation.

Building Windows Applications

When the Web first came along, there was a clear distinction between Windows applications and web applications. Windows applications ran on the desktop or a local-area network (LAN), and web applications ran on a distant server and were accessed by a browser. This distinction is now being blurred as Windows applications reach out to the Web for services. Many new applications consist of logic running on a client, a database server, and remote third-party computers located on the Web. Traditional desktop applications such as Excel or Outlook are now able to integrate data retrieved through web connections seamlessly, and web applications can distribute some of their processing to client-side components.

Planning Your Application

The first thing we will need to do, before we write a single line of code, is to determine what type of reports we want to deliver in our Web application and how they are going to be used. Are they listing or grouped reports Are they used to check data entry in a form before submitting it What will the reports look like Will users want to print the reports from their browser or export to another format such as PDF, RTF, or Excel All of these questions can help you gather the information you need to design your reports and get a handle on how they are going to be delivered.

Dynamic Crystal Reports

Viewer Crystall Report 2008 Net

If you have Crystal Reports 2008, you can also create a report using its SDK's object model. Just as in Word or Excel VBA, for example, anything you can do with Crystal Reports from the designer interface you can also accomplish programmatically via the SDK. Using the same interface shown in the previous section, you can build a report using the code shown in Listing 7-18.

Data Dynamics ActiveReports for NET

Run-time engine assembly file Designer assembly file Native functions assembly file Viewer assembly file Web assembly file Web designer assembly file Visual Studio .NET Integration Package HTML Export DLL PDF Export DLL TIFF Export DLL Excel Export DLL RTF Export DLL Text Export DLL

Syncfusions Essential XlsIO

Instantiate the spreadsheet creation engine oExcelEngine new ExcelEngine() Create a workbook oWorkBook oExcelEngine.Excel.Workbooks.Create() Reference the first worksheet oWS oWorkBook.Worksheets 0 oWS.PageSetup.Orientation ExcelPageOrientation.Portrait oWS.PageSetup.PaperSize ExcelPaperSize.PaperLetter You need to instantiate the spreadsheet-creation engine first and then reference the existing workbook and worksheet as you would in Excel. The PageSetup object encapsulates the same settings as its Excel counterpart, and Essential XlsIO provides its own enumerated values to refer to such properties as Orientation and PaperSize. Headers and footers are set as you expect. Finally, you set the workbook's summary and custom document properties. Listing 7-10 shows how the cells are filled with data from the data source. Display headers in bold, centered, with a yellow background oWS.Range sRow, 1, sRow, 2 .CellStyle.Color Color.Yellow oWS.Range sRow, 1, sRow, 2...

Reportviewer Rdl Headertype

The code in Listing 7-26 shows how to iterate the DataSet, create the individual columns headers, and then populate the columns with data. You see that this code produces a template to be executed by SSRS. Unlike the earlier Excel example, you're not populating every cell in every row with data.

Using the Assessment Tool

The assessment tool that accompanies this guide generates two Microsoft Excel 2003 files. This first file is named MainReport.xls and contains a high level look at the results of the assessment. When you first open the file, you will see a page like the one illustrated in Figure 3.1.

System CodeDom Compiler Namespace

Although a wizard can be a powerful tool, its fixed interface doesn't allow coverage of every validation possibility. This is where you can allow the user to enter .NET source code to check the entered data. Then, you have the unlimited power of source code available to you. You can call a stored procedure that performs extensive server-side data validation, or perhaps write a series of steps that can never be defined with a wizard. Of course, you only allow such a privilege to specially trained power users. Many bright end users, although not professional developers, can handle this with minimal training. Given that Basic is still taught in high school and college programming classes, and given that many people learn VBA to write Excel, Word, and Outlook macros, your users will likely be far more comfortable with VB syntax than with C . Given this situation, it's probably best to support VB.NET syntax in your custom validation screens. Even if the users have no programming...

Adding a Data Source to Your Report

Locate the type of datasource you would like to add to your report and expand the data source folder. Depending on what type of datasource you select, an additional dialog may open. For example, if you select ODBC (RDO), a second dialog will open and prompt you for an ODBC data source. Likewise, if you select Access Excel (DAO) under More Data Sources, a second dialog will open and allow you to select a spreadsheet or database to report from.

Enableusedummydata On Crystal Report

With datasources, 331 with existing reports, 329-330 when exporting reports, 332 in formulas, 253-255 in Report Designer, 330-331 in subreports, 331-332 in Web Forms Viewer, 333 in Windows Forms Viewer, 332 in XML Report Web Services, 333-334 events in Report Viewer, 113-114, 140-146, 188-191 examples. See samples tutorials Excel (Microsoft) as datasource, 33, 63, 213 exporting reports to, 136, 284 printing reports from, 188 exception handling, 146. See also troubleshooting Exception property, HandleException event, 146 ExceptionEventArgs class, 113 Exchange (Microsoft), exporting reports to public folders of, 284

Containing Forms Within a Parent Form

The previous section showed how to launch a new form that can be moved around the screen independently of the form from which it was launched, even though the lifetime of that window is tied to the one that created it. What if you want to create a form that will be fully contained within the client area of its parent window This style of user interface is called a multiple-document interface (MDI), even though what you contain in the child forms may not be document-oriented at all. The terminology is a holdover from the days when earlier versions of programs like Microsoft Word and Excel opened each document in a child window that was contained within the outer window, or frame, which represented the application itself. Most of those programs have migrated to being more document-centric instead of application-centric and now present a separate top-level window for each document that is open. However, the application style that they introduced is still around and may make sense for...

How To Use This Book

Indicates the use of Visual Basic for Applications (VBA) code such as tags or attributes, scripting language code such as statements, operators, or functions, and Excel Object Model code such as objects, methods, or properties. An Extra section provides additional information about the task you just accomplished. Extra sections often contain interesting tips and useful tricks to make working with Excel macros easier and more efficient.


This circular process of a content component's life cycle is where most errors are likely to be introduced if the repository does not have a CMS. It requires careful coordination between author and editor because each author and editor may be able to overwrite the work of the other. This coordination is where CMSs excel and why any decent-size Web site uses them.

The Last Word

A large part of this chapter has focused on a back-to-basics approach that stresses organization and logic instead of graphic anywhere it if looked like Microsoft Excel. The dividing line is usually drawn between productivity applications and entertainn market forskinnable word processors isn't nearly as large.

EImproved Usability

Another user interface enhancement is multi-tab windows (see Figure 4.6). Rather than seeing just the open window, as in Visual Basic 6.0, multiple tabs now appear across the top of the Visual Studio .NET window, similar to worksheets in Microsoft Excel. This is a convenient method to quickly be reminded of what windows are currently open. It also provides an easy way to navigate back and forth among several code or form windows we're working with.


DTS connections are based on the OLE DB architecture, allowing the use of numerous, readily available OLE DB-compliant providers. In addition to the vast array of OLDE DB drivers, DTS can use ODBC connections through the OLE DB provider for ODBC, and it can access flat files using the OLE DB flat-file provider. DTS has native OLE DB support for both Oracle and SQL Server because they are so widely used. Other providers exist for such data sources as a Microsoft Excel spreadsheets, Microsoft Access, and HTML.

Using MDI Forms

A second style is the MDI style, where a parent frame window can hold zero or more child windows. The Excel user interface, shown in Figure 9.5, is a good example. Figure 9.5 The Excel MDI user interface showing the frame and two child windows. Figure 9.5 The Excel MDI user interface showing the frame and two child windows.

Adonet Architecture

Microsoft ADO.NET's object model encompasses two distinct groups of classes content components and managed-provider components. The content components include the DataSet class and other supporting classes such as DataTable, DataRow, DataColumn, and DataRelation. These classes contain the actual content of a data exchange. The managed-provider components assist in data retrievals and updates. Developers can use the connection, command, and data reader objects to directly manipulate data. In more typical scenarios, developers use the DataAdapter class as the conduit to move data between the data store and the content components. The data can be actual rows from a database or any other form of data, such as an XML file or an Excel spreadsheet.

Database Files

As mentioned earlier, these native drivers (and the limitations that come with them) are not included with Crystal Reports .NET, apart from the direct drivers for Excel and Access. In order to create reports from these datasources, we have a number of options One type of native connection that is still supported is the direct connection to Microsoft Access databases and Excel spreadsheets. Both of these file types can be used as the datasource for your report without having to use ODBC.

Existing Data Format

DTS has a great deal more flexibility and not only can deal with SQL Server entities and text files but also with any data that can be accessed through OLE DB. This includes data sources such as Excel, Oracle, and any data source that supports the OLE DB provider for ODBC. In addition, SQL Server does not need to participate as it does with a BCP application, BulkCopy SQL-DMO, or the BULK INSERT statement.

The Observer Pattern

We expect this sort of behavior because there are any number of Windows applications, like Excel, where we see that behavior. Now there is nothing inherent in Windows to allow this activity and, as you may know, programming directly in Windows in C or C++ is pretty complicated. In VB, however, we can easily make use of the Observer Design Pattern to cause our program to behave in this way.

Ole Db

The real innovation behind OLE DB was Microsoft's strategy for Universal Data Access (UDA). The thinking behind UDA is that data is stored in many places - e-mails, Excel spreadsheets, web pages, and so on, as well as traditional databases - and that we should be able to access all this data programmatically, through a single unified data access technology. OLE DB is the base for Microsoft's implementation of this strategy. The number of OLE DB providers has been gradually rising to cover both relational database systems (even the opensource MySQL database now has an OLE DB provider), and non-relational data sources such as the Exchange 2000 Web Store, Project 2000 files, and IIS virtual directories. However, even before these providers became available, Microsoft ensured wide-ranging support for OLE DB by supplying an OLE DB provider for ODBC drivers, This meant that right from the start OLE DB could be used to access any data source that had an ODBC driver. As we shall see, this...

The Err object

A string that specifies the object that generated the error. When the error is generated within your application, the Source property is the project's name, which is more or less useless. (It would have been nice to get the name of the offending procedure.) However, when the error is generated by an external COM component, the Source property returns the programmatic ID of that component, which has the form application.objectname, as in Excel.Application, for example.

Auto Complete

Looking for a way to make text entry a little easier A common solution in Windows applications is AutoComplete input controls. These controls store recent entries and offer them when the user starts to type something similar. You'll see autocompletion at work when you type a URL into Internet Explorer's address bar or when you enter a file name in the Run dialog box (choose Run from the Start menu). Other applications use them for a variety of purposes, such as tracking recent help searches in Microsoft Word and tracking recent cell entries in Microsoft Excel. With this mode, the AutoComplete suggestion is automatically inserted into the control as the user types. For example, if you start by pressing the E key within a text box, the first item that starts with E appears in the control. However, the added portion is selected so that if the user continues to type, the new portion will be replaced. This is the autocompletion behavior used in Excel and older versions of Internet Explorer.

Binary Reader

Applications, such as Microsoft Word and Microsoft Excel, both monitor the files you are currently using in case external changes are made to the files. You are then presented with an option to reload the file so you can view all changes made to it. To explore the features of the FileSystemWatcher object, you build an application that monitors all text files on the root of the C drive and displays relevant information in the application window.

Application Domains

A process is, essentially, a running application. Each .NET application runs in its own process. If you have Word, Excel, and Visual Studio open, you have three processes running. If you open another copy of Word, another process starts up. Each process is subdivided into one or more application domains (or app domains). An app domain acts like a process but uses fewer resources.