Connecting Technology and Business.

Dependent Dropdown list in Excel

​Supposing I have a table of four columns that indicate four types of foodstuff: fruit, food, meat and drink and below them are the specific food name. See the following screenshot:


Now I need to create one drop down list that contains the foodstuff, such as fruit, food, meat and drink and the second dropdown would have the specific food name. If I select food, the second drop down will show rice, noodle, bread and cake.

To do this, please apply the following steps:

1. First, I need to create some range names for these columns and the first categories row.

(1.) Create a range name for the categories, the first row, select the A1:D1, and type the range name Foodstuff into the Name Box, then press Enter key.


(2.) Then you need to name the range for each of the columns as above step as shown below:

DDL3.png2. Now I can create the first drop down list, please select a blank cell or a column that you want to apply this drop down list, and then click Data > Data Validation > Data Validation, see screenshot:


3. In the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =Foodstuff into the Source box. See screenshot:


Note: You need type in the formula what you named your categories.

4. Click OK and my first drop down list have been created, then select the cell and drag the fill handle to the cell that you want to apply this option.


5. Then I can create the second drop down list, select one blank cell, and click Data > Data Validation > Data Validation again, in the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =indirect(F1) into the Source box, see screenshot:


Note: F1 indicates the cell location for the first drop down list I have created, you can change it as your need.

6. Then click OK, and drag the cell content downwards, and the dependent drop down list have been created successfully. See screenshot:


And then if I choose one type of the foodstuff, the corresponding cell will only display its specific food name.


1. The drop-down arrow is visible only when the cell is active.

2. You can continue going deeper as you like, if you want to create the third drop down list, just use the second dropdown as the Source of the third dropdown.


Future big data analysts will know everything you did today

Debates are raging about whether big data still holds the promise that was expected or whether it was just a big bust. The failure of the much-hyped Google Flu Trends to accurately predict peak flu levels since August 2011 has heightened the concerns.

In my mind, there is no doubt that data analytics will one day help to improve health care and crime detection, design better products, and improve traffic patterns and agricultural yields. My concern is about how we will one day use all the data we are gathering – and the skeletons it will uncover. Think about how DNA technology is being used to free people who were wrongfully imprisoned decades ago. Imagine what supercomputers of the future will be able to do with the data that present-day data gatherers haven't yet learned to use. 

Over the centuries, we gathered data on things such as climate, demographics, and business and government transactions. Our farmers kept track of the weather so that they would know when to grow their crops; we had land records so that we could own property; and we developed phone books so that we could find people. About 15 years ago we started creating Web pages on the Internet. Interested parties started collecting data about what news we read, where we shopped, what sites we surfed, what music we listened to, what movies we watched, and where we traveled to. With the advent of LinkedIn, MySpace, Facebook, Twitter and many other social-media tools, we began to volunteer private information about our work history and social and business contacts and what we like – our food, entertainment, even our sexual preferences and spiritual values. 

Today, data are accumulating at exponentially increasing rates. There are more than 100 hours of video uploaded to YouTube every minute, and even more video is being collected worldwide through the surveillance cameras that you see everywhere. Mobile-phone apps are keeping track of our every movement: everywhere we go; how fast we move; what time we wake. Soon, devices that we wear or that are built into our smartphones will monitor our body's functioning; our sequenced DNA will reveal the software recipe for our physical body. 

The NSA has been mining our phone metadata and occasionally listening in; marketers are correlating information about our gender, age, education, location, and socioeconomic status and using this to sell more to us; and politicians are fine-tuning their campaigns.

This is baby stuff compared to what lies ahead. The available tools for analyzing data are still crude; there are very few good data scientists; and companies such as Google still haven't figured out what is the best data to analyze. This will surely change rapidly as artificial-intelligence technologies evolve and computers become more powerful and connected. We will be able to analyze all data we have collected from the beginning of time – as if we were entering a data time machine. 

We will be revisiting crime cases from the past, re-auditing tax returns, tracking down corruption, and learning who were the real heroes and villains. An artificially intelligent cybercop scanning all the camera data that were gathered, as well as phone records, e-mails, bank-account and credit-card data, and medical data on everyone in a city or a country, will instantly solve a crime better than Sherlock Holmes could. Our grandchildren will know of the sins we committed; Junior may wonder why grandpa was unfaithful to grandma. 

What is scary is that we will lose our privacy, opening the door to new types of crime and fraud. Governments and employers will gain more control over us, and have corporations reap greater profits from the information that we innocently handed over to them. More data and more computing will mean more money and power. Look at the advantage that bankers on Wall Street have already gained with high-frequency trading and how they are skimming billions of dollars from our financial system. 

We surely need stronger laws and technology protections. And we need to be aware of the perils. We must also realize that with our misdeeds, there will be nowhere to hide – not even in our past. There are many opportunities in this new age of data. 

Consider what becomes possible if we correlate information about a person's genome, lifestyle habits, and location with their medical history and the medications they take. We could understand the true effectiveness of drugs and their side effects.  This would change the way drugs are tested and prescribed. And then, when genome data become available for hundreds of millions of people, we could discover the links between disease and DNA to prescribe personalized medications –tailored to an individual's DNA. We are talking about a revolution in health and medicine. 

In schools, classes are usually so large that the teacher does not get to know the student – particularly the child's other classes, habits, and development through the years. What if a digital tutor could keep track of a child's progress and learn his or her likes and dislikes, teaching-style preferences, and intellectual strengths and weaknesses? Using data gathered by digital learning devices, test scores, attendance, and habits, the teacher could be informed of which students to focus on, what to emphasize, and how best to teach an individual child. This could change the education system itself.

Combine the data that are available on a person's shopping habits with knowledge of their social preferences, health, and location. We could have shopping assistants and personal designers creating new products including clothing that are 3D-printed or custom-manufactured for the individual. An artificial intelligence based digital assistant could anticipate what a person wants to wear or to eat and have it ready for them. 

All of these scenarios will become possible, as will thousands of other applications of data in agriculture, manufacturing, transportation, and other fields. The only question is how fast we will get there – and what new nightmares we will create. 


Future big data analysts will know everything you did today

About Author

Vivek Wadhwa is a fellow at the Rock Center for Corporate Governance at Stanford University, director of research at the Center for Entrepreneurship and Research Commercialization at Duke's engineering school and distinguished scholar at Singularity and Emory universities. His past appointments include Harvard Law School and University of California Berkeley.

Office Web Apps is Office Online - Now With New Features Added

New features allow user to move as seamlessly as possible between Office Online and the desktop.

Excel Online

In Excel Online, users can now insert new comments as well as edit and delete existing comments.

Power users will appreciate that Excel Online now has improved support for files that contain VBA. One can now open and edit VBA-enabled spreadsheets without removing (or corrupting) the VBA contained in the file.

The "Tell Me" feature allows the user to ask Excel how to do something, and presents the relevant commands in a simple drop-down list so one can perform their action right away.

Word Online

Reviewing and providing feedback on documents is a key requirement for users of Office Online. The ability to add and view comments in the View mode was already available. And this is being extended to editing mode, so one can make comments and changes at the same time as others.

Those using Word Online for the creation of research papers and reports can now simply add footnotes and endnotes inline with the text.

Word Online is now smarter when it comes to list making, too. For example, when one is making a list, if they are directly below an existing numbered list and if they start typing, their next line automatically becomes part of the list—just like in desktop Word.

PowerPoint Online

A re-engineered text editor shows a high fidelity view of slides at the time of creation so that the layout of the slides looks more like the final result.

Some performance and video playback optimizations have been made by speeding up advancement of slides in the editor and by adding the ability to play back embedded YouTube videos.

OneNote Online

A One click printing has been added for easy printing of pages.

Multi-column section or page navigation has been added, so that users can maximize reading and editing space regardless of what device or browser they using.

Internet Explorer 11 knows what device you are holding in your hand now!

On April 3rd, Microsoft announced an updated version of Internet Explorer 11 available with the Windows 8.1 Update and for Windows 7 customers as well as the debut of Internet Explorer 11 for Windows Phone 8.1. The automatic updates will bring this to the devices right away.

People browse the Web on many devices, switching between their laptop, tablet, and phone throughout the day. This update to IE11 means that whatever device one picks up, the tabs and favourites are there for them, right where they left off. Windows 8.1 and Windows Phone 8.1 share the same experience and underlying Web standards. This helps developers build interoperable sites and apps that just work across many browsers and devices. (That includes updates to the latest specs for hardware-accelerated (and stable) WebGL as well as professional quality rich media like HTML5 video including closed captioning, adaptive streaming, and rights management).

Introducing IE11 for Windows Phone 8.1

With Windows Phone 8.1 users now have access to the most modern and full-featured phone browser ever with IE11. In addition to the same underlying engine across device experiences, and roaming favourites and tabs between devices, Windows Phone now offers many of the same features as the modern IE users experience on Windows. With IE11 for Windows Phone and Live Sites users can now pin thier favourite sites directly to their Start Screen for information from the Web at a glance. Reading Mode helps users optimize the view of articles like they were reading a digital book on the phone. Voice Commands get users quickly to their favourite Web sites by speaking naturally to their device. IE11 for Windows Phone also includes InPrivate to keep users' browsing private. And to help users better manage their phone's data usage, the new High Savings Mode can be enabled to reduce data consumption by 60-80% through reducing image downloads and only loading elements of the page relevant to the content you want to view.

Updated IE11 for Windows 8.1

With this update, IE11 adapts users' browsing experience by detecting their Windows device and input type – whether an 8 inch tablet in portrait mode or a 24 inch desktop with mouse and keyboard. The Web is still front-and-center but new design enhancements make their browsing experience feel like it was made just for their device – like the number of tabs on-screen and the size of the fonts and menus. They can also now control when the browser remains on-screen or hides away for full-screen browsing depending on the type of device one uses.

Enterprise Mode helps organizations progress toward modern browsers

Available for both Windows 8.1 and Windows 7, Enterprise Mode for Internet Explorer 11 provides improved Internet Explorer 8 compatibility for specified sites. Enabling enterprises to safely update to Internet Explorer 11 while maintaining great backwards compatibility for specific sites that were developed for Internet Explorer 8 or below. Organizations can designate that Enterprise Mode be enabled for specific sites so they can benefit from modern Web standards, better performance, and increased security of a modern Web browser, while reducing upgrade costs and extending legacy Web app investments. By improving backward compatibility for older versions of Internet Explorer, this also helps organization stay up-to-date with services such as Office 365 and deploy devices like the Surface Pro 2.

-Inputs from the IEBlog