Drop down lists take a range of cells as input and show it as a list. There is not much you can do with how your list looks in the drop down. For example, you can not give a background color or change font color, font type, font size, etc.
Sometimes you may want to lock cells in Excel so that other people can’t make changes to it. It could be to avoid tampering of critical data or prevent people from making changes in the formulas.
This two-step process enables you to import Access web app data into Excel, where you can filter, graph, and analyze it to produce meaningful reports. In this article, I'll show you how to import Access web app data into an Excel workbook where you can then filter, graph, analyze, report, and more. Importing is an easy two-step process, but it's not particularly intuitive. You must first enable a connection for the database. Then, you create the connection in Excel, which allows you to import the data. If you skip the first step, the subsequent import will fail.
There is no one size that fits all. When it comes to Excel, the default settings work fine in most of the cases. But if need be, you can easily customize many of the Excel Options. In this blog post, I will show you how to use Excel Options to customize your workbooks and get a better spreadsheet experience.
Tracking all the steps of a project is an important task. It’s as important as the execution of the each step. In fact, tracking makes execution easy. And, a milestone chart is a perfect way to track a project in a simple way.
In this tutorial we will learn how to work with the Excel’s COUNTIF formula. The Excel COUNTIF formula counts the number of cells inside a range that match the given criteria. The rule of formula; COUNTIF(first as range, second as criteria). In this example we can use COUNTIF to return the number of occurrences for Tom.
In this tutorial, I will show you how to create: A Simple Pareto Chart in Excel. A Dynamic Pareto Chart in Excel. Creating a Pareto Chart in Excel is very easy. All the trickery is hidden in how you arrange the data.
If you’re building an Excel workbook for other people to use, you can add form control buttons, so it’s easy for them to run macros. One of my sample files has Navigation Buttons on each sheet, so you can quickly go to the previous or next sheet. There are a few form control button quirks though – maybe you’ve run into some of them. Today, we’ll take a look at one of the button name quirks – a hidden name that Excel fiercely protects.
If you work with currency/sales data, it comes in handy to know how to insert dollar and cent symbols with the numerical/sales values in Excel. While the dollar sign is right there on the keyboard, cent symbol is a little more difficult to find and insert.
A one-dimensional array interacting with a spreadsheet–in its basic form–will either collect or print data down a column or across a row. What if a need arises to collect or print data in both down and across? Or maybe you want an array to create a drop-down list within each cell of a range? Multi-dimensional arrays are essential to accomplishing this.
In this tutorial we will look at the tool Excel offers called Selection Pane, that you can use to manage the objects in your worksheet.
SUMPRODUCT is one of the powerful excel functions. And, today, I will show you one of its amazing powers. You will learn how can you write a conditional sumproduct function. Yesterday, I got a mail from one of my subscribers. She wanted to create a conditional statement with sumproduct and IF to get data from a table.
I’ll start with a simple routine that opens a CSV file, saves it as an Excel workbook, then plots all the data in the file, letting Excel use whatever defaults it sees fit. Then I’ll show a few variations, to help you figure out how to work with your own special data. Here is a zipped Excel workbook that contains all of this code, with buttons on the worksheet to run each routine. The zip file also contains a folder with a number of dummy CSV files.
Reworking an old demo database, I came across some code to generate a random date between two supplied dates and thought it could be of use to other.
While reworking an old demo of mine, I came across a function I had created to generate a Random Number between t2o supplied numbers and thought it could help other, so here it is.
Auditing and troubleshooting formulas in Microsoft® Excel® can be time consuming and difficult. That is why the FORMULATEXT function is helpful because it simplifies formulae error checking and correction. The formula text can easily be extracted without having to copy and paste. The FORMULATEXT function syntax has one required input—a reference to a cell or range of cells. The reference can be to another worksheet or open workbook.
Using Excel Conditional Formatting to highlight matches is easy when you team it up with a data validation list like this: Before we can set up the Conditional Formatting we need to set up the data validation list. So, over in column K I have the list of Salesperson’s names.
Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence – today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function. We will start with the basics and then move on to more elaborate examples and uses of the VBA Evaluate function.
With the help of Excel Sorting Data Tips we looking for solutions for very interesting problems today. How to sort dynamically a list containing two columns (‘Product’ and ‘Sales’ columns) at will only using excel functions?
In this tutorial, I show you one way to programmatically enable trust access to the VBA project object model using a macro. Enabling trust access to the VBA project object model allows you to write macros that can add new macros to a workbook. You’ll be able to add modules, write new macros and run these macros from your own macro.