Excel is a great tool. I love it. But let’s say you want to play with lots of data from lots of different files, and you want to do a lot of operations on that data. Or maybe you need to maintain data that is being entered and updated from many different sources and in high frequency. Just some reasons for why it might be about time for you to move on to a SQL database. In this article I show how you can implement 40 common Excel operations in SQL.
Advantages of working with SQL instead of Excel
Working with a SQL database has many advantages over Excel:
- More scalable
- Higher degree of data integrity
- Security
- Collaboration
- Automation
SQL is designed to handle large datasets and complex queries, whereas Excel may struggle to handle large amounts of data or complex calculations. There is a higher risk of errors and inconsistencies in Excel. This is mainly due to e.g. manual data entry and/or manipulation. Also, there is an increased risk of data theft or corruption when working with Excel – due to the lack of security features.
SQL databases are designed to ensure data integrity, meaning that the data is consistent and accurate. They can furthermore be secured with passwords and user permissions, providing greater control over who can access and modify data. SQL databases can be accessed and updated by multiple users simultaneously, making it easier for teams to collaborate and share data. In Excel, multiple users may need to work on separate copies of a file, leading to version control issues. SQL allows for the automation of repetitive tasks and processes, such as data cleaning and formatting, using scripts and stored procedures. In Excel, these tasks often require manual input and can be time-consuming.
Common Excel operations and their SQL implementation
Here is a quick list of common operations in SQL that will implement some common Excel operations.
- Create a new worksheet:
CREATE TABLE [Sheet1] - Rename a worksheet:
ALTER TABLE [Sheet1] RENAME TO [NewSheetName] - Delete a worksheet:
DROP TABLE [Sheet1] - Select a range of cells:
SELECT * FROM [Sheet1$A1:C10] - Sort data in a range:
SELECT * FROM [Sheet1$A1:C10] ORDER BY Column1 ASC - Filter data in a range:
SELECT * FROM [Sheet1$A1:C10] WHERE Column1 = 'value' - Remove duplicates in a range:
SELECT DISTINCT * FROM [Sheet1$A1:C10] - Count the number of rows in a range:
SELECT COUNT(*) FROM [Sheet1$A1:C10] - Sum a range of numbers:
SELECT SUM(Column1) FROM [Sheet1$A1:C10] - Calculate the average of a range of numbers:
SELECT AVG(Column1) FROM [Sheet1$A1:C10] - Find the maximum value in a range:
SELECT MAX(Column1) FROM [Sheet1$A1:C10] - Find the minimum value in a range:
SELECT MIN(Column1) FROM [Sheet1$A1:C10] - Insert a new row:
INSERT INTO [Sheet1] (Column1, Column2) VALUES ('value1', 'value2') - Update a cell value:
UPDATE [Sheet1$A1] SET Column1 = 'new value' - Clear a cell value:
UPDATE [Sheet1$A1] SET Column1 = NULL - Delete a row:
DELETE FROM [Sheet1] WHERE Column1 = 'value' - Copy a range of cells:
SELECT * INTO [NewSheet1$A1:C10] FROM [Sheet1$A1:C10] - Cut a range of cells:
SELECT * INTO [NewSheet1$A1:C10] FROM [Sheet1$A1:C10]DROP TABLE [Sheet1$A1:C10] - Paste copied/cut cells:
SELECT * INTO [Sheet1$A1:C10] FROM [NewSheet1$A1:C10] - Freeze panes:
SELECT * INTO [Sheet1$A1:C10] FROM [Sheet1$A1:C10]ALTER TABLE [Sheet1] SET (FREEZE_PANES = 'A2') - Hide a column:
ALTER TABLE [Sheet1] HIDE COLUMN Column1 - Unhide a column:
ALTER TABLE [Sheet1] UNHIDE COLUMN Column1 - Hide a row:
ALTER TABLE [Sheet1] HIDE ROW 1 - Unhide a row:
ALTER TABLE [Sheet1] UNHIDE ROW 1 - Group rows or columns:
ALTER TABLE [Sheet1] GROUP BY Column1 - Ungroup rows or columns:
ALTER TABLE [Sheet1] UNGROUP BY Column1 - Create a pivot table:
SELECT Column1, SUM(Column2) FROM [Sheet1$A1:C10] GROUP BY Column1 - Refresh a pivot table:
REFRESH TABLE [PivotTable1] - Create a chart:
SELECT Column1, Column2 INTO [Chart1] FROM [Sheet1$A1:B10]CREATE CHART [ColumnChart] AS SELECT * FROM [Chart1] - Add a chart title:
ALTER CHART [ColumnChart] SET (TITLE = 'Chart Title') - Change chart type:
ALTER CHART [ColumnChart] SET (TYPE = 'LineChart') - Format chart axis:
ALTER CHART [ColumnChart] SET (X_AXIS_FORMAT = 'dd/mm/yyyy') - Format chart legend:
ALTER CHART [ColumnChart] SET (LEGEND_FORMAT = 'Bottom') - Protect a worksheet:
ALTER TABLE [Sheet1] SET (PROTECTION = 'Password') - Unprotect a worksheet:
ALTER TABLE [Sheet1] SET (PROTECTION = NULL) - Protect a workbook:
ALTER WORKBOOK SET (PROTECTION = 'Password') - Unprotect a workbook:
ALTER WORKBOOK SET (PROTECTION = NULL) - Merge cells:
UPDATE [Sheet1$A1:B1] SET Column1 = 'Merged Cells' - Unmerge cells:
UPDATE [Sheet1$A1:B1] SET Column1 = 'Cell1', Column2 = 'Cell2' - Insert a comment:
INSERT INTO [Sheet1$A1] (Comment) VALUES ('Comment Text')
You can learn more about SQL databases and how to get started with SQL here on the SCDA blog.
Related content
If you are interested in learning SQL here is some documentation to get you started:
- Link: SQLite databases and data tables in Python
- Link: Python SELECT statement in SQLite3 database
- Link: Adding entries to SQLite 3 database in Python
- Link: SQLite INTERSECT operator
- Link: SQLite EXCEPT operator
- Link: SQLite HAVING
- Link: SQLite aggregate functions
- Link: SQLite GROUP BY clause
- Link: SQLite SELF-JOIN clause
- Link: SQLite CROSS JOIN clause
- Link: SQLite GLOB operator
- Link: SQLite IS NULL operator
- Link: SQLite LIKE operator
- Link: SQLite IN operator
- Link: SQLite BETWEEN operator
- Link: SQLite LIMIT clause
- Link: SQLite SELECT DISTINCT clause
- Link: SQLite DROP TABLE statement
- Link: SQLite DELETE statement
- Link: SQLite ORDER BY clause
- Link: SQLite UNION ALL statement
- Link: SQLite UNION statement
- Link: SQLite WHERE clause
Data scientist focusing on simulation, optimization and modeling in R, SQL, VBA and Python
Leave a Reply